Let’s see how to query your MySQL database and avoid SQL injection using Nodejs and Javascript variables. This method will work whatever framework you use for your NodeJS server (vanilla, express, fastify). The code below will use fastify, but it would be the same for an express server.

First, we need to install the npm mysql packet using:

npm install mysql
// or
yarn add mysql

Once done, a connection between our Mysql database and NodeJS is needed:

// app.js
const mysql = require("mysql");

const db = mysql.createConnection({
  host: "host.something",
  user: "admin",
  password: "azerty", // Be sure to have something more elaborate :D
  database: "dev",
});

db.connect(function (err) {
  if (err) throw err;
  console.log("Connected to MySQL database!");
});

Start your node server using node app.js. You’ll see a message in your console Connected to MySQL database!, meaning that you are now connected and can start with some query!

Our first query to test if everything is working:

db.query('SELECT * FROM users', function (err, result) {
    if (err) throw err;
    console.log(result);
  });

If a ’ users ’ table exists, this call will get us all our users.

Now, what if we want to update our MySQL database with variables obtained from the body of a NodeJS route. Assuming we have fastify installed and a route /changeusername/:id that accepts a JSON object’s payload with a name value.

const fastify = require("fastify")({ logger: true });

fastify.post("/changeUserName/:id", async (request, reply) => {
  const { name } = request.body; // name: Alan string, all SQL input need to be a string
  const { id } = request.params; // id : 123 string, if it wasn't; use JSON.stringify()

  sqlParams = [name, id];
  var sql = "UPDATE users SET name = ? WHERE id = ?";
  db.query(sql, sqlParams, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
  });
  reply.code(200)
});

For keeping the example simple, this code does not handle errors. If your code is for a product released in production, please see how to handle errors.

We can see in this snippet that we are using SQL parameters for the NodeJS call of MySQL. The critical part of keeping in mind is to declare an array of values used where you declare ? in the MySQL call. Note that all variables need to be a string. If it’s not, use JSON.stringify().

It’s important to use this and not input directly value in the SQL like this:

// BAD, vulnerable to sql injection
const name = 'Alan'
const id = '1'
sql = `UPDATE users SET name =${name} WHERE id = ${id}

The Javascript variables in NodeJS will work and successfully do the MySQL call. But it is vulnerable to SQL injection. Use previous code to protect yourself from SQL injection.

You can also escape your value like this to avoid SQL injection:

// GOOD, protected from SQL injection
const name = 'Alan'
const id = '1'
sql = `UPDATE users SET name =${db.escape(name)} WHERE id = ${db.escape(id)}

Now you know how to initialize your MySQL Database with NodeJS variables and query it without introducing the risk of SQL injection!