2

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

Stecya
  • 22,896
  • 10
  • 72
  • 102

1 Answers1

5

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

Community
  • 1
  • 1
Delta76
  • 13,931
  • 30
  • 95
  • 128
  • 2
    -1. this is bad approach to use pure string, cause i could pass `tableName = "users ; DROP TABLE users;"` , and this will drop table – Stecya May 18 '11 at 08:08
  • User will input tablename. I prefer not to use stored procedure, need some solution on client side – Stecya May 18 '11 at 08:35
  • Thanks for help. I put regex validation on client input – Stecya May 19 '11 at 10:48
  • Why not populate a drop-down with all available/eligible tables which the user can pick from, eliminates the whole injection thing. – Amund Midtskog Jan 05 '17 at 11:28