什么是动态地选择一个表名在运行时的最佳方式是什么？ [英] What's the best way to choose a table name dynamically at runtime?

问题描述

我使用的MySQL Connector / Net和我想写反对他的名字会在运行时指定一个表的查询。

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

由于我不认为你可以在查询参数表名，我用了一个枚举，而不是一个字符串的函数参数限制的 SQL注入。

这似乎并像一个好方法吗？有没有更好的办法？

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

推荐答案

您不能paramaterize一个的标识（表名或字段名）在MySQL中，但是，你可以使用反引号转义。

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;`;

基本上，你可以只要使用动态名称或领域，因为它们是用反引号。为了prevent SQL注入这样一来，所有你需要做的就是去掉任何反引号第一：

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 + "`";

这篇关于什么是动态地选择一个表名在运行时的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！