MySqlException when passing table name as a parameter to MySqlCommand

Question

I am writing a very simple bit of code and cannot figure out why the parameters are not working on my SQL select. Any advice would be appreciated.

I have a prior connection/select without parameters that works fine
I have tried the select in MySQL workbench it is fine (and have simplified it too) Here is the relevant code. There is an exception on the ExecuteReader statement.

This is the result from the locals window:

[MySql.Data.MySqlClient.MySqlException] {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AAIT_N'' at line 1"} MySql.Data.MySqlClient.MySqlException

MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd;
conn.Open();
try
   {
     cmd = conn.CreateCommand();
     cmd.CommandText = "SELECT * from @p ";  
     cmd.Parameters.AddWithValue("@p", sym);
     string wait = Console.ReadLine();
     MySqlDataReader myresults = cmd.ExecuteReader();   <=== Throws an exception
     if (myresults.HasRows)
etc.

If you are interested in the reason behind this, its because the parameter system escapes/cleans the values it inserts in the SQL. It is inserting your `@p` parameter with quotes, turning it into `SELECT * FROM "AAIT_N"`, including the quotes, which is an invalid table name. — Ron Beyer, Aug 03 '15 at 15:48
Thanks Ron, I actually spent 2 hours trying to figure exactly that out (I haven't written serious code in a lot of years), Rahul's response clears up the abstraction I needed. — Don Unknown, Aug 03 '15 at 15:55

score 1 · Accepted Answer · answered Aug 03 '15 at 15:40

1

No, you can't pass the table name as parameter. Rather you can do like below

 string query = string.Format("SELECT * from {0}", sym);  
 cmd.CommandText = query; 
 MySqlDataReader myresults = cmd.ExecuteReader();

answered Aug 03 '15 at 15:40

Rahul

76,197
13
71
125

2

oooohhhhh fun.... I'll pass "blah; DROP DATABASE blah;" – Jonathan Henson Aug 03 '15 at 15:43
Thank you. It throws a different error now . . .but this solved the problem. Thank you very much!! – Don Unknown Aug 03 '15 at 15:44
@DonUnknown this is probably the solution you need. But you may want to verify that sym is not coming from user input in any way before using. Otherwise, you may need to do some sanitization work first. – Jonathan Henson Aug 03 '15 at 15:45
@JonathanHenson, that's true, it's open now for SQL Injection but the answer points to the fact that table name can't be passed as parameter. A better solution would be use a procedure with a dynamic query. – Rahul Aug 03 '15 at 15:46
It's only vulnerable if this is being triggered from a UI, web request, or some other sort of input. If there are proper controls in place, it's fine. The writer of this code just needs to make that decision consciously. – Jonathan Henson Aug 03 '15 at 15:47
Thanks Jonathan. No user input here. my data on a closed system. A control freak's playground if you will :) Thanks again. – Don Unknown Aug 03 '15 at 15:47
1

I would suggest some kind of reflection to resolve the tablename on runtime. – blfuentes Aug 03 '15 at 15:48
@JonathanHenson, Yes but either way; if I had to do this; I would use a procedure which takes table name as parameter and build a dynamic query on that. – Rahul Aug 03 '15 at 15:48
@Rahul, that's certainly the safest approach. – Jonathan Henson Aug 03 '15 at 15:49

score 0 · Answer 2 · answered Aug 03 '15 at 15:42

I don't think you can use a table name as a parameter.

MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd;
conn.Open();
try
   {
     cmd = conn.CreateCommand();
     cmd.CommandText = "SELECT * from someTable where firstName = @p ";  
     cmd.Parameters.AddWithValue("@p", "yourValue");
     Console.ReadLine();
     MySqlDataReader myresults = cmd.ExecuteReader();
     if (myresults.HasRows)
etc.

MySqlException when passing table name as a parameter to MySqlCommand

2 Answers2