Nodejs mysql escape single quote

From my understanding you want to Escape query values when you perform the mysql operations i don't know which mysql driver you are using but i will suggest some solutions with the node.js driver for mysql.

On this nodejs mysql drive builtin mechanism for Escaping query values.In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods:

Caution These methods of escaping values only works when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).

var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
  if (error) throw error;
  // ...
});

Alternatively, you can use ? characters as placeholders for values you would like to have escaped like this:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {
  if (error) throw error;
  // ...
});

Multiple placeholders are mapped to values in the same order as passed. For example, in the following query foo equals a, bar equals b, baz equals c, and id will be userId:

connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
  if (error) throw error;
  // ...
});

This looks similar to prepared statements in MySQL, however it really just uses the same connection.escape() method internally.I hope this will helps you.

If I do the following:

I get the expected output of: tmp/myValue

But the following code:

console.log(connection.escape(value));

It outputs 'tmp/myValue'. Notice the single quotes that are now around the value.

Is there a reason for this or is it a bug?

Hi! I'm afraid that is the definition of escaping a string in MySQL: surround value with quotes (and escape any inner quotes).

I have definitely heard of escaping inner quotes being part of escaping a string, but adding quotes around the string is new to me. Do you know of any where in MySQL's documentation that says this is part of escaping a string, because I can't find anything? Also, what type of attack would this even protect against?

Perhaps you are missing the purpose of the connection.escape function provided by this library. Here is the intended use:

// Interpolating strings without using connection.escape
console.log('SELECT * FROM user WHERE username = ' + username + ' AND password = ' + password);
// SELECT * FROM user WHERE username = john smith AND password = jsm1965

// Interpolating strings correctly using connection.escape
console.log('SELECT * FROM user WHERE username = ' + connection.escape(username) + ' AND password = ' + connection.escape(password));
// SELECT * FROM user WHERE username = 'john smith' AND password = 'jsm1965'

Under the documentation for "Escaping Query Values" it states:

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the connection.escape() or pool.escape() methods

So it seems like the intended purpose is to protect against SQL Injection attacks, not to make appending parameters easier. Thoughts?

That's exactly right. Consider not using connection.escape vs using it:

// Variable collected from a client
var username = "jim"
var password = "pass' OR '1'='1";

// Interpolating strings without using connection.escape and "trying" to quote them:
console.log("SELECT * FROM user WHERE username = '" + username + "' AND password = '" + password + "'");
// SELECT * FROM user WHERE username = 'jim' AND password = 'pass' OR '1'='1'

// Interpolating strings correctly using connection.escape
console.log('SELECT * FROM user WHERE username = ' + connection.escape(username) + ' AND password = ' + connection.escape(password));
// SELECT * FROM user WHERE username = 'jim' AND password = 'pass\' OR \'1\'=\'1'

The reason the quotes are added around your strings in connection.escape is so you don't have to remember which type of quotes (single or double) the function is escaping AND so numbers work correctly. Consider numbers:

// Query for an ID
console.log('SELECT * FROM book WHERE id = ' + connection.escape(id));
// SELECT * FROM book WHERE id = 5

// If you got a string in id from the client:
// SELECT * FROM book WHERE id = 'string!'
// So you can see the value of escape quoting your string

Feel free to reopen, if you have further questions.

This is great, although it does cause problems when trying to do things like:

console.log(`SELECT * FROM book where name LIKE '%${connection.escape(searchName)}%'`)
// SELECT * FROM book where name LIKE '%'SEARCH_NAME'%'

// You get those extra quotes inside the LIKE ...

@tarwin See #1386 about escaping Values for LIKE.
The example should be in the Docs. There will be someone taking care of that soon(tm), I guess.

@kai-koch I realized I could simply do:

let searchName = 'SEARCH_NAME'
let searchLike = `%${searchName}%`
console.log(`SELECT * FROM book where name LIKE ${connection.escape(searchLike)}`)
// SELECT * FROM book where name LIKE '%SEARCH_NAME%'

How do I escape a quote in MySQL?

How do I remove a single quote from a MySQL query?

How do you escape a single quote in SQL?

How do I allow a single quote in a MySQL query?