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

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.


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 malicious user can change the logic of the SQL statement.

A malicious 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 = ''


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 --


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.

  • 1
    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

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.

  • 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] – Sathyajith Bhat 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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