Variable in MySQL Query causes error

Question

I have a node.js app using node-mysql to query a MySQL database.

Problem: It appears that when I make the table name in the query a variable, things stop working. Did I miss out on something?

Working Node Code

client.query('SELECT * from tableA',
                function(error, results, fields) {
                    if (error)
                        throw error;
                    callback(results);
                });

Non-working Node Code

client.query('SELECT * from ?',
                [ tableA ],
                function(error, results, fields) {
                    if (error)
                        throw error;
                    callback(results);
                });

Tablenames can't usually be passed as parameters. You need to use dynamic SQL instead. — Ken White, Nov 29 '11 at 21:32
possible duplicate of [*use a variable for table name in mysql sproc*](http://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc) and [*In MySQL: How to pass a table name as stored procedure and/or function argument?*](http://stackoverflow.com/questions/2977356/in-mysql-how-to-pass-a-table-name-as-stored-procedure-and-or-function-argument) — mellamokb, Nov 29 '11 at 21:38

score 3 · Accepted Answer · answered Nov 29 '11 at 21:39

3

You could probably just append the table name to the string (pseudo code, I don't know node.js)

client.query('SELECT * from ' + [tablaA],
                function(error, results, fields) {
                    if (error)
                        throw error;
                    callback(results);
                });

answered Nov 29 '11 at 21:39

Andreas Wederbrand

38,065
11
68
78

and you kinda have the answer in your previous question http://stackoverflow.com/questions/8317472/inserting-variables-into-sql-query-for-node-mysql :) – Andreas Wederbrand Nov 29 '11 at 21:41
Right, I was wondering if this applies to MySQL queries in general like in PHP, or is this node.js specific – Nyxynyx Nov 29 '11 at 22:09
1

In Mysql table names are not allowed to be variables (?) in prepared statements and I guess that nodejs is doing a prepared statement in the background. – Andreas Wederbrand Nov 29 '11 at 22:27

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

They reason why it's not working is pretty clear.

The query from the non-working code will be :

SELECT * from 'tableA'

A solution is the one from @Andreas, but you will have the same problem in a where statement or insert for other values you don't want to be escaped like "null" value. ( convert into a string)

Same problem here

Check out the source how format && escape from node-mysql works.

edited May 23 '17 at 11:59

Community

1
1

answered Nov 30 '11 at 00:01

malletjo

1,766
16
18

1

Am i right to say that table names, database names, column names cannot be escaped, while values to be added to the columns and MySQL functions can? – Nyxynyx Nov 30 '11 at 01:25
I would say yes, placeholders are normally for dynamic data (parameters) – malletjo Nov 30 '11 at 01:36

Variable in MySQL Query causes error

2 Answers2