CODEWITHSUNDEEP
mysqlnode.jsnode-mysql
Yasin Yaqoobi
Yasin Yaqoobi

Reputation: 2040

Escaping values in Mysqljs

https://github.com/mysqljs/mysql#introduction

mysqljs is pretty inconsistent with escaping values, or I am not understanding the docs.

Error:

this.table = 'elections';
mysql.query('SELECT * FROM ? where name = ?', [this.table, this.votesTable]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax error: 'SELECT * FROM \'elections\' where name = \'prim1000\''

But this works: 

`mysql.query('UPDATE elections SET updated_at = ? WHERE name = ?', [this.getTimeStamp(), this.votesTable])

But if I remove "elections" in the query above and put "?" instead it will throw an error. So the following won't work. 

mysql.query('UPDATE ? SET updated_at = ? WHERE name = ?', [this.table, this.getTimeStamp(), this.votesTable])

Upvotes: 0

Views: 52

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562358

Referring to the documentation page you linked to, under the section "Escaping query identifiers", you should be able to do this:

mysql.query('SELECT * FROM ?? where name = ?', [this.table, this.votesTable]

Most SQL frameworks do not allow parameters to be used for anything besides individual values. I.e. not table identifies, column identifiers, lists of values, or SQL keywords. The mysqljs library is uncommon in that it has support for quoting identifiers and key/value pairs.

Re your comment:

The ?? placeholder is for identifiers. Identifiers must be quoted differently from values. In MySQL, a string value is quoted like 'string' but an identifier is quoted with back-ticks.

SELECT * FROM `mytable` where name = 'myname'

The mysqljs class uses the ?? as a special placeholder for an identifier, so you can tell the class it must be quoted with back-ticks.

Upvotes: 1

Related Questions