Accelerated Security Course - Episode 2: SQL Injections

This article was written by Damien Metzger, and first published on the PrestaShop blog, on August 8th, 2011.

A SQL injection is when a hacker enters malicious data in a SQL query. This type of attack is on the rise and you need to protect yourself as it is very easy to modify the nature of the query without sufficient protection.

We would like to illustrate this with an example rather than a long explanation. This is what can happen on a login page.

Your query:

SELECT id FROM users WHERE email = ‘[email protected]' AND password = ‘$plop'

Method:

• The hacker sets the following password: ‘ OR ‘1' = ‘1
• The resulting query is:

  SELECT id FROM users WHERE email = ‘[email protected]' AND password = ‘‘ OR ‘1' = ‘1'
  

It always sends the user ID without having to know the password.

So how can we protect ourselves? It's simple enough as long as you remember to do it every time!

There are 2 common types of injection:

1. Exploiting a lack of protection around quotes and double quotes
2. Exploiting a lack of data protection

For the first type, you must "escape" single and double quotes with a suitable function

• The standard PHP function is addslashes().
• You can use the mysql_real_escape_string() function if you can connect to open MySQL. This function also covers some extra characters.
• Use pSQL() in PrestaShop. It is extremely thorough with anything resembling HTML and manages magic quotes (magic quotes are a configuration on some servers that escape quotes automatically; in theory it's a good idea but its presence or absence can cause differences in the nature of some servers. PrestaShop does not recommend using magic quotes).

For the second type, which mainly concerns numerical data, you just need to do a cast. It is far more difficult to exploit a query if you are restricted to integers and floating-point numbers!

Most of the tools that provide an abstraction layer to access the database include various protection mechanisms. Parameter binding is a particularly good way to protect queries.

There are tools that attempt injections to test out your scripts. Since you aren't a sophisticated hacker and there is no guarantee of security, this is a good way to check that you have not forgotten anything.

Let us tell you about the elections in Sweden last year. Voters are legally permitted to complete their ballots by hand. As the results were very tight, the government decided to anonymously publish the list of paper ballot votes. Among the many candidate names, one smart-pants had written "pwn DROP TABLE VALJ" on their voting ballot knowing that the ballots would be digitized and entered in a database. Why? To completely delete the table of results! Anything is possible if you put your mind to it.