如何在ODBC查询中参数化表名 [英] How to parameterise table name in ODBC query

问题描述

我与数据库有ODBC连接，我希望用户能够查看任何表中的数据.因为这是一个ASP.net应用程序，所以我不能相信发送的表名也不包含虚假名称.我尝试使用参数化查询，但总是收到一条错误消息，说我"必须声明表变量"-这似乎是个问题，因为它是表名

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?

推荐答案

使用存储过程，这是最安全的方法.

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

一些提示:

1. 您可能还可以使用System.Data.SqlClient命名空间对象
2. 在using语句中包含连接，命令和适配器对象的初始化

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

这是一个简单的例子:

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);
        }
    }
}

另一种可行的方法是检索所有表名，并将tableName字符串变量验证为列表中的一项，也许使用:

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
    }
}

这篇关于如何在ODBC查询中参数化表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！