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:

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).


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 (;).


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'


Fork me on GitHub