More specifically, the website interprets the data submitted by the attacker as a database command, which it then executes. If the command is to modify entries in a database, or even to delete the entire database, then the results can understandably be catastrophic. For that reason it is vital that organizations take steps to prevent SQL injection attacks.
SQL injection attacks pose a serious security threat to organizations. A successful SQL injection attack can result in confidential data being deleted, lost or stolen; websites being defaced; unauthorized access to systems or accounts and, ultimately, compromise of individual machines or entire networks. Twenty years after its discovery, SQL injection remains a top database security concern.
How a SQL injection attack works
Imagine someone has to appear in court and is asked to provide their name. Instead of giving their real name, “Joe Bloggs,” they give the name “Release Joe Bloggs.” When the case comes up, the judge calls out “Release Joe Bloggs,” so the bailiff releases him.
This illustrates the concept of a SQL injection attack. Instead of providing a real name, the accused deliberately formulates a name that is interpreted as a command, resulting in an unintended action – in this case, an unintended release.
SQL injection examples
Here’s an example of how a SQL injection attack could be carried out in practice. The attack is designed to gain access to all data about a user from the database table USERS without knowing a user name or matching password.
The SQL application code might be:
SELECT * FROM Users WHERE Username=’$username’ AND Password=’$password’
Using a web interface, when prompted for their username and password, an attack might enter:
1′ OR ‘1’ = ‘1
and
1′ OR ‘1’ = ‘1
By entering this deliberately formulated username and password pair, the attacker has effectively injected two whole OR conditions into the authentication process.
Let’s take a closer look to see how.
The SQL application code was expecting a simple text string such as joebloggs for the username, and another simple string such as password123 for the password.
It would then parse the line:
SELECT * FROM Users WHERE Username=’$username’ AND Password=’$password’
as
SELECT * FROM Users WHERE Username=’joebloggs’ AND Password=’password123
and access the data for a user joebloggs (if there is one) if the password for that user is password123.
But here’s the problem. What the application was not expecting was that an attacker would enter a username and password formulated in this way, with a clever use of apostrophes.
The result is that the query is parsed as:
SELECT * FROM Users WHERE Username=’1′ OR ‘1’ = ‘1’ AND Password=’1′ OR ‘1’ = ‘1’
Now the application will access data for any user if their password is 1 or if 1=1. And since the condition 1=1 is always true, this SQL query will always result in the password authentication process being bypassed.
(Code sample sourced from OWASP)
In the SQL injection example above, the two OR conditions are injected when the application was expecting a username and password string, but an attack could just as well inject a database command such as DROP DATABASE, which results in the loss of all the information stored in a database.
For example, imagine a database application that enables an employee to enter their name into one field, and a number such as a social security number into the next field, and stores this information in a database called socsecnumbers.
The application will likely have a form with some code behind it to accept a name in the form ’employeename’. A malicious employee (or outside attacker) might be able to carry out a SQL injection attack that causes the application to execute the SQL command DROP DATABASE socsecnumbers, which results in the deletion and complete loss of the information stored in that database.
To do so, instead of entering Joe Bloggs, the attacker could enter the name Joe Bloggs’); DROP DATABASE socsecnumbers; — ?
resulting in the application understanding:
‘Joe Bloggs’); DROP DATABASE socsecnumbers; — ?’
The key part of this attack is, again, the malicious use of an apostrophe. The result of this ‘ character after Joe Bloggs is that the database thinks it has finished receiving the name it is expecting. Since more characters have been entered, it decides they should be interpreted as SQL code. The ); means that the following statement should be executed immediately, so the database executes a DROP DATABASE socsecnumbers command.
There will still be a trailing ‘ in the code that has not been used, so to prevent that being a problem the name entered by the attacker ends with ;– because the ; means carry on executing the next part of the name, and — tells the database to give up on that line because the rest of the statement should be treated as a comment.
SQL injection security
SQL injection attacks only work when an application is fooled into executing code because it receives user input in a form it is not expecting. That means a vital SQL injection security measure is to carry out data sanitization and validation. This effectively adds an inspection layer to ensure that any submitted data is not unusual and might pose a SQL injection risk.
Sanitization usually involves running any submitted data through a function (such as MySQL’s mysql_real_escape_string() function) to ensure that any dangerous characters such as ‘ are not passed to a SQL query in data.
Validation is slightly different in that it involves adding code that attempts to ensure that any data submitted is in the form that is expected in that particular instance. At the most basic level this includes ensuring that email addresses contain an “@” sign, that only digits are supplied when numeric data (such as a zip code) is expected, and that the length of a piece of data submitted is not longer than the maximum expected length (so a social security number should not include more than 9 digits).
Validation is often carried out in two ways: by blacklisting dangerous or unwanted characters and by whitelisting only those characters that are allowed in a given circumstance, which can involve more work on the part of the programmer. Although validation may take place on the client side, hackers can modify or get around this, so it’s essential that all data is validated on the server side as well to reduce SQL injection risk.
Read on for more ways to reduce SQL injection risk.
This updates a June 23, 2016 article