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

Let’s 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");

The code is very straightforward and the user will able to log in when $row is not NULL

Now let’s focus on query

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

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

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

Now analysis the query when this code is injected in the 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 the table, so $row will not be NULL and the hacker will enter the login.

  • Programmer mistakes and Solution:

Now let’s look at the errors done by programmer and solution at every step.

 1. Input on the client side:

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

Solution: In input tag just merely use type=’email’ which will prevent a user from entering a 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 constraint.

2.  Why fetching directly:

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

Solution: To solve such case we can take help of mysql_num_rows functions which calculate the number of rows returned by the 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 exploited. Why the number of rows greater than 0. The problem will still persist as the query may return any number of rows greater than zero able to log in

Solution: Solving this is quite 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 a subtle problem if a table has only one user entry like username=admin, password=admin. So a number of users will be 1 and hacker may log in.

Solution: As it depends on many entries in the database. We can make sure there is more than one entry in the table.

Final Solution: Above problem and all other problem 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 the 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 your thoughts on this in the comments section and suggest if I missed anything.

Thanks for reading.

One Reply to “In & Out of SQL Injection”

Leave a Reply

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