Node.js - MySQL - how to prevent SQL injection

In this article, we would like to show you how to prevent SQL injection in MySQL. 

SQL injection is a serious problem for web developers. It involves entering a SQL subquery in place of the main query value, which may then be incorrectly processed by the database.

Below we present 3 ways to defend against SQL injection Attack:

• escape() method
• placeholders
• named-placeholders - external npm package

In each case, we will try to pass the 'SELECT * FROM `users` WHERE `salary` < 3700' query.

Note: At the end of this article, you'll find some database preparation files that you can practice on.

1. escape() method

The first way is to use connection.escape() method, which arguments are a value we are getting from the user, so the one that could turn out to be unsafe.
Next step is to pass the query to the method which sends the query to the database.

const mysql = require('mysql');

const connection = mysql.createConnection({            // gets connection with database
    host: 'localhost',  // '127.0.0.1'
    user: 'root',
    password: 'password',
    database: 'my_database',
});

connection.connect(error => {
    if (error) throw error;
    const value = 3700;                                  // SELECT * FROM `users` WHERE `salary` < 3700
    const query = 'SELECT * FROM `users` WHERE `salary` < ' + connection.escape(value); 
                                                 
    connection.query(query, (error, result) => {       // sends queries and receives results  
        connection.end();                              // closes connection
        if (error) throw error;
        console.log(result);
    });
});

2. Placeholders

In this case, we create a query that has question marks in the places where we want to insert something. values in the array are inserted in the order in which they were passed.

Note: ? is for ordinary values, ?? is used for column and table names.

const mysql = require('mysql');

const connection = mysql.createConnection({              // gets connection with database
    host: 'localhost',  // '127.0.0.1'
    user: 'root',
    password: 'password',
    database: 'my_database',
});

connection.connect((error) => {
    if (error) throw error;
    const query = 'SELECT * FROM ?? WHERE `salary` < ?';
    const values = ['users', 3700];        // SELECT * FROM `users` WHERE `salary` < 3700

    connection.query(query, values, (error, result) => { // sends queries and receives results
        connection.end();                                // closes connection
        if (error) throw error;
        console.log(result);
    });
});

3. named-placeholders

The last way is to use the external npm package - named-placeholders. It is similar to the previous method, but in this case, we have more control over the order and what will be passed.

Note: :x is for ordinary values, ::x is used for column and table names.

const mysql = require('mysql');
const toUnnamed = require('named-placeholders')();

const connection = mysql.createConnection({                    // gets connection with database
    host: 'localhost',  // '127.0.0.1'
    user: 'root',
    password: 'password',
    database: 'my_database',
});

connection.connect((error) => {
    if (error) throw error;
    const query = toUnnamed('SELECT * FROM ::table WHERE `salary` < :value', {
        table: 'users',
        value: 3700,          // SELECT * FROM `users` WHERE `salary` < 3700
    });

    connection.query(query[0], query[1], (error, result) => {  // sends queries
        connection.end();                                      // closes connection
        if (error) throw error;
        console.log(result);
    });
});

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`surname` VARCHAR(50) NOT NULL,
	`department_id` INT(10) UNSIGNED,
    `salary` DECIMAL(15,2) NOT NULL,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `users`
	( `name`, `surname`, `department_id`, `salary`)
VALUES
	('John', 'Stewart', 1, '3512.00'),
	('Chris', 'Brown', 2, '1344.00'),
	('Kate', 'Lewis', 3, '6574.00'),
	('Ailisa', 'Gomez', NULL, '6500.00'),
	('Gwendolyn', 'James', 2, '4200.00'),
	('Simon', 'Collins', 4, '3320.00'),
	('Taylor', 'Martin', 2, '1500.00'),
	('Andrew', 'Thompson', NULL, '2100.00');

References

1. SQL injection - wikipedia