CODEWITHSUNDEEP
mysqlsqlnode.js
Kushagra Agarwal
Kushagra Agarwal

Reputation: 11

how to replace all the single quotes to \' for a mysql query

I have made a simple query using nodejs and mysql. The issue is whenever the user inputs product details containing any single quote an error occurs. How do i replace or remove such error.

Upvotes: 1

Views: 6839

Answers (3)

Loga Nathan
Loga Nathan

Reputation: 133

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.

Upvotes: 3

Federico B.
Federico B.

Reputation: 189

You must escape the input values as reported in the documentation of mysql for nodejs using escape function.

Upvotes: 2

AZinkey
AZinkey

Reputation: 5329

node-mysql this is a good library which auto escape query values check it out https://github.com/mysqljs/mysql#escaping-query-values

Upvotes: 1

Related Questions