Languages
[Edit]
EN

Node.js - MySQL - how to prevent SQL injection

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:

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
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

Node.js - MySQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join