1

As an extension to How to protect user specified table name from SQL Injection in C#, using MySQL, I'm trying to figure out how to protect the user-specified-table-query from SQL Injection:

string createEventSQL = "INSERT INTO " + TableNameFromUser +
        " (" + TableColumnNames + ") " +
        "VALUES(" + ParametrizedTableColumnParams + ")";

To be clear: I would love to use a predefined library to parametrize the input, but I can't find one.

I don't want additional queries (e.g. SHOW TABLES LIKE @TableNameFromUser;) to secure this, since performance is an issue.

It seems to me that many people claim that it isn't possible to to make a 100% secure solution, but that doesn't make sense to me, since resorting to parametrization should be just as "insecure" as doing all the work yourself. Essentially, I just want to replicate what the MySQL Connector would do, if it supported parametrized table names.

I'm not very experienced with SQL yet, but so far I've found that I need to:

What else is can be done to protect from SQL Injection?

Community
  • 1
  • 1
Aske B.
  • 6,419
  • 8
  • 35
  • 62
  • 4
    The safest way would be to use a whitelist with all existing tablenames. You don't have to query the list of tables every time - you can query it once and cache it in memory. – Jan Nov 22 '12 at 12:36
  • Consider this answer: [Table name and table field on SqlParameter C#?](http://stackoverflow.com/a/4810953/590956) – Sam May 29 '13 at 16:32

2 Answers2

1

The MySQL Ado connector supports parameters on MySqlCommand - here. As you've identified, in general, you should always pass parameters instead of mangling ad hoc SQL.

Unfortunately, this won't parameterize the table name, as per this SO post : MySqlParameter as TableName.

So it looks like you will need to validate and sanitize the table name.

Can you compare the table name against a white list? Or possibly keep a list of valid tables names somewhere else in the database?

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • That's a good idea. In combination with [Ollie's answer](http://stackoverflow.com/a/13512957/1380710) a prefix could be made to remove the necessity of maintaining the white-list (since in my case, valid tables could realistically be added from a few times in one day to once in 3 months). Now I don't know which answer to accept. – Aske B. Nov 22 '12 at 13:01
1

You can, and should, parameterize your data values. You know that.

You can't parameterize your table names and column names using SQL's prepared statement feature. However, you should establish and enforce constraints on what can be in your user-furnished table and column names. For example, you could insist that table and column names all start with a letter, and consist of between 3 and 15 characters from the set of letters, numbers, and underscores. You can easily write a checker function that will throw an exception for user-furnished names that break the constraint, and always check the names with that function when composing a query.

For the sake of performance, you've ruled out checking the table and column names against the schema in your dbms. You might want to reconsider that decision: these queries aren't as slow as you think they are. The MySQL information schema lets you do queries like this:

SELECT COLUMN_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'zip'
   AND TABLE_NAME= 'zip'

This will give you a nice list of columns in the table you've specified.

O. Jones
  • 103,626
  • 17
  • 118
  • 172