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
– Ken White, Commented Nov 29, 2011 at 21:32
possible duplicate of 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? — mellamokb
– mellamokb, Commented Nov 29, 2011 at 21:38

Andreas Wederbrand · Accepted Answer · 2011-11-29 21:39:08Z

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, 2011 at 21:39

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

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

3 Comments

Andreas Wederbrand Over a year ago

and you kinda have the answer in your previous question stackoverflow.com/questions/8317472/… :)

Nyxynyx Over a year ago

Right, I was wondering if this applies to MySQL queries in general like in PHP, or is this node.js specific

Andreas Wederbrand Over a year ago

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.

Community · Accepted Answer · 2017-05-23 11:59:38Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 30, 2011 at 0:01

malletjo

1,78616 silver badges19 bronze badges

2 Comments

Nyxynyx Over a year ago

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?

malletjo Over a year ago

I would say yes, placeholders are normally for dynamic data (parameters)

Collectives™ on Stack Overflow

Variable in MySQL Query causes error

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related