从SQL注入使SQL Server存储过程安全

This can be easily injected here because the @ID param can be practically anything in this SQL statement by inputting it, however, how do you prevent this exploit?

这可以很容易地在这里注入,因为@ID param通过输入它几乎可以在这个SQL语句中的任何内容,但是,你如何防止这种利用?

I prefer to specifically prevent this exploit at this level rather than application level, any suggestions?

我更喜欢在这个级别而不是应用程序级别,任何建议专门防止此漏洞利用?

CREATE PROCEDURE [dbo].[GetDataByID]
@ID bigint,
@Table varchar(150)
AS
BEGIN

Declare @SQL Varchar(1000)

SELECT @SQL = 'SELECT * FROM ' + @Table + ' WHERE ID = ' + CONVERT(varchar,@ID)

SET NOCOUNT ON;

EXEC(@sql)  
END

4 个解决方案

#1

Check this page, it has a wonderful guide to dynamic sql, and the options to execute them safely

查看此页面,它有一个很好的动态sql指南,以及安全执行它们的选项

In your case it should be like this:

在你的情况下它应该是这样的:

SELECT @SQL =  N'SELECT * FROM ' + quotename(@Table) + N' WHERE ID = @xid' 
EXEC sp_executesql @SQL, N'@xid bigint', @ID

#2

1) create a new table that will have an identity PK and contain the string table names
2) insert all/only the valid tables you will allow in your procedure
3) use this int identity PK as the input parameter value (TableID) for the stored procedure
4) in the procedure, just look up the string value (table name) from the given identity PK and you are safe to concatenate that looked up string in your query. 5) your WHERE clause is fine since you pass in an int

1)创建一个新的表,它将具有一个标识PK并包含字符串表名2)插入所有/只有你将在程序中允许的有效表3)使用这个int identity PK作为输入参数值(TableID)存储过程4)在过程中,只需从给定的标识PK中查找字符串值(表名),您就可以安全地连接查询中的查找字符串。 5)你的WHERE子句很好,因为你传入一个int

#3

I would recommend avoiding dynamic sql altogether. The problems are as follows:

我建议完全避免使用动态sql。问题如下:

The obvious injection attach scenario

明显的注入附加场景

Binary injection attacks are much smarter and can bypass traditional string escaping

二进制注入攻击更加智能,可以绕过传统的字符串转义

Performance is the big one - SQL Server is designed to manage execution plans on stored procedures and they will run faster than queries that are built dynamically. If you are using dynamic sql, there is no real benefit to using a stored procedure at all. If you want flexibility in code for selecting from multiple tables, you should consider an ORM or something to make your code easier. Considering you have to pass in the table dynamically, then I would go so far as to say there is no point to a procedure like the above and a different solution is the best option. If you are just writing against SQL (ie no ORM), then code generating seperate procs would even be a better option.

性能是最重要的 - SQL Server旨在管理存储过程的执行计划,并且它们比动态构建的查询运行得更快。如果您使用的是动态sql,则根本不使用存储过程。如果您希望从多个表中选择代码的灵活性,您应该考虑使用ORM或其他东西来简化代码。考虑到你必须动态传递表格,那么我甚至会说上面的程序没有意义,不同的解决方案是最好的选择。如果您只是针对SQL编写(即没有ORM),那么生成单独的proc的代码甚至会是更好的选择。

NOTE QUOTENAME will NOT garantee you are injection safe. Truncation injection is still possible. Read http://msdn.microsoft.com/en-us/library/ms161953.aspx before using it.

注意QUOTENAME不保证您注射安全。截断注射仍然是可能的。使用前请阅读http://msdn.microsoft.com/en-us/library/ms161953.aspx。

#4

Although I'd advice against dynamic sql in general, in this case I think you can get away with it by checking if the @Table variable contains a valid table name.

虽然我总体上建议不要使用动态sql,但在这种情况下,我认为你可以通过检查@Table变量是否包含有效的表名来逃避它。

The question is if you plan on allowing schema names and/or cross-db queries, I' assuming you don't want to go out of the db (or the server) here but do allow for different schema's (AdventureWorks shows how they can be used)

问题是,如果您计划允许模式名称和/或跨数据库查询,我假设您不想在这里退出数据库(或服务器)但允许不同的模式(AdventureWorks显示它们如何能够使用)

You MIGHT want to also include views for @Table.

您可能还希望包含@Table的视图。

It probably would be 'nice' if you also checked if the object found actually has an ID column and thrown a 'userfriendly' error if not. Optional though.

如果您还检查了找到的对象是否实际上有ID列并且如果没有则抛出“用户友好”错误,那么它可能会“很好”。虽然可选。

Just putting QuoteName() around @table will NOT protect you against everything. Although a great function, it's far from perfect. IMHO your best bet would be to parse the @Table variable, check if its contents is valid and then create dynamic sql based on the obtained parts. I started out doing most of above and surprisingly there it requires a LOT of checking for something that looks as simple as this =)

只是在@table周围放置QuoteName()不会保护你免受一切。虽然功能很强,但它还远非完美。恕我直言,你最好的办法是解析@Table变量,检查其内容是否有效,然后根据获得的部分创建动态sql。我开始做上面的大部分工作,令人惊讶的是它需要很多检查看起来像这样简单的东西=)

CREATE PROCEDURE [dbo].[GetDataByID] ( 
                                        @ID bigint,
                                        @Table nvarchar(300)
                                      )
AS

DECLARE @sql nvarchar(max)

DECLARE @server_name sysname,
        @db_name     sysname,
        @schema_name sysname,
        @object_name sysname,
        @schema_id   int        

SELECT @server_name = ParseName(@Table, 4),
       @db_name     = ParseName(@Table, 3),
       @schema_name = ParseName(@Table, 2),
       @object_name = ParseName(@Table, 1)

IF ISNULL(@server_name, @@SERVERNAME) <> @@SERVERNAME
    BEGIN
        RaisError('Queries are restricted to this server only.', 16, 1)
        Return(-1)
    END

IF ISNULL(@db_name, DB_Name()) <> DB_Name()
    BEGIN
        RaisError('Queries are restricted to this database only.', 16, 1)
        Return(-1)
    END


IF @schema_name IS NULL
    BEGIN
        IF NOT EXISTS ( SELECT *
                          FROM sys.objects
                         WHERE name = @object_name
                           AND type IN ('U', 'V') )
            BEGIN
                RaisError('Requested @Table not found. [%s]', 16, 1, @object_name)
                Return(-1)
            END

        SELECT @sql = 'SELECT * FROM ' + QuoteName(@object_name) + ' WHERE ID = @ID'
    END
ELSE
    BEGIN

        SELECT @schema_id = Schema_id(@schema_name)

        IF @schema_id IS NULL 
            BEGIN
                RaisError('Unrecognized schema requested [%s].', 16, 1, @schema_name)
                Return(-1)
            END

        IF NOT EXISTS ( SELECT *
                          FROM sys.objects
                         WHERE name = @object_name
                           AND schema_id = @schema_id
                           AND type IN ('U', 'V') )
            BEGIN
                RaisError('Requested @Table not found. [%s].[%s]', 16, 1, @schema_name, @object_name)
                Return(-1)
            END

        SELECT @sql = 'SELECT * FROM ' + QuoteName(@schema_name) + '.' + QuoteName(@object_name) + ' WHERE ID = @ID'
    END

EXEC sp_executesql @stmt   = @sql,
                   @params = N'@ID bigint',
                   @ID     = @ID

Return(0)

Supra compiles, but you might need to iron out some bugs as I didn't quite go as far as checking all code-paths.

Supra编译,但你可能需要解决一些错误,因为我没有完全检查所有代码路径。

#1