使用动态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.

然后，使用此查找storeprocedure构建所需的SQL而不是从参数

Then using this look up the storeprocedure 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

您可以使用此扩展来验证，架构，列等等......

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

您最终需要构建你自己的SQL并针对注射的保护。周围有没有得到。

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