MySqlParameter as TableName

Question

I want to use MySqlParameter to pass tableName into query (to prevent slq injections)

MySqlCommand cmd = new MySqlCommand("select * from @table"), cn)
cmd.Parameters.AddWithValue("@table",TableName);

But this is not working. How can I pass tableName as parameter

P.S. I tried to change @ to ? - not working

Answer 1

You cannot pass table name as parameter. You have to use dynamic SQL to do this, so you have to string concentration to do it, for example

  MySqlCommand cmd = new MySqlCommand(String.Format("select * from {0}",tableName), cn)

But because users input the tableName, so SQL injection is possible. You can use this SQL to determine if that table exists before query anything from it:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename';

(You can perfectly parametrize this query, so SQL injection will be eliminated)

Generally, be careful of SQL injection. But if you use this internal (not expose to user), then SQL injection should not be problem.

Better, you can construct a stored procedure to deal with this, as in my another answer:

Unified SQL getter with LINQ