What's the best way to choose a table name dynamically at runtime?

Question

I am using MySQL Connector/Net and I want to write a query against a table whose name will be specified at runtime.

This example is off the top of my head (not tested):

public class DataAccess
{
    public enum LookupTable
    {
        Table1,
        Table2,
        Table3
    }

    public int GetLookupTableRowCount(LookupTable table)
    {
        string tableName = string.Empty;

        switch (table)
        {
            case LookupTable.Table1: 
                tableName = "table_1";
                break;
            case LookupTable.Table2: 
                tableName = "table_2";
                break;
            case LookupTable.Table3: 
                tableName = "table_3";
                break;
            default:
                throw new ApplicationException("Invalid lookup table specified.");
        }

        string commandText = string.Concat("SELECT COUNT(*) FROM ", tableName);

    // Query gets executed and function returns a value here...
    }
}

Since I don't think you can parameterize a table name in a query, I used an enum rather than a string in the function parameter to limit the possibility of SQL injection.

Does that seem like a good approach? Is there a better way?

Answer 1

You can't paramaterize an identifier (table name or field name) in MySQL, however, you can escape them using backticks.

The following query will run safely but produce an error because the table doesn't exist (unless by some weird chance you actually have a table named like this):

SELECT * FROM `users; DROP TABLE users;`;

Basically, you can use dynamic names or fields as long as they are enclosed in backticks. In order to prevent SQL injection this way, all you need to do is strip out any backticks first:

tableName = tableName.Replace("`", "");
string commandText = "SELECT COUNT(*) FROM `" + tableName + "`";