10 Feb 2015

Web security: SQL injection

Ninth post in the web security series.

The previous post was about XSS which is basically an attack injecting HTML/JS in your application. An other common vulnerability is SQL injection, which is the same technique but for injecting SQL code in your application database.

This usually happens when user provided data are used in an SQL query without escaping the data.

The classic example is a login form where the application code use the values given by the user as is in a query:

query = "SELECT * FROM users WHERE login = '" + login + "' AND password = '" + password + "';";

(I will talk in an other post about encrypting password)

Here if the user provide something like admin as login and ' OR '1' = '1 as password the query sent to the database will be:

SELECT * FROM users WHERE login = 'admin' AND password = '' OR '1' = '1';

And the user will probably get admin access on the application.

In most programming languages there are plenty of ways to deal properly with SQL injection so it's a shame that it's still one of the most common vulnerability. Here are some ways to mitigate that issue:

• Use an ORM: for object oriented languages ORM are frameworks that bind the database model to the object model so you (mostly) don't have to write SQL queries yourself.
• Use an abstraction library that will automatically escape parameters in your SQL queries.
• If you still have to write SQL queries, run them as prepared statements so the query and the parameters are separated (for queries used several times, properly used prepared statements could also increase performances).
• If none of the previous things are possible and you still have to write plain SQL queries, escape the parameters with the proper library for your database (each database have different characters to escape). For instance in PHP with MySQL as database, use the function mysqli_real_escape_string to escape each arguments of your SQL query.