In & Out of SQL Injection

  • What SQL Injection ?  

It is a vulnerability in database queries, allow hackers to inject some logical code to get the expected output from database.

There are different ways we perform SQL injection depends on structure and usage, for now we will see the vulnerability in login queries. We will see what actually happen when hacker inject the code. What Programmer actually do wrong? and how to fix that?

  • What Actually Happen:

Lets see some php code use for login:

$sql="select * from table where username='$username' and password='$password'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result);
if($row!=NULL)
print("Login Successfully");

Code is very straight forward and user will able to login when $row is not NULL

Now lets focus on query

SELECT * FROM `USERTABLE` WHERE USERNAME='$username' and PASSWORD=’$password’

In this query $username and $password are variable and enter by user on client side, the query return the rows of table which have that username and password otherwise it will return nothing and variable $row will be NULL and user will not get access.

But , logical code use by hacker is 1′ or ‘1’=’1 .

Now analysis the query when this code is injected in query.

SELECT * FROM `USERTABLE` WHERE USERNAME='1' or '1'='1' and PASSWORD=’1′ or ‘1’=’1 

or ‘1’=’1′ will make this query is always true and returns all the rows of table, so $row will not be NULL and hacker will enter the login.

  • Programmer mistakes and Solution:

Now lets look at the mistakes done by programmer and solution at every step.

 1 . Input on client side:

Problem: First of all,  we are allowing user on client side to enter quotes as input.

Solution: In input tag just simply use type=’email’ which will prevent user form entering quote. But its client side and user and easy change code and it is also very easy to disable the java-script which check this client side constraints.

2.  Why fetching directly:

Problem: We are assigning $row=mysql_fetch_array  , but to do this if nothing is returns.

Solution: To solve such case we can take help of mysql_num_rows functions which calculate the number of rows return by query, we can modify our code to

$sql="select * from table where username='$username' and password='$password'";
$result=mysql_query($sql);
$num_of_rows=mysql_num_rows($result);
if($num_of_rows>0)
print("Login Successfully");

3. Number of rows greater than Zero:

Problem: Mostly you may have figure out the problem in above implementation, we can be easily exploit. Why number of rows greater than 0. Problem will be still persists as query may return any number of rows greater than zero able to login

Solution: Solving this is quit simple by making if condition to

if($num_of_rows==1)
print("Login Successfully");

4. Single row in database:

Problem: Meanwhile above code seems perfect to anyone other is subtle problem if table has only one user entry like username=admin, password=admin. So number of users will be 1 and hacker may login.

Solution: As it is depend on number of entries in database. We can make sure there are more than one entry in table.

Final Solution: Above problem and all other problem are occur only due to we user able to pass quote to login request even if we solve with client side checking. So the perfect solution is to remove the quotes after getting the username and password on client side.

This is will solve all the problem. If you are developing then you must use MySQLi library.

And that’s it! You can always mention you thoughts on this in comments section and suggest if I missed anything.

Thanks for reading.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *