How to parameterise table name in ODBC query

问题:

I have an ODBC connection to a database and I would like the user to be able to view data within any table. As this is an ASP.net application I cannot trust that the table name sent doesn't also contain nasties. I have tried using a parameterised query but I always get an error saying that I "Must declare the table variable" - this appears to be an issue because it is the table name

 string sql = "SELECT TOP 10 * FROM ? ";
 OdbcCommand command = new OdbcCommand(sql, dbConnection);
 command.Parameters.Add(new OdbcParameter("@table", tableName));
 OdbcDataAdapter adapter = new OdbcDataAdapter();
 adapter.SelectCommand = command;
 adapter.Fill(tableData);

What is the best method to achieve this in a secure way?

回答1:

Use a stored procedure, it's the safest way.

Some hints:

1. You probably may also use the System.Data.SqlClient namespace objects
2. Enclose your connection, command and adapter objects initializations in using statements

Here's a simple example:

string sqlStoredProcedure = "SelectFromTable";
using (OdbcConnection dbConnection = new OdbcConnection(dbConnectionString))
{
    dbConnection.Open();
    using (OdbcCommand command = new OdbcCommand(sqlStoredProcedure, dbConnection))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new OdbcParameter("@table", tableName));
        using (OdbcDataAdapter adapter = new OdbcDataAdapter(command))
        {
            adapter.SelectCommand = command;
            adapter.Fill(tableData);
        }
    }
}

Another way to go would be to retrieve all table names and validate the tableName string variable as an entry in the list, maybe using:

DataTable tables = dbConnection.GetSchema(OdbcMetaDataCollectionNames.Tables);

Here's a simple implementation based on your scenario:

string sql = "SELECT TOP 10 * FROM {0}";
using (OdbcConnection dbConnection = new OdbcConnection(dbConnectionString))
{
    dbConnection.Open();

    DataTable tables = dbConnection.GetSchema(OdbcMetaDataCollectionNames.Tables);
    var matches = tables.Select(String.Format("TABLE_NAME = '{0}'", tableName));

    //check if table exists
    if (matches.Count() > 0)
    {
        using (OdbcCommand command = new OdbcCommand(String.Format(sql, tableName), dbConnection))
        {
            using (OdbcDataAdapter adapter = new OdbcDataAdapter(command))
            {
                adapter.SelectCommand = command;
                adapter.Fill(tableData);
            }
        }
    }
    else
    {
        //handle invalid value
    }
}