Node.js: Preventing SQL Queries from SQL Injections

Question

I a developing a REST API with Node.js, using AWS Lambda and API Gateway. I am using MySQL database. I am a Java guy and very new to Node.JS. Just one day old.

I realised that there are multiple ways to prevent SQL INjection in Node.js. Below is my code

const mysql = require('mysql');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

const con = mysql.createConnection({
  host     : prop.get('server.host'),
  user     : prop.get("server.username"),
  password : prop.get("server.password"),
  port     : prop.get("server.port"),
  database : prop.get("server.dbname")
});


exports.getRoleByID = (event, context, callback) => {

  const { id } = event.queryStringParameters;
  console.log("id", id);

  // allows for using callbacks as finish/error-handlers
  context.callbackWaitsForEmptyEventLoop = false;
  const sql = "select * from role where idrole = ?";
  con.query(sql, [id], function (err, result) {
    if (err) throw err;

    var response = {
      "statusCode": 200,
      "headers": {
        "Content-Type": "application/json"
      },
      "body": JSON.stringify(result),
      "isBase64Encoded": false
    };
    callback(null, response)
  });
};

As you can see, I am using ? to apply the SQL Injection protection. But I also noticed that doing something like this will give the protection

var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);

Which way is used to protect the code from SQL Injections? The way I used or the connetion.escape() way? Or some other way?

AKX · Accepted Answer · 2021-07-23 06:47:56Z

2

Using ? placeholders is simpler to read and write and is likely to offer increased performance, since the parameters can be encoded more efficiently (e.g. numbers could be sent over the wire as their binary representations, not as strings).

answered Jul 23, 2021 at 6:47

AKX

171k16 gold badges146 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PeakGen Over a year ago

Appreciate your answer. To simply what you say, you are saying what I have done is right? Because in Java we use something called PreparedStatements, but no idea about node. The ? looks very similar to the Java way anyways.

Sirko Over a year ago

It's prepared statements here as well. For your library, see npmjs.com/package/mysql#preparing-queries .

AKX Over a year ago

If you use node-mysql2 instead of mysql, it will actually use real prepared statements: github.com/sidorares/node-mysql2/blob/master/documentation/… (The mysql library you're using doesn't, it seems.)

AKX Over a year ago

@Sirko Based on the same document, it looks like mysql doesn't use real prepared statements under the hood. ("This looks similar to prepared statements in MySQL, however it really just uses the same connection.escape() method internally.")

PeakGen Over a year ago

Thanks AKX. Can you update your answer with suggestions about the mysql2 and its prepared statements?. I also noticed it has more userbase than the mysql lib. Now I am using the mysql2

Collectives™ on Stack Overflow

Node.js: Preventing SQL Queries from SQL Injections

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related