SQL injections

Never trust the client

Every data coming from the client:

  • HTTP headers,
  • URL parameters, query string,
  • Request body, form data,
  • Cookies, Storage API,

may contain invalid data, for many reasons:

  • Error by the user;
  • Old browser / JavaScript switched off;
  • The client is a robot;
  • The user is intentionally trying to hack your web app.

For these reasons, server-side code must always verify data sent by the client.

SQL Injections

Consider this code, verifying user credentials.

var user = req.body.user;
var pass = req.body.pass;
var sql = `SELECT * FROM users WHERE login='${user}' AND password='${pass}'`;
var rows = await knex.raw(sql);
if (rows.length > 0) {
  // user verified
}

The user sends these data in the request body:

user=root
pass=' OR '1'='1

The generated SQL query will be:

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

The WHERE condition is always true! The hacker connects to your database as root!.

What are the risks associated to an SQL injection?

  • Rights escalation (connect as root),
  • Data theft (dump the database),
  • Data compromise (destroy/modify the data).

https://xkcd.com

Here’s a list of some documented SQL injection attacks.

Note: although realistic, the attack suggested by XKCD is unlikely: most SQL engines nowadays forbid multiple statements, i.e. statements separated by a semi-colon (;).

Countermeasures

We already saw the solution: escape special characters ', ", ;

  • Using a Query builder,

    await knex('users').where({
    'login': user,
    'password': pass,
});

  • Using prepared statements.

    await knex.raw('SELECT * FROM users WHERE login=? AND password=?', 
               [user, pass]);

Result (in both cases)

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

References

Fork me on GitHub