How to Prevent SQL Injection Attacks

SQL injection is a technique that exploits a security vulnerability from the database layer of an application. This technique consists in inserting malicious code into strings that are later passed to the database management system for parsing and execution. The main goal of this attack is to expose hidden data, to override valuable one, or even to execute dangerous system level commands on the database host. It happens from using poorly designed query language handlers. In this article I’ll show how you can enhance your code in order to prevent such attacks .

How can a SQL Injection attack be executed?

Let’s start with an example to help you to understand how SQL Injection works. Let’s take a look to the standard login window below:

Assuming that the system has a Users table, then the SQL code used to authenticate the user will look like follows:

SELECT *

FROM Users

WHERE username = 'cookiemonster' AND password = 'thepassword'

So, the username and password values are passed to the authentication program that will replace this data in the SQL section and then verify if a user matches this username/password combination. Now imagine that, instead of inserting the username and password, the user inserts the following code:

Also as in the previous situation, the username and password values are passed to the authentication program and the result will be as follows:

SELECT *

FROM Users

WHERE username = '' OR 1=1; /*' AND password = '*/--'

Kaboom! All those who understand SQL code know that the WHERE clause will return true because the username is empty, the condition 1=1 is true and the remaining code will be ignored because it is inside a comment block. Result: the attacker is successfully authenticated in our system and has access to all the features without providing a valid user! Even worse than this would be if the user inserts something like:

This will be executed as:

SELECT *

FROM Users

WHERE username=''; DROP TABLE Users; /*' AND password = '*/--'

Kaboom! Kaboom! In this case, instead of being authenticated, the attacker had deleted the entire Users table from our system! Now, do you understand the risks of SQL injection?

How to prevent this type of attacks?

There are some tips that you can follow in order to prevent SQL injection:

• Never connect to the database as a superuser. Use always customized users with very limited privileges.
• Check if the user input has the expected data type. If you are expecting a number, make sure that the user has inserted a number.
• Quote each non numeric user supplied value that is passed to the database. The problem of the examples above was that the inserted quotes interrupted the flow of the program. For example, in PHP you can use mysql_real_escape_string() or addslashes() in order to escape special characters.

I hope this was helpful to you and if you have any questions feel free to post some comments below!

security