Escaping values in Mysqljs

Question

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])

What is the question? :-)

Craig van Tonder
– Craig van Tonder

2018-02-27 22:17:21 +00:00
Commented Feb 27, 2018 at 22:17 — Craig van Tonder
– Craig van Tonder, Commented Feb 27, 2018 at 22:17

Bill Karwin · Accepted Answer · 2018-02-28 00:47:02Z

1

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.

edited Feb 28, 2018 at 0:47

answered Feb 27, 2018 at 22:25

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Yasin Yaqoobi Over a year ago

This works but I am not sure what's the difference between ?? and ?.

Collectives™ on Stack Overflow

Escaping values in Mysqljs

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related