What are SQL Injections?

In computing, SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. This is the wikipedia definition. Let us try to further understand what it means. When using a SQL database with node.js and express.js we write queries to fetch the data from the database. A normal SQL query looks like:

USE application;
SELECT * FROM users;

Where application is the schema and users is the table. A schema may consist of multiple tables. This query returns all rows in the users table.

Now it may not always be useful for us to retrieve all the rows from the database. Let us say we want the row pertaining to the user with a specific user id. Instead of retrieving all the entries from the table and then iterating over them all till we find the desired entry we can write a query telling the database which entry we want and it will retreive only that.

USE application;
SELECT * FROM users WHERE user_id = '27129';

This is what the basic query structure looks like. Let us see how we can write a function in node.js to execute the query.

const mysql = require('mysql2');
const pool = mysql.createPool({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE
}).promise()

async function getUser(user_id) {
    const [user] = await pool.query(
        "SELECT * FROM users WHERE user_id = " + user_id
    );
    return user;
}

First we create a conncection to the MYSQL database and then we write an asynchronous function to get the user details. Looks pretty straightforward right. We are just taking the user_id as an input and passing it to the query directly. This user_id will mostly be coming from the frontend of our application in some way. The function will simply return the row pertaining to the user_id sent by the user. But what happens when a user_id which does not exist is entered? Well in that case nothing will be returned. So is our database secure? No. The variable user_id is passed to the SQL query in the form of an executable code and not pure data, which allows the user to write logic which may allow for some interesting scenarios. Let us see how this works.

What does a computer return when given to check ‘1==1’? It return true. We will be using this simple information to write a very basic SQL injection.

var user_id = '  OR 1=1';

Now when the function is executed with the following user_id the database will return all entries from the database where the user_id is ‘  ‘ or 1=1. Which unfortunately for us will be all entries😭. This means the user will be returned with all the data from our users table which might contain sensitive information such as names, emails, passwords, etc. So the 1=1 logic superceeds our logic of matching the entry where the user_id is given. So how do we handle this? How do we let the variable passed to the query be allowed to read as only data and not executable code. SQL has a very neat way of handling this.

async function getUser(user_id) { 
    const [user] = await pool.query(
        "SELECT * FROM users WHERE user_id = ?",
        user_id
    );
    return user; 
}