文章/答案/技术大牛

发布

社区首页 >问答首页 >如何使用带有参数的sp_executesql来防止SQL注入？

问如何使用带有参数的sp_executesql来防止SQL注入？
EN

Database Administration用户

提问于 2022-01-02 23:23:50

回答 2查看 2.2K关注 0票数 8

下面是使用sp_executesql的动态过滤解决方案

IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO
CREATE PROC dbo.GetOrders
 @orderid AS INT = NULL,
 @custid AS INT = NULL,
 @empid AS INT = NULL,
 @orderdate AS DATE = NULL
AS
DECLARE @sql AS NVARCHAR(1000);
SET @sql = 
 N'SELECT orderid, custid, empid, orderdate, filler'
 + N' /* 27702431-107C-478C-8157-6DFCECC148DD */'
 + N' FROM dbo.Orders'
 + N' WHERE 1 = 1'
 + CASE WHEN @orderid IS NOT NULL THEN
 N' AND orderid = @oid' ELSE N'' END
 + CASE WHEN @custid IS NOT NULL THEN
 N' AND custid = @cid' ELSE N'' END
 + CASE WHEN @empid IS NOT NULL THEN
 N' AND empid = @eid' ELSE N'' END
 + CASE WHEN @orderdate IS NOT NULL THEN
 N' AND orderdate = @dt' ELSE N'' END;
EXEC sp_executesql
 @stmt = @sql,
 @params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATE',
 @oid = @orderid,
 @cid = @custid,
 @eid = @empid,
 @dt = @orderdate;
GO

在查询的第541页，上面写着

因为动态代码使用参数而不是将常量注入代码，所以它不会暴露于SQL注入攻击中。

如何在sp_executesql中使用参数来防止SQL注入？

谢谢

sql-server

dynamic-sql

sql-injection

回答 2

Database Administration用户

发布于 2022-01-03 12:28:49

双层

要回答您的问题，您需要尝试使用带有参数的sp_executesql的替代方案：

使用EXEC (没有sp_executesql)
使用sp_executesql (没有参数)

这两者都可能导致SQL注入攻击，在适当的情况下。

值得注意的是，即使完全非参数化，上述代码的风险也相对较低，因为正在传递的数据类型不是字符串类型但这还是有可能的。

字符串具有更高的恶意有效负载风险。

下面的代码示例是在不同的上下文中从我的关于使用动态SQL的介绍中获得的，但是很好地适用于您的问题。

字符串-安全

您可以安全地使用这样的代码，因为用户输入不是执行的字符串的一部分：

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @TableName sysname = N'Votes';

IF @TableName = N'Votes'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Votes AS v;'
END

IF @TableName = N'Posts'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p;'
END

EXEC(@SQLString);
GO

unsafe-strings

在本例中，用户输入被连接到执行的字符串中，而不是参数化的。这可能会造成以下问题：

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end? */
EXEC (@SQLString);

最终结果是这样执行的查询，它在Title列中搜索单个通配符，然后搜索一个额外的结果，列出数据库中的所有表。

  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656  
  AND p.Title LIKE '%' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --%' 
  ORDER BY p.Score DESC;

虽然很多人会关注模因，比如删除表，但是动态SQL的真正问题通常是数据的窃取。钱就在那儿。

still-not-safe

使用sp_executesql是很好的第一步，但是它仍然需要与参数一起使用。下面这样的代码仍然以与上面相同的方式受到SQL注入的影响。

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --';
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;
/* Check the results -- what's that at the end? */
EXEC sys.sp_executesql 
    @SQLString;

将执行与上面相同的查询。

back-to-safety

使用与示例更好地对齐的代码，我们可以通过将值赋值给参数来避免SQL注入，而不是将其直接连接到字符串中。

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL 
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE N''%'' + @Title + N''%'' ';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end now? */
EXEC sys.sp_executesql 
    @SQLString, 
  N'@Title NVARCHAR(250)', 
    @Title;

这次我们的字符串构建的结果是不同的。现在看起来是这样的：

  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656  
  AND p.Title LIKE N'%' + @Title + N'%'  
  ORDER BY p.Score DESC;

我们没有返回包含来自sys.tables的详细信息的结果，而是返回零行，因为参数被设置为搜索字符串，并且没有与其匹配的帖子标题。

next-next-next

希望这能让您更好地理解参数化动态SQL如何帮助您避免SQL注入攻击。

使用它还有其他很好的理由，比如更好的计划缓存，但这不在这个问题的范围之内。

要了解更多信息，请查看我在这里的帖子：

票数 13

Database Administration用户

发布于 2022-01-03 02:23:16

SQL注入是将用于创建查询的参数的数据输入到查询中的行为。换句话说，不是使用参数orderid = @oid，而是使用orderid = 'abc'。这使您容易受到恶意或意外注入不正确或意外的查询。例如，您可能在参数中有一个撇号，或者它可能无法正确地转换日期。或者更危险的是，黑客试着像这样 Robert'); DROP TABLE Students;--。

上面的查询之所以安全得多，是因为它基本上是静态的。(这个特定的厨房水槽查询被称为一个，并具有其他的性能优势。)它只针对有限的一组条件进行更改:是否提供参数。它不向查询中注入实际数据。

SET @sql = 
 N'SELECT orderid, custid, empid, orderdate, filler'
 + N' /* 27702431-107C-478C-8157-6DFCECC148DD */'
 + N' FROM dbo.Orders'
 + N' WHERE 1 = 1'

此部分是静态的，并且始终是查询的一部分。

 + CASE WHEN @orderid IS NOT NULL THEN
 N' AND orderid = @oid' ELSE N'' END

本部现予有条件增补。但是，重要的是，实际数据不是查询的一部分，只有是否提供数据才是更改的部分。

这要容易得多，因为现在唯一的风险是预先知道的小语法错误(显然没有Intellisense)。不同查询的可能范围要小得多:您只有16种不同的可能性，它们都遵循相同的行，因此您可以更好地推理结果，并且可以轻松地测试所有可能性是否有效。

其结果是您得到了查询和数据的分离。查询将传递给编译器，并将数据分别绑定到充当占位符的参数。编译器不可能将数据误解为实际代码，它始终是数据。

票数 7

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/305558

复制

相似问题

问如何使用带有参数的sp_executesql来防止SQL注入？
EN

回答 2

Database Administration用户

双层

字符串-安全

unsafe-strings

still-not-safe

back-to-safety

next-next-next

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用带有参数的sp_executesql来防止SQL注入？EN

回答 2

Database Administration用户

双层

字符串-安全

unsafe-strings

still-not-safe

back-to-safety

next-next-next

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用带有参数的sp_executesql来防止SQL注入？
EN