如何使用sp_executesql避免SQL注入

问题描述

在下面的示例代码中，表名称是一个输入参数.在这种情况下，如何避免使用sp_executesql进行SQL注入.下面是示例代码，我试图使用sp_executesql来避免它，但是它不起作用.谁能告诉我如何纠正它?

In the below sample code, Table Name is an input parameter. In this case, how can I avoid SQL injection using sp_executesql. Below is the sample code, I am trying to use sp_executesqlto avoid it but it doesn't work. Can anyone tell me how to correct it?

ALTER PROC Test @param1  NVARCHAR(50),
                 @param2  INT,
                 @tblname NVARCHAR(100)
AS
  BEGIN
      DECLARE @sql NVARCHAR(1000)

      SET @sql= N'  select * from ' + @tblname
                + ' where name= @param1 and id= @param2';

      PRINT @sql

      EXEC Sp_executesql
        @sql,
        N'@param1 nvarchar(50), @param2 int',
        @param1,
        @param2;
  END

EXEC Test
  'John',
  2,
  ' emp; delete from emp where id = 567; select * from emp '

输出:打印消息:

select * from  emp; delete from emp where id = 567; select * from emp  where name= @param1 and id= @param2;

所有输入参数将完全替换，并删除一行.请让我知道如何处理这种情况.

All the input parameters are exactly substituted and one row is deleted.Please let me know how to handle this scenario.

推荐答案

您可以将表名包含在[]

SET @sql= N'  select * from [' + @tblname + '] where name= @param1 and id= @param2';

但是，如果您使用由两部分组成的命名约定，例如dbo.tablename，则您必须添加其他解析，因为[dbo.tablename]会导致:

However, if you use a two-part naming convention e.g dbo.tablename, you have to add additional parsing, since [dbo.tablename] will result to:

您应该对其进行解析，以使其等于dbo.[tablename].

You should parse it so that it'll be equal to dbo.[tablename].

这篇关于如何使用sp_executesql避免SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！