Preventing SQL Injection in Node.js

SQL Injection is a technique that sends malicious SQL queries to a database to manipulate or leak information. In this post, we’ll explain how to prevent SQL Injection when connecting to a MySQL database using the ‘mysql’ package in Node.js.

First, to understand how SQL Injection works, let’s examine a simple example:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "yourdb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  
  var sql = "SELECT * FROM users WHERE username = '" + req.body.username + "' AND password = '" + req.body.password + "'";
  
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Result: " + result);
  });
});

This code reads a username and password from the user and runs a SQL query to find the corresponding user. However, this approach is not safe. If a malicious user inputs the following values:

username: ‘ OR ‘1’=’1 password: ‘ OR ‘1’=’1

The SQL statement would transform into:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

Consequently, this query which is always true without other any conditions finally leaks all user data.

To prevent such the danger of SQL Injection, you can use Prepared Statements in Node.js. This method always safely escapes and binds parameters instead of inserting them directly into your SQL queries:

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  
  var sql = "SELECT * FROM users WHERE username = ? AND password = ?";
  var values = [req.body.username, req.body.password];
  
  con.query(sql, values, function (err, result) {
    if (err) throw err;
    console.log("Result: " + result);
  });
});

No matter what a malicious user inputs, using this method guarantees to surely protect your database from SQL Injection.

I hope this post has helped to make your Node.js app safer. SQL injection can put your data at significant risk, but it’s easy to prevent when you write your code properly. But always remember to validate all user inputs and handle them with care.