How to Prevent SQL Injection in a NodeJS API

    Apr 15, 2023       by Pankaj Kumar

To write code in any programming language, It is very important to write the code in a way so that it can be robust to handle all kinds of attacks. Database query injection is one of the vital parts which a developer needs to take care of. If we talk about the RDBMS then SQL injection is a very crucial part that a developer needs to care about.

To prevent SQL injection in a Node.js API, you can take the following steps:

1. Use parameterized queries

Parameterized queries use placeholders for user input and bind the input values to those placeholders before executing the query. It helps to prevent malicious user input from being injected into the SQL query. You can use libraries like mysql2, pg, or sqlite3 to create parameterized queries.

Here is an example of using parameterized queries with mysql2:

 

 
const mysql = require('mysql2');
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'XXXXXX',
    password: 'XXXXXXXXXXXXXXXXx',
    database: 'db_development'
  });
 
  const userId = req.params.id;
  connection.query('SELECT * FROM students WHERE student_id = ?', [id], function (error, results, fields) {
     if (error) throw error;
     res.json(results);
  });
 

 

2. Sanitize user input 

Sanitizing user input means removing any potentially harmful characters from the input before using it in a query. You can use libraries like validator.js or sanitize-html to sanitize user input.

Here is an example of using validator.js to sanitize user input:

 

 
  const validator = require('validator');
  const name = validator.escape(req.body.name);
  const email = validator.escape(req.body.email);
  connection.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], function (error, results, fields) {
    if (error) throw error;
    res.json({ message: 'User added successfully' });
});
 

 

3. Limit database privileges

Ensure that the database user account used by your application has only the necessary permissions to execute queries. For example, if your application only needs to read data from the database, then the database user account should only have read privileges.

 

4. Use an ORM:

Object-Relational Mapping (ORM) libraries like Sequelize or TypeORM can help prevent SQL injection by automatically escaping user input and generating parameterized queries. They also provide other benefits like easy database schema management and model validation.

Here is an example of using Sequelize to prevent SQL injection:

 

 
  const { Sequelize, Model, DataTypes } = require('sequelize');
  const sequelize = new Sequelize('sqlite::memory:');
  class User extends Model {}
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING
  }, { sequelize, modelName: 'user' });
  const userId = req.params.id;
  User.findOne({ where: { id: userId } }).then(user => {
     res.json(user);
});
 

 

By taking these steps, you can significantly reduce the risk of SQL injection attacks in your Node.js API.

Nodejs attack SQL Injection

Find other similar Articles here:

CATEGORIES
INTERVIEW QUESTIONS