0

As a reaction to MySqlParameter as TableName, it seems that MySQL's Connector/NET library doesn't support table names from user input in the following way:

MySqlCommand cmd = new MySqlCommand("SELECT * FROM @table");
cmd.Parameters.AddWithValue("@table",TableNameFromUserInput);

So I tried to find another way, but I couldn't find any libraries that did this for me.

When searching for how to do this manually, I couldn't found anything that didn't tell you to use "prepared statements" from some already defined library, no matter the language.

To my limited knowledge of SQL, I only think it's necessary to put backticks (`) around the user input and then check the input for backticks - but I wanted to be sure.

Question

How do you protect a user specified table name from SQL-Injection when the MySQL Connector doesn't support it?

Community
  • 1
  • 1
Aske B.
  • 6,419
  • 8
  • 35
  • 62

2 Answers2

5

Check if

TableNameFromUserInput

is an existing table, before executing the query. That prevents other errors like "table doesnt exist" aswell.

CSharpie
  • 9,195
  • 4
  • 44
  • 71
  • 1
    I like this idea, because you can paramterize the table name for *this* command. – hometoast Nov 21 '12 at 15:08
  • It seems like a possible solution, but it requires an additional query for all queries that use this query, and in my case it's going to be executed 10 times a second or more. – Aske B. Nov 21 '12 at 15:43
  • 2
    @Aske You can cache a list of existing table names - they won't change fast normally... – Jan Nov 22 '12 at 12:34
1

you cannot add tableName (as well as columnName) as parameter in prepared statements because it only supports for a value. For your safety, add additional code to validate tableName, ex.

string tableName = "hello";
tableName = UDFunctionClean(tableName);
string query = String.Format("SELECT * FROM `{0}`", tableName);
MySqlCommand cmd = new MySqlCommand(query);
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • What should the `UDFunctionClean` do? Escape backticks and that's it? – Aske B. Nov 21 '12 at 15:07
  • maybe, actually its up to you, check for illegal characters such as single quote as tableName cannot contain single quotes. – John Woo Nov 21 '12 at 15:08
  • Well, I don't care if they try to use a table called `\`; DROP Database dbName; SELECT * from \``, I just want to make it secure from non-standard requests. Are there any other ways to do SQL-injection than the backtick? – Aske B. Nov 21 '12 at 15:49
  • I realized I also needed to escape, escape characters (`\ `). Otherwise the user could just escape the first escape character (`\\`; Injected_Query_Here; SELECT * FROM \`table` turns into `SELECT * FROM \`\\\`; Injected_Query_Here; SELECT * FROM \`table\``). – Aske B. Nov 22 '12 at 10:40