How to Prevent SQL Injection Attack (Explained with an Example)

by Lakshmanan Ganapathy on February 7, 2012

This article explains basics of SQL Injection with an example that shows SQL Injection, and provides methods to prevent from these attacks.

As the name suggests, this attack can be done with SQL queries. Many web developers are unaware of how an attacker can tamper with the SQL queries. SQL-Injection can be done on a web application which doesn’t filter the user inputs properly and trusts whatever the user provides. The idea of SQL injection is to make the application to run undesired SQL queries.

All the examples mentioned in this article are tested with the following:

  • PHP 5.3.3-7
  • Apache/2.2.16
  • Postgresql 8.4

SQL Injection Example

Most of the web application has a login page. So we will start with that. Let us assume the following code was written by the application.

index.html:

<html>
<head><title>SQL Injection Demo</title></head>
 <body onload="document.getElementById('user_name').focus();" >
 <form name="login_form" id="login_form" method="post" action="login.php">
  <table border=0 align="center" >
   <tr>
    <td colspan=5 align="center" ><font face="Century Schoolbook L" > Login Page </font></td>
   </tr>
   <tr>
    <td> User Name:</td><td> <input type="text" size="13" id="user_name" name="user_name" value=""></td>
   </tr>
   <tr>
    <td> Password: </td><td> <input type="password" size="13" id="pass_word" name="pass_word" value=""></td>
   </tr>
   <tr>
    <td colspan=2 align="center"><input type="submit" value="Login"> </div></td>
   </tr>
  </table>
 </form>
</body>
</html>

When the user enters the user_name and pass_word, it will be posted to login.php via HTTP_POST method.

login.php:

<?php
$Host= '192.168.1.8';
$Dbname= 'john';
$User= 'john';
$Password= 'xxx';
$Schema = 'test'; 

$Conection_string="host=$Host dbname=$Dbname user=$User password=$Password"; 

/* Connect with database asking for a new connection*/
$Connect=pg_connect($Conection_string,$PGSQL_CONNECT_FORCE_NEW); 

/* Error checking the connection string */
if (!$Connect) {
 echo "Database Connection Failure";
 exit;
} 

$query="SELECT * from $Schema.users where user_name='".$_POST['user_name']."' and password='".$_POST['pass_word']."';"; 

$result=pg_query($Connect,$query);
$rows = pg_num_rows($result);
if ($rows) {
 echo "Login Success";
}
else {
 echo "Login Failed";
}
?>

The line number 19 in the above code is vulnerable to SQL-Injection (i.e the line that starts with “$query=”SELECT *..”). The SQL query is designed to match the given username and password with the database. It will work properly if the user provides valid username and password. But an attacker can craft the input as follows:

In username field, instead of providing a username the attcker can enter the following.

' or 1=1;--

The attacker than then leave the password field be empty.

When the attacker clicks submit, the details will be posted to login.php. In login.php the query will be framed as follows:

SELECT * from test.members where user_name='' or 1=1;--' and password='';

The above one is a valid SQL query. In postgresql – is the comment character. So the statements after – will be treated as comments and it will not be executed. Now the postgresql will execute

select * from test.members where user_name='' or 1=1;

This will return true and give “Login Success” message.

If the attacker knows the database tables name, then he can even drop those tables by giving the following input in the username field.

';drop table test.lop;--

Some login application, tends to do the following.

  • Stored the password as md5 in the database
  • First select the username,password from the database based on the username provided.
  • Then md5 the password given by the user, and compare it with the password got from database.
  • If both are matched, then login is success.

Let’s see how we can bypass that if the query is vulnerable to SQL-Injection.

login.php:

$query="SELECT user_name,password from $Schema.members where user_name='".$_POST['user_name']."';"; 

$result=pg_query($Connect,$query); 

$row=pg_fetch_array($result,NULL,PGSQL_ASSOC); 

# Find the md5 for the user supplied password.
$user_pass = md5($_POST['pass_word']); 

if(strcmp($user_pass,$row['password'])!=0) {
 echo "Login Failed\n";
}
else {
 echo "Login Success\n";
}

Now enter the following in the username field

' UNION ALL SELECT 'laksh','202cb962ac59075b964b07152d234b70

Enter “123” in the password field and click submit. md5(123) is 202cb962ac59075b964b07152d234b70

Now the query would expand as follows:

SELECT user_name,password from test.members where user_name='' UNION ALL SELECT 'laksh','202cb962ac59075b964b07152d234b70';

When the above query is executed, the database will return ‘laksh’ as the username and ‘ 202cb962ac59075b964b07152d234b70′ as password.

We also posted “123” in the pass_word field. So the strcmp will return 0 and the authentication will be success.

The above are just couple of examples of SQL injection attacks. There are lot of these variations. Following are some of the things you can do to reduce the possibility of SQL-Injection attacks.

  • Strict type checking ( Don’t trust what the user enters )
  • If you expect user name to be entered, then validate whether it contains only alpha numerals.
  • Escape or filter the special characters and user inputs.
  • Use prepared statements to execute the queries.
  • Don’t allow multiple queries to be executed on a single statement.
  • Don’t leak the database information to the end user by displaying the “syntax errors”, etc..

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 7 comments… read them below or add one }

1 Vineel Kumar Reddy Kovvuri February 7, 2012 at 1:55 am

good article
but its mostly on sql injections techniques not on prevention.
and thanks for sharing your ideas…..

2 Ron February 7, 2012 at 9:05 am

thanks
good article.
all developers should make a note of this; junior as well as senior.

3 Tarun February 9, 2012 at 1:00 am

Awesome article :)

4 Ganesh March 1, 2012 at 2:18 am

Thanks

5 rajasekhar March 9, 2012 at 3:15 am

very usefull, but let me know the same is applicable in all databases or only on postgresql

6 GAUTAMKUMAR SONI September 24, 2012 at 1:25 pm

hello dear , you write about vulnerable web-site …there any body can attack easily . your thought is nice but only among the attack ..your concept is not related to security against SQL INJECTION. so dear you think about prevention of sql injection…

7 chandana neeraganti June 11, 2014 at 1:08 pm

Hi frnd, itz very glad to knw about the attacks….and its possibilities.But we need how to prevent them.how we can find in easy way to overcome the attacks.

Leave a Comment

Previous post:

Next post: