[Edit]

Node.js - MySQL - how to prevent SQL injection

1 contributors

2 contributions

0 discussions

6 points

Created by:

Kia-H

546

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

Edit

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.

xxxxxxxxxx
 
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

Edit

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.

xxxxxxxxxx
 
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

Edit

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.

xxxxxxxxxx
 
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

Edit

create_tables.sql file:

xxxxxxxxxx
 
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:

xxxxxxxxxx
 
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');