MySQL表名作为参数 [英] MySQL table name as parameter

问题描述

我正在尝试进行设置，以便将表名作为参数传递给命令文本，但是我无法使其正常工作.我四处看看，发现了这样的问题: MySQL的参数化查询C#，但我没有任何运气.

I'm trying to set up so that the table name is passed to the command text as a parameter, but I'm not getting it to work. I've looked around a bit, and found questions like this: Parameterized Query for MySQL with C#, but I've not had any luck.

这是相关代码(连接==包含连接字符串的MySqlConnection):

This is the relevant code (connection == the MySqlConnection containing the connection string):

public static DataSet getData(string table)
{
    DataSet returnValue = new DataSet();
    try
    {
        MySqlCommand cmd = connection.CreateCommand();
        cmd.Parameters.AddWithValue("@param1", table);
        cmd.CommandText = "SELECT * FROM @param1";

        connection.Open();

        MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
        adap.Fill(returnValue);
    }
    catch (Exception)
    {   
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
            connection.Close();
    }
    return returnValue;
}

如果我更改:

cmd.CommandText = "SELECT * FROM @param1";

收件人:

cmd.CommandText = "SELECT * FROM " + table;

作为一种测试方法，并且可行(我正在将XML从数据集写入控制台以进行检查).因此，我很确定问题只是以错误的方式使用了参数功能.有指针吗?

As a way of testing, and that works (I'm writing the xml from the dataset to console to check). So I'm pretty sure the problem is just using the parameter functionality in the wrong way. Any pointers?

如果我输错了，也请纠正我，但是使用Parameter功能应该提供针对SQL注入的完全保护，对吗?

Also, correct me if I'm mistaken, but using the Parameter functionality should give complete protection against SQL injection, right?

推荐答案

您可以不参数化表名，列名或任何其他数据库对象.您可以仅参数化您的值.

You can not parameterize your table names, column names or any other databse objects. You can only parameterize your values.

您需要在SQL查询中将其作为字符串串联传递，但在此之前，我建议使用 strong 验证或白名单(仅固定的 possible 组)正确的值).

You need to pass it as a string concatenation on your sql query but before you do that, I suggest use strong validation or white list (only fixed set of possible correct values).

此外，如果我输入有误，请更正我，但请使用参数 功能应提供针对SQL注入的全面保护， 对吧?

Also, correct me if I'm mistaken, but using the Parameter functionality should give complete protection against SQL injection, right?

如果您的意思是带有参数功能"的参数化语句，是的，这是正确的.

If you mean parameterized statements with "parameter functionality", yes, that's correct.

请注意，有一个名为动态SQL 支持 SELECT * FROM @tablename ，但不建议这样做

By the way, be aware, there is a concept called dynamic SQL supports SELECT * FROM @tablename but it is not recommended.

正如我们所看到的，我们可以在动态的帮助下使此过程起作用 SQL，但也应该清楚我们没有获得任何优势 在存储过程中生成动态SQL.你可以 以及从客户端发送动态SQL.因此，可以:1)如果使用SQL 语句非常复杂，您可以节省一些网络流量，然后执行 封装. 2)如我们所见，从SQL 2005开始， 处理权限的方法. 尽管如此，这是一个坏主意.

As we have seen, we can make this procedure work with help of dynamic SQL, but it should also be clear that we gain none of the advantages with generating that dynamic SQL in a stored procedure. You could just as well send the dynamic SQL from the client. So, OK: 1) if the SQL statement is very complex, you save some network traffic and you do encapsulation. 2) As we have seen, starting with SQL 2005 there are methods to deal with permissions. Nevertheless, this is a bad idea.

人们要参数化参数似乎有几个原因 表格名称.一个阵营似乎是SQL新手 编程，但具有其他语言(如C ++，VB)的经验 等参数化是一件好事.参数化表格 实现通用代码并提高可维护性的名称 像优秀的程序员一样.

There seems to be several reasons why people want to parameterise the table name. One camp appears to be people who are new to SQL programming, but have experience from other languages such as C++, VB etc where parameterisation is a good thing. Parameterising the table name to achieve generic code and to increase maintainability seems like good programmer virtue.

但是，就数据库对象而言，这是一个古老的事实 不成立.在适当的数据库设计中，每个表都是唯一的，例如 它描述了一个唯一的实体. (或者至少应该如此！)当然， 以一打或更多的查找表结束所有并不少见的情况并不少见 有一个ID，一个名称列和一些审核列.但是他们确实 描述不同的实体，并且它们的相似之处应视为 只是机会，未来的需求可能会使桌子更多 不一样.

But it is just that when it comes to database objects, the old truth does not hold. In a proper database design, each table is unique, as it describes a unique entity. (Or at least it should!) Of course, it is not uncommon to end up with a dozen or more look-up tables that all have an id, a name column and some auditing columns. But they do describe different entities, and their semblance should be regarded as mere chance, and future requirements may make the tables more dissimilar.

这篇关于MySQL表名作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！