变量表名使用动态SQL在C＃ [英] Variable table name using dynamic SQL in C#

问题描述

我一直在寻找一种输入变量表名称的方法，似乎最好的方法是使用动态sql，尽管它可以导致SQL注入。任何人都可以证明这是如何在C＃中完成的？例如，我想要一些实现这样的东西：

I've been looking around for a way to enter a variable table name and it seems the best way is to use dynamic sql, although it can lead to SQL injection. Can anyone demonstrate how this is done in C#? For example, I want something to implement something like this:

 SqlCommand command= new SqlCommand("SELECT x FROM @table WHERE @column = @y", conn);

从这里可以看出，表名和列名称将是变量。我以前使用字符串连接，但为了安全起见我想避免这种情况。不确定是否重要，但表和列不是由用户输入确定，而是由用户选择的链接确定，所以也许SQL注入不是问题？

As you can see from this, the table name and column name would be variables. I was using string concatenation before but I want to avoid that for security purposes. Not sure if it matters, but the table and column are not determined by user input but actually determined by the links the user selects, so maybe SQL injection isn't a problem here?

推荐答案

我想到的最好的方式是两部分。

The Best way I can think of is two part.

1. 你具有将该名称作为参数的存储过程。这个proc首先查找目录中的表名（ Information_Schema.Table ），并获得正确的名称来验证它是否有效。

1. You have a stored procedure that takes the name as a parameter. This this proc first looks up the table name in the catalog (Information_Schema.Table) and get's the proper name to verify it's a valid.

然后使用这个查找存储过程构造所需的SQL，而不是参数。

Then using this look up the stored procedure constructs the required SQL and not from the parameter.

使用这个查找，你实际上是防止传递的非有效表名。

Using this look up you are essentially protecting against non valid table names being passed in.

CREATE PROCEDURE RunSQL
    (@table NVARCHAR(50))
AS
BEGIN 
    DECLARE @validTableName NVARCHAR(50)

    SELECT 
        @validTableName = TABLE_NAME    
    FROM 
        INFORMATION_SCHEMA.TABLES
    WHERE 
        TABLE_NAME = @table
        AND SCHEMA_NAME = 'dbo' -- here you can limit or customise the schema

    IF @validTableName IS NULL
        RAISE ... raise an exception

    DECLARE @dynamicSql NVARCHAR(100) = REPLACE('SELECT * FROM dbo.[#TABLE#]', '#TABLE' @validTableName) 

    EXECUTE sp_executesql .... @dynamicSql ....
END

您可以使用extend this来验证，模式，列等...

You can use extend this to validate, schema, columns etc...

您最终需要构建自己的SQL并执行防止注射。没有得到解决。

You ultimately need to construct your own SQL and do the protection against injection. There is no getting around that.

这篇关于变量表名使用动态SQL在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！