Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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

share|improve this question
    
Here is an article that explains it quite thoroughly: developer.com/db/article.php/3765256/… –  Roger Jan 27 '12 at 20:50
    
add comment

migrated from crypto.stackexchange.com Jan 24 '12 at 12:40

This question came from our site for software developers, mathematicians and others interested in cryptography.

2 Answers

up vote 27 down vote accepted

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.

share|improve this answer
    
Of course, one should never store a password in plain text, as this example implies; Passwords that will be entered in full, should only be stored in a hashed and salted form. –  Cheekysoft Jan 25 '12 at 14:28
add comment

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.

share|improve this answer
    
Can you please provide the examples of that ? –  Bhavik Ambani Jan 24 '12 at 12:54
    
btw @Rory you seem to have missed link [1] –  Sathya Jan 24 '12 at 13:00
    
Please don't use validation to mitigate this. Use prepared statements with bound parameters. –  Cheekysoft Jan 24 '12 at 14:10
1  
@Cheekysoft - good point. My answer definitely came from the perspective of how to quickly fix an instance of SQLi. However I would encourage everyone to use validation in addition to prepared statements, as a free/very cheap additional layer of security. –  Rory Alsop Jan 24 '12 at 14:45
    
Pretty much all databases allow access to the file system and may be able to launch installed executables. Assume full system compromise for the worst case. –  Cheekysoft Jan 26 '12 at 15:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.