SQL table parameterizing is not allowed?

Question

I am trying to use some parameters in my sql query like this;

    Answer_table = new MySqlParameter("@answerTable", AT);
    MySqlCommand solved_q = new MySqlCommand("SELECT * FROM @answerTable WHERE UserID = @uID", c2.get_con());
    solved_q.Parameters.Add(Answer_table);
    solved_q.Parameters.Add(uID);

but It's not working as I expected. It works for uID when I dont use a parameter for table name(@answerTable), is it because it is not allowed? if it is is there any other way to do that?

any help would be appreciated :)

duplicate: http://stackoverflow.com/questions/6041496/mysqlparameter-as-tablename — manji, Jun 21 '12 at 16:05
Parameters in the from part of a SQL statement are not allowed. You'll have to concatenate it into the string rather than using a parameter. Be sure that if the table name is tied to user input, you do appropriate escaping to guard against SQL injection attacks. — JamieSee, Jun 21 '12 at 16:12
@JamieSee Or as vimvq1987 suggested in [this answer](http://stackoverflow.com/a/6041545/119477) you can just validate that the value of `@answertable` exists in the DB before execution. — Conrad Frix, Jun 21 '12 at 16:16

score 3 · Answer 1 · answered Jun 21 '12 at 16:05

You cannot specify the FROM clause using a parameter.

You can construct the SQL statement by building the actual string:

new MySqlCommand(String.Format("SELECT * FROM {0} WHERE UserID = @uID"
                 , answertable)
                 , c2.get_con());

Note - be sure to guard against SQL injection attacks by not allowing manipulation of the ansertable variable.

score 2 · Accepted Answer · answered Jun 21 '12 at 16:07

It's not allowed, because it's part of the information the DBMS needs in order to compile its execution path for a prepared statement.

You would have to build the statement as a string at runtime, before you prepare it. Fortunately, most DBMS would still cache your prepared statemtent.

Important caveat: if the name of the table comes "from the outside" in any way (e.g. a parameter in a HTML request), you will have to be very careful to avoid SQL injection (i.e.: someone could pass something evil like "table1 ; drop table table1" instead of what you expect, and that would change the meaning of your SQL statement in unexpected ways).

Olivier Jacot-Descombes · Answer 3 · 2012-06-21T16:11:40.980

0

Try

MySqlCommand solved_q = new MySqlCommand(
    String.Format("SELECT * FROM {0} WHERE UserID = @uID", AT),
    c2.get_con());
solved_q.Parameters.AddWithValue("@uID", uID);

Since AT is determined programmatically (I hope) and not entered by the user, you will not have a SQL-injection issue here.

edited Jun 21 '12 at 16:11

answered Jun 21 '12 at 16:06

Olivier Jacot-Descombes

104,806
13
138
188

score 0 · Answer 4 · answered Sep 07 '12 at 19:57

By the sounds of it you're doing a SELECT, so you're not creating a new table... that being said, the tables that are in the db at time of query are already known. Take advantage of this fact by querying the db for a list of valid tables. Populate a list first, then validate the tablename that you've been provided with for sanity. If it's in the valid list, then make a string and execute it against the db.

SQL table parameterizing is not allowed?

4 Answers4