What is SQL injection?

Question

What is the meaning of SQL injection? I am not able to understand the term. And what problems may be caused by SQL injection?

Answer 1

SQL injection most commonly happens when a programmer builds an SQL command by appending together (or interpolating) strings, using user-supplied input.

e.g. Imagine this extract from a vulnerable piece of user authentication (login) pseudocode from a fictional web application.

username = getPostData( "username" );
password = getPostData( "password" );

sql = "select id, username from users'
      + ' where username='" + username + "' and password='" + password +"'";
result = executeQuery( sql );

if (result[ 0 ]) {
  loginUser( result[ 0 ][ 'id' ] );
  print "You are logged in as " + encodeAsHtml( result[ 0 ][ 'username' ] );
}

At first glance you may think this looks sensible, but the problem is that it makes no distinction between the user-supplied data and the SQL code; data can be treated as code. This means that a malicous user can change the logic of the SQL statement.

A malicous user could completely bypass the login protection if he could change the logic of the SQL command to produce an answer that guarantees at least one row is always returned.

For example, if he entered a real username bob, but put the password as:

' or 1=1 --

Then this would grant him access to someone else's account. This is because the resultant SQL would look like this:

select id, username
from users 
where username='bob' and password='' or 1=1 --'

Note that the logical expression 1=1 always evaluates to true. Also note that the injection vector ended with two hyphens, which marks the rest of the line as a comment. Thus the SQL is logically the same as

select id, username
from users 
where (username='bob' and password='') or true

which is logically the same as

select id, username
from users 
where true

which is logically the same as

select id, username 
from users

Thus, all the users in the database will be returned, and he will be logged in as the first one in the list - which is usually the administrator.

Also SQL injection can be used to read the all data out of the database. Entering the username as follows (SQL Server syntax) will list the user-defined table names

' union select -1, name, from sysobjects where xtype = 'U' order by id --

because this produces

select id, username
from users
where username = ''

union

select -1, name
from sysobjects
where xtype = 'U'

order by id asc

because our injected data has an id of -1 and we are sorting the data by id, the first row returned from the database will be our select in the sysobjects table. So our displayed "username" will now be the name of the first user-created table in the database. Similar techniques can be repeated to read out all the data of every column of every row of every accessible table. Please note that this can still be done even when the feature being attacked produces no output at all!

Some combinations of programming language database libraries and DBMSs also allow query-stacking. This is a technique where a whole new SQL command is appended on the end. The database will then execute both queries. Username:

'; delete from users --

produces

select id, username
from users
where username='';

delete from users 

Now your application doesn't have any users (and requires SQL injection to log in). See mandatory XKCD comic.

See this SQLi cheat sheet if you are interested in more techniques commonly used by attackers and vulnerability testers.

So how do I avoid this?

Actually, in many common scenarios, it is so easy. Prepared statements separate data from code and do not allow parameters to be treated as SQL code. Simply recode your query to use prepared statements and bind individual parameters to the placeholders.

in pseudocode:

username = getPostData( "username" );
password = getPostData( "password" );

sql = "select id, username from users where username=? and password=?";
query = prepareStatement( sql );

query.setParameter( 0, username );
query.setParameter( 1, password );
result = executePreparedStatement( query );

As always this isn't the whole story...

Don't forget to defend in depth and always do input validation too, as you always would (should). WHERE clauses need extra attention as special characters such as % may not be wanted. Be particularly careful when passing user-data as arguments to database functions and be aware how they can be abused. If you have stored procedures that generate dynamic queries you may need further protection within the procedures themselves.

Answer 2

SQL Injection is a technique of using valid SQL commands to tamper with, delete or inject data into a back end database directly through weaknesses in the validation of code input to a web application which calls that database.

It is one of the OWASP Top Ten most commonly used attack routes as it is very simple to exploit.

It can be used to compromise entire databases of customer data, credit card data, financial records etc., or to get a copy of this data. Generally quite a major impact!

The good thing is it is very easy to mitigate through Input Validation - most frameworks provide modules to do this.