SQL Injection : How secure is your application


17 Mar  

I know many novice PHP programmers who are very good when it comes to writing PHP scripts and adding nice CSS elements (+ some Jquery stuffs). But some of these guys  don’t care about the security of the application that they have written (sorry, no offense intended ).

In this edition of techblog, I’m going to focus only on SQL injection specific security aspects of the web application.

 

What is SQL injection?

 

SQL injection is an exploitation of the vulnerability of an application by injecting SQL statements from the user end. Let me explain this further : assume that you have a web application that is using a database as the back end. Suppose you have a form element (say, a search box or login box) that submits a query to this database. Then, you can inject the actual SQL code into the database and meddle with the information stored in that database, provided the programmer has not secured the application.

 

How does it happen?

 

Assume that you have a simple login application written in PHP or JSP. Typically, the program will check the submitted data (username and password) with the details held in the database. If it matches, the user will be allowed to enter.

 

login application

 

And here is the code :

 

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Techblog Demo - SQL injection</title>
    </head>
    <body>
<body>
<div style="text-align: center;"><big
 style="color: rgb(255, 0, 0);"><big><br>
SQL Injection&nbsp; Countermeasures &nbsp;: Techblog Tutorial</big></big><br>
<br>
<br>
<br>
<form method="post"
 action="LoginServlet">
Username: <input name="UsrName"
 type="text">
  <br>
  <br>
Password: <input name="UsrPword"
 type="password">
  <br>
  <br>
  <input name="LoginBtn" value="Login"
 type="submit"> </form>
<br>
<br>
<hr style="width: 100%; height: 2px;">techblog.aasisvinayak.com<br>
<br>
</div>

    </body>
</html>

 

And when it comes to the server side, the SQL statement for doing the check will be:

 

mysql_statement = ‘SELECT * from users WHERE username="%s1" and password="%s2"’

 

The meaning of this code is that it will select all the column entries (assume that there are only 2 columns – username and password) from the table ‘users’ where the row should match username= ‘your submitted username’ and password= ‘submitted value’. Hence,the result will be ‘none’ if your entries are incorrect. If you are checking for ‘None’ as the result for denying access; you are in serious trouble. Even if you are using other methods, there are issues.

 

Here is the reason:

Assume that the entry for username is 

‘ OR 1=1–

In this case, the username is empty hence it will fail at the check point, but the next one is ‘OR 1=1’. This means that the program will check if one of these conditions are met. The second condition (after OR) is 1=1; which is always true. Hence we passed the check. Now ,as you can see, we have also added ‘–’  at the end. These corresponds to the comment lines in SQL statement. Hence the password will not be checked.

Another point is that in most of the systems, one of the usernames will be admin. So you can use admin’– as the username and you will get admin privileges.

One can also use a UNION statement if he wants to select and display all the entries in the database. If you are have two tables named ‘users’ and ‘financial details’, you can use a UNION statement to display all the contents in ‘financial details’ table by injecting SQL into a text box.

 

Ways to prevent

 

  • The easiest way is to use Javascript to validate the entries that the user is submitting. Well, this is actually ineffective as JavaScript being a client side script, it can easily be surpassed.
  • Using parameterized statements in server side scripts is a recommended way. In this case, you can parameterize the inputs as (in JDBC)

prep.setString(1, username);

prep.setString(2, password);

and then execute the query

In PHP you may use

$statement->bindParam(‘:username’, $user);

$statement->bindParam(‘:password’, $pass);

  • Escaping is another method where we will introduce extra quotes for the submitted value. If you want to use this, you can try:

("SELECT * from users WHERE username=’%s’ and password=’%s’",

                  mysql_real_escape_string($username),

                  mysql_real_escape_string($password));

One thing to note here is that sometimes using quotes will cause SQL statements generated in next stages to be corrupted, if there are invalid entries in the database.

  • Also, there are few vendor specific methods for preventing this.

 

Tips

 

  • Never use common names for your tables – like users or accounts
  • Disable unwanted functions in database
  • Never use ‘admin’ or ‘root’ as the admin username
  • Use both client side (javascript) and server side checks
  • Assign privileges to users (database users)
Share and Enjoy:
  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Blogplay

Tags: ,


TechBlog on Facebook

Comments (1)

 

  1. ankit sulodia says:
    March 18, 2010 at 5:54 pm

    itz so so

Leave a Reply