什么时候应该使用唯一约束而不是唯一索引？

Question

irimias

Asked: 2017-04-15 00:29:57 +0800 CST2017-04-15 00:29:57 +0800 CST 2017-04-15 00:29:57 +0800 CST

防止动态 SQL 中的 SQL 注入

772

让我们想象一个检索数据并进行某种分页的存储过程。这个过程有一些输入，描述了我们想要哪组数据以及我们如何对其进行排序。

这是一个非常简单的查询，但我们以它为例。

create table Persons(id int, firstName varchar(50), lastName varchar(50))
go
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

它应该是这样使用的：

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc'

但是一个聪明的人可以推出：

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = ''

...并删除数据

这显然不是一个安全的情况。我们怎么能防止它呢？

注意：这个问题不是关于“这是一种进行分页的好方法吗？” 也不是“做动态sql是一件好事吗？”。问题是关于在动态构建 sql 查询时防止代码注入，以便在将来我们必须再次执行类似的存储过程时有一些指导方针使代码更干净。

一些基本的想法：

验证输入

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

传递 id 而不是字符串作为输入

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0
as

declare @orderByName varchar(50)
set @orderByName =  case @orderBy when 1 then 'id'
                        when 2 then 'firstName'
                        when 3 then 'lastName'
                    end 
                +' '+case @orderDir 
                        when 0 then 'desc' 
                        else 'asc' 
                    end

if @orderByName is null
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderByName

exec(@sql)

还有其他建议吗？

5 个回答

Voted

AMtwo · Answer 1 · 2017-04-15T05:45:58+08:00

在您的示例代码中，您将三类“事物”传递到动态 SQL 中。

您传递了@OrderDir，这是一个表示ASCor的关键字DESC。
你传递了@OrderBy，它是一个列名（或者可能是一组列名，但根据#1 的实现方式，我假设你期望一个列名。
您传递@PageNumberand @PageSize，它成为生成字符串中的文字。

关键词

这真的很简单——您只想验证您的输入。你很清楚这是这个选项的正确选择。在这种情况下，您期望的是ASCor DESC，因此您可以检查用户是否传递了其中一个值，或者您可以切换到不同的参数语义，其中您有一个参数是拨动开关。将您的参数声明为@SortAscending bit = 0，然后在您的存储过程中，将该位转换为ASC或DESC。

列名

在这里，您应该使用该QUOTENAME功能。Quotename 将确保对象被正确地[引用]，确保如果有人试图传入“; TRUNCATE TABLE USERS”的“列”，它将被视为列名，而不是任意注入的代码。这将失败，而不是截断USERS表：

SELECT [; TRUNCATE TABLE USERS]...
FROM...

文字和参数

对于@PageNumberand @PageSize，您应该使用sp_executesql正确传递参数。正确参数化您的动态 SQL 不仅可以将值传入，还可以将值取回。

在此示例中，@x和@y将是您的存储过程范围内的变量。它们在您的动态 SQL 中不可用，因此您将它们传递到@aand @b，它们的作用域是动态 SQL。这允许您在动态 SQL 内部和外部都具有正确键入的值。

DECLARE @i int,
 @x int,
 @y int,
 @sql nvarchar(1000),
 @params nvarchar(1000);


SET @x = 10;
SET @y = 5;
SET @params = N'@i_out int OUT, @a int, @b int';
SET @sql    = N'SELECT @i_out = @a + @b';


EXEC sp_executesql @sql, @params, @i_out = @i OUT, @a = @x, @b = @y; 
SELECT @i;

即使使用 varchar 值，将值保留为变量也可以防止某人任意传递被执行的代码。此示例确保用户输入得到SELECT编辑，而不是任意执行：

DECLARE @UserInput varchar(100),
         @params nvarchar(1000) = N'@value varchar(100)',
         @sql nvarchar(1000)    = N'SELECT Value = @value';

SET @UserInput = '; TRUNCATE TABLE USERS;'
EXEC sp_executesql @sql, @params, @value = @UserInput;

我的代码

这是我的存储过程版本，带有表定义和一些示例行：

CREATE TABLE dbo.Persons
       (
        id INT,
        firstName VARCHAR(50),
        lastName VARCHAR(50)
       );
GO

INSERT INTO dbo.Persons(id, firstName,lastName)
VALUES (1,'George','Washington'),
       (2,'John','Adams'),
       (3,'Thomas','Jefferson'),
       (4,'James','Madison'),
       (5,'James','Monroe')


ALTER PROCEDURE dbo.GetPersons
       @pageNumber INT = 1,
       @pageSize INT = 20,
       @orderBy VARCHAR(50) = 'id',
       @orderDir VARCHAR(4) = 'desc'
AS
       SET NOCOUNT ON;

--validate inputs
IF NOT EXISTS ( SELECT   1 FROM     sys.columns
                WHERE    object_id = OBJECT_ID('dbo.Persons')
                AND name = @orderBy )
BEGIN
        RAISERROR('Order by column does not exist.', 16,1);
        RETURN;
END;

IF (@orderDir NOT IN ('ASC', 'DESC'))
BEGIN
        RAISERROR('Order direction is invalid. Must be ASC or DESC.', 16,1);
        RETURN;
END;

--Now do stuff
--sp_executesql takes in nvarchar as a datatype
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT id, firstName, lastName
FROM (
    SELECT id, firstName, LastName, ROW_NUMBER() OVER(ORDER BY '
           + QUOTENAME(@orderBy) + N' ' + @orderDir + N') AS rn
    FROM dbo.Persons
    ) t
WHERE rn > ( @pageNumber-1) * @pageSize
        AND rn <= @pageNumber * @pageSize 
ORDER BY ' + QUOTENAME(@orderBy) + N' ' + @orderDir;

EXEC sys.sp_executesql @sql, N'@pageNumber int, @pageSize int',
                   @pageNumber = @pageNumber, @pageSize = @pageSize;

GO

您可以在此处看到，代码功能正常，并为您提供正确的排序和分页：

EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'DESC';
EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'ASC';
EXEC dbo.GetPersons @OrderBy = 'firstName';
EXEC dbo.GetPersons @OrderBy = 'lastName';
EXEC dbo.GetPersons @PageNumber = 2, @PageSize = 1, @OrderBy = 'lastName', @orderDir = 'ASC';

并了解输入处理如何防止有人尝试做奇怪的事情：

EXEC dbo.GetPersons @OrderBy = 'lastName', @orderDir = 'UP';
EXEC dbo.GetPersons @OrderBy = ';TRUNCATE TABLE Persons;';

附加阅读

sp_executesql 示例

Aaron Bertrand 要改掉的坏习惯：使用 EXEC() 代替 sp_executesql

Aaron Bertrand 的厨房水槽程序

Scott Hodgin - Retired · Answer 2 · 2017-04-15T01:30:32+08:00

缓解 SQL 注入的一种常用方法是QUOTENAME围绕传递到存储过程的变量使用。

因此，在您的示例中，可以像这样修改代码：

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+ Quotename(@orderBy)+' '+@orderDir

如果有人试图传入额外的“删除”命令，执行会出错，因为生成的动态 SQL 如下所示：

select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by id)a from Persons)t;delete from Persons;print' ) as rn
    from Persons
    ) t
where rn > (1-1) * 20
        and rn <= 1 * 20 
order by [id)a from Persons)t;delete from Persons;print']

导致此错误：

消息 102，级别 15，状态 1，第 27 行
']' 附近的语法不正确。

此外，Aaron Bertrand 有一篇很棒的博客，关于要踢的坏习惯：使用 EXEC() 而不是 sp_executesql

Daniel Hutmacher · Answer 3 · 2017-04-15T01:35:26+08:00

一个明显的解决方案是不使用动态 SQL。我认为您的任务可以使用常规的非动态 T-SQL 代码来完成，这在安全性（如所有权链接）方面也为您提供了其他优势。

所以而不是：

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderByName

exec(@sql)

例如，你可以..

SELECT id, firstName, lastName
FROM (
    SELECT id, firstName, lastName, ROW_NUMBER() OVER (
        ORDER BY (CASE @OrderByName
                  WHEN 1 THEN id END),
                 --- different datatypes, I'm assuming
                 (CASE
                  WHEN 2 THEN firstName
                  WHEN 3 THEN lastName END)) AS rn
    FROM persons
    ) AS t
WHERE rn > (@pageNumber-1) * @pageSize
  AND rn <= @pageNumber * @pageSize
ORDER BY rn;

继续阅读，

John Eisbrener · Answer 4 · 2017-04-15T05:47:21+08:00

@Scott Hodgin 的回答涉及到这一点，但基本上在生成面向客户端/应用程序的动态 SQL 字符串时，最好的方法是使用sp_executesql。

虽然消除 SQL 注入攻击并非完全万无一失，但 sp_executesql 可能是您将获得的最好的。Aaron Bertrand 的 Scott 链接到的文章非常直截了当，但要快速总结 sp_executesql 相对于其他方法的好处是：

在字符串中使用强类型变量
有更好的查询计划重用机会

第一点是我认为与您的问题相关的最重要的一点，因为您可以限制参数的长度、类型等。这使得注入讨厌的代码变得异常困难。

为了提供更完整的答案，我已经相应地更新了您的 sp。有趣的是，在您的情况下，因为您试图参数化列文字，您需要嵌套 sp_executesql 语句，因此第一个嵌套语句将列名设置为文字，第二个执行传入分页值，如下所示：

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql nvarchar(max), @sql_out nvarchar(max)
set @sql = 'SELECT @sql_out = ''select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '' + @oB + '' '' + @oD + '') as rn
    from Persons
    ) t
where rn > (@pN -1) * @pS
        and rn <= @pN * @pS
order by '' + @oB + '' '' + @oD + '''''

--PRINT(@sql)
EXEC sp_executesql @sql, N'@oB varchar(50), @oD varchar(4), @sql_out nvarchar(max) OUTPUT', @oB=@orderBy, @oD=@orderDir, @sql_out=@sql_out OUTPUT
EXEC sp_executesql @sql_out, N'@pN int, @pS int', @pN=@pageNumber, @pS=@pageSize

Rob Farley · Answer 5 · 2017-04-17T16:52:29+08:00

Rob Farley

2017-04-17T16:52:29+08:002017-04-17T16:52:29+08:00

简单选项 - 加入以sys.columns确保它是有效的列名，并使用CASE默认为ASC是否传入其他任何内容DESC。

（哦，使用nvarchar(max)for@sql和sp_executesql）

declare @sql nvarchar(max)
select @sql = 'select id, firstName, lastName
  from (
    select id, firstName, LastName, row_number() over(order by '+QUOTENAME(c.name)+' '+ CASE WHEN @orderDir = 'DESC' THEN 'DESC' ELSE 'ASC' END +') as rn
    from Persons
    ) t
  where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
    and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+QUOTENAME(c.name)+' '+ CASE WHEN @orderDir = 'DESC' THEN 'DESC' ELSE 'ASC' END
FROM sys.columns c
WHERE c.name = @orderby
AND c.object_id = OBJECT_ID('dbo.Persons');

EXEC sp_executesql @sql;

0

防止动态 SQL 中的 SQL 注入

关键词

列名

文字和参数

我的代码

附加阅读

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

防止动态 SQL 中的 SQL 注入

5 个回答

关键词

列名

文字和参数

我的代码

附加阅读

相关问题