How to Prevent a SQL Injection Attack?

by

In this blog, I have explained to cover some basic knowledge of SQL injection, how hacker uses this flaw to gain unauthorized access, how we can modify our code a little bit to overcome this and how to make our sites/application more secure. In short, this blog uses PHP and MySQL with special examples of types of SQL Injections and explains how to prevent SQL injection.

What is SQL injection?

According to SQL injection, it means inserting or inputting SQL queries in the input fields of the application in order to manipulate or change the existing query to retrieve unauthorized information from a database. Also, SQL injection attack is one of the most widely used application vulnerability attacks that usually hacker do. Similarly, it is easy to do, doesn’t need detailed in particular technical knowledge and most of the sites/applications are vulnerable to it. Indeed SQL injection is considered to be the most severe and common attack by which hacker can steal the important data secretly from the back-end database. It is not a database or web application server issue but it is a web application programming issue and most of the developers are not aware of this.

Types of SQL Injections:

Therefore hackers can hack data from servers by exploiting SQL Injection in different methods. Common methods include retrieving data based on errors, conditions (true or false) and time. Let’s have a look into this to understand it in more detailed ways.

1- Error-Based SQL injection: 

When exploiting an error-based SQL Injection attack, hackers can fetch the details such as table names and content from visible database errors.

for example:

https://abc.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM details_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM details_schema.tables GROUP BY x)a)

This request will be returned as an Error Duplicate entry ‘details_schema’ for key ‘group_key’.

Same way works for table names and content. Disabling error messages on production servers helps to prevent hackers from fetching such information.

2- Boolean-Based SQL Injection:

Sometimes an error message is not displayed on the page when an SQL query fails, making it difficult for a hacker to get details from the vulnerable application.However, there is still a way to find information.When an SQL query fails, sometimes some sections of the web page disappear or change, or the full website can fail to load.

These notification allow hackers to determine whether the input parameter is vulnerable and whether it allows extraction of data. Hackers can test for this by inserting a condition into an SQL query

for example- 

https://abc.com/index.php?id=1+AND+1=1

If the website loads as normal, it might show that it is vulnerable to an SQL query Injection. for this to be sure, an attacker typically tries to provoke a false result using something like this:

https://abc.com/index.php?id=1+AND+1=2

Since the condition returns false, if no response is returned or the webpage does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to an SQL injection.

3- Time-Based SQL Injection:

In many cases, even though a vulnerable SQL query does not have any visible effect on the output of the page and it might still be possible to extract information from an underlying database in certain ways.Hackers determine this by instructing the database to wait a stated amount of time before responding.

If the page is almost not vulnerable, it will load quickly.The SQL syntax may be similar to the one used in the Boolean-Based SQL Injection Vulnerability.But to set a measurable sleep time, the ‘true’ function is changed to something that takes some time to execute, such as ‘sleep(4)’ which instructs the database to sleep for four seconds:

https://abc.com/index.php?id=1+AND+IF(version()+LIKE+’8%’,sleep(4),false)

How to prevent SQL Injection?

Followings are the basic points that need to be followed during interaction with the database :

⦁ Always try to replace following special characters from a query.

These special characters are :

1. “;” (Query delimiter function in the database)

2. ” ‘ ” (single Character data string delimiter function in the database)

3. “–“,”#”  (Used for Single line comment in the database)

4.”%”  (LIKE clause in the database)

5. ” (Double quote delimiter function in the database)

6. “\” (Backslash delimiter function in the database)

⦁  Always validate user input on the server end.

⦁ Use of Stored Procedures to interact with the database.

⦁ Use of Prepared Statements with Parameter Queries.

⦁ Enforcing Least Privilege to execute the query.

Real time example:

Login Page: 

The common approach for login functionality query is :

$user_email = $_POST[’email’];
$user_password = $_POST[‘password’];
$query = “SELECT * FROM employee WHERE email = ‘” . $user_email . “‘ AND password = ‘” . $user_password . “‘”;

Sometimes when the user provides their detail in the login form, query validates user’s credentials and allows the user to log in. But when hacker supplies password as “‘ or ‘1’=’1” without double quotes, the query will become:

$query = “SELECT * FROM users WHERE email = ‘[email protected]’ AND password = ” or ‘1’=’1′”;

Which allows a hacker to login without knowing the password of the user. So to overcome this problem, use following technique:

 $user_email= str_replace(array(‘ — ‘, ‘#’, ‘;’), array(”, ”, ”), $_POST[’email’]);
 $user_email= addslashes($user_email);
 $user_password = md5($_POST[‘password’]);
$query = “SELECT * FROM users WHERE username = ‘\’ or 1′ AND password = ‘154eec809fb37f6944eccf1fa1eb15d063′”;

For example-

When URL is used as a query string:

Let’s say you have e-commerce sites and you have implemented a functionality where you are fetching a specific product detail based on its id coming from URL and it affects the query  in the following manner:

http://www.abc.com/user_details.php?id=1$prod_id = $_GET[‘id’];
$query = ‘SELECT * FROM prod_detail WHERE id = ‘ . $prod_id;

For example, when id in the query string is changed to “1 or 1 = 1” without double quote, the query will be:

$query = ‘SELECT * FROM prod_detail  WHERE id = 1 or 1 = 1’;

Here query will be true and the query will return all data from the prod_detail table.

http://www.abc.com/user_details.php?id=-1 union select * from user;

Likewise, here also query will return all details from the user table. To avoid this situation allow id to accept only integer value.

$prod_id = intval($_GET[‘id’]);
$query = ‘SELECT * FROM products WHERE id = ‘ . $prod_id;

Earlier if we provide “1 or 1 = 1 ” or “-1 union select * from user”, furthermore it will be converted into integer value which will prevent the SQL injection attack indeed.

This is all about SQL injection. Thanks for reading 🙂  Happy Coding!!! For more Interesting stuff click here.

Latest posts by Ravi Verma (see all)

1 thought on “How to Prevent a SQL Injection Attack?”

Leave a Comment