What is an SQL Injection Attack ?

Please note that the website now has moved to https://www.thesecuritybuddy.com. Please visit the updated article here What is SQL Injection Attack ? - The Security Buddy

In many web applications, input data are taken from users and corresponding SQL query is executed in the server side to fetch or store proper data in the database. For example, if a user wants to see all books authored by an author, he searches with the name of the author. That input data is taken from the user and corresponding SQL query is executed in the database to fetch data. And the results are displayed back with proper formatting.

But, sometimes attackers exploit the security vulnerabilities in the application software and tricks the server to execute malicious SQL queries, thus deleting or changing the database or stealing sensitive data to perform even more attacks. This type of attacks are called SQL Injection Attacks.

Let's understand the attacks in more detail with some examples.

Example 1 : Suppose, username and corresponding sensitive data are stored in a database. A registered user, provides username and corresponding data is displayed in his webpage. For that, the server takes the username and executes the following SQL query in the database :

SELECT * FROM users WHERE name = ' ” + userName + “ ' ; “

where userName is the username is taken as input from the user, may be through a form.

At this point, the attacker can input,

' OR '1'='1

And, if proper care is not taken while writing the code, the server will execute the following query :

SELECT * FROM users WHERE name = ' ' OR '1'='1';

As a result, sensitive data of all the users will be revealed to him.

Example 2 : In another example, the attacker can input,

a' ; DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't

As a result, the server will be tricked to execute the following query in the database :

SELECT * FROM users WHERE name = 'a' ; DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';

And this will delete the username table altogether, if proper care is not taken.

Example 3 : In the next example, let's suppose, the following query is executed in the database by the server :

SELECT * FROM userinfo WHERE userid = “ + id_variable + “ ; ”

where id_variable is an input taken from the user.

But, if this user provided variable is not properly checked for type constraints, the attacker can take advantage of that.

An attacker can input,

1 ; DROP TABLE userinfo

As a result, the server will be tricked to execute the following query in the database :

SELECT * FROM userinfo WHERE userid = 1 ; DROP TABLE userinfo ;

As a result, the userinfo table is deleted from the database.

Example 4 : Let's suppose, for the URL http://bookreview.com/review.php?ID=5

The server executes the following query in the database :

SELECT * FROM bookreviewinfo WHERE bookid = “ + id_variable + “ ; ”

Suppose, the attacker loads the following URL :

http://bookreview.com/review.php?ID=5 OR 1=1

If proper care is not taken, the following query will be executed in the database :

SELECT * FROM bookreviewinfo WHERE bookid = 5 OR 1 = 1;

And then, the attacker loads this URL :

http://bookreview.com/review.php?ID=5 AND 1=2

If proper care is not taken, in the first case, reviews of all books will be shown. And, in the second case, an error page will be shown.

But, this will give enough information to the attacker to know that the application is vulnerable to SQL Injection Attack. So, now he can proceed forward to do some more experiments to get the version of SQL running in the server etc.

And, finally he can plan for even more attacks.

Example 5 : Suppose, in a web application, a user authenticates himself with username and password. And he has sensitive data stored in the website.

Let's assume, John is a registered user in the website with his username to be 'john'.

So, when he logs in, the server will execute the following query to fetch his data :

SELECT * FROM users WHERE username = ' john ' ;

Suppose, an attacker registers in the website with a username “john'--” and a different password.

So, when the attacker logs in, the following query will be executed :

SELECT * FROM users WHERE username = ' john ' --' ;

Please note that, '--' indicates comments and the characters after '--' are ignored while executing the query, thus avoiding getting syntax errors for trailing ( ' ) while executing the query.

So, sensitive information of John will be displayed to the attacker.

The attacker can even go a step forward and change John's password or steal sensitive data like credit card numbers etc.

Purpose of SQL Injection Attack

Using SQL Injection Attacks, an attacker can :

• steal sensitive information
• modify or delete database
• bypass authentication and impersonate a particular user
• gather enough information to perform even more attacks.

Countermeasures for SQL Injection Attacks

There are couple of precautions we can take to mitigate this attack.

• User input should not be embedded in the query directly. Instead, parameterized statements that work with parameters should be used.
• Type constraints of variables should be properly checked before executing the query.
• For parameterized statements, parameters should be escaped properly. For example, in PHP mysqli_real_escape_string() can be used to escape parameters.
• Certain characters can even be forbidden to be used in the query.
• Database permissions should be limited appropriately. Some tables can be restricted to be fetched without appropriate permissions.
• Bin2hex() and unhex() can be used to convert the parameters to/from hex values. The advantage of this is, the output of unhex() function is returned as a string and not interpreted.

For more information on countermeasures to be taken, you may look into the following link :

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

So, this article was intended to inform you about another vulnerability. Hope it solved its purpose.