ASP - SELECT子句中的SQL注入防护 [英] ASP - SQL injection protection in the SELECT clause

问题描述

从<紧固抵御SQL injection得到很大的帮助后， href=\"http://stackoverflow.com/questions/11020846/classic-asp-protection-against-sql-injection/11020916\">classic针对SQL注入 ASP的保护，我遇到哪些的重大问题不能使用参数化查询解决。

After getting great help in securing against SQL injection from classic ASP protection against SQL injection, I've encountered a major issue which cannot be solved using parameterized queries.

name = Trim(Request.QueryString("name"))
flds = Trim(Request.QueryString("flds"))
sql = "set rowcount 0 select " & flds & " from [TABLE] where Name = '" & name & "'"

据我了解，参数化查询将防止在WHERE子句中的SQL注入（在这种情况下，名称字段。

FLDS 是一个逗号分隔的想要返回的用户参数列表。因为很明显，这是的非常容易受到SQL注入。

flds is a comma-separated list of parameters that the users wants returned. As it is obvious, it is very vulnerable to SQL injection.

一个想法，我要保护我的code是要有有效的字段的静态生成字典，拆分 FLDS的，，验证每一个字符串对字典的值，构造的SQL查询，这将包括所有的都在字典present字段。

One idea I have to secure my code is to have a statically generated dict of valid fields, split the flds string by ",", verify each one of the values against the dict, and construct the SQL query that will consist of all the fields that are present in the dict.

在我看来，虽然这种方法将安全工作，它需要我在数据库中的每个变化（但罕见的那些）来修改静态列表。

It seems to me that although this method will work for security, it will require me to modify the static list at every change in the database (however rare those are).

是否有针对安全SQL注入攻击这个code更好的/正确的方法呢？

Are there better/proper ways of securing this code against SQL injection attacks?

推荐答案

创建SQL Server中的分割功能（也有较新的版本更好的，但是这是你在SQL Server 2000中获得）：

Create a split function in SQL Server (there are better ones for newer versions, but this is what you get in SQL Server 2000):

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(4000),
   @Delimiter  CHAR(1)
)
RETURNS @Items TABLE
(
   Item NVARCHAR(4000)
)
AS
BEGIN
   DECLARE
       @Item VARCHAR(12),
       @Pos  INT;

   WHILE LEN(@List)>0
   BEGIN
       SET @Pos = CHARINDEX(@Delimiter, @List);

       IF @Pos = 0
           SET @Pos = LEN(@List)+1;

       SET @Item = LEFT(@List, @Pos-1);

       INSERT @Items SELECT LTRIM(RTRIM(@Item));

       SET @List = SUBSTRING(@List, @Pos + LEN(@Delimiter), LEN(@List));

       IF LEN(@List) = 0 BREAK;
   END
   RETURN;
END
GO

然后创建一个存储过程：

Then create a stored procedure:

CREATE PROCEDURE dbo.RunScaryQuery
  @columns NVARCHAR(4000),
  @table   NVARCHAR(255)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @collist NVARCHAR(4000), @sql NVARCHAR(4000);

  SELECT @collist = COALESCE(@collist + ',', '') + c.name 
    FROM syscolumns AS c
    INNER JOIN dbo.SplitStrings(@columns, ',') AS s
    ON s.Item = c.name
    WHERE c.id = OBJECT_ID(@table);

  SELECT @sql = 'SELECT ' + @collist + ' FROM ' + @table
  -- where ...
  ;

  EXEC sp_executesql @sql;
END
GO

现在从ASP调用该存储过程与正确参数化的命令对象。

Now call that stored procedure from ASP with a properly parameterized command object.

这将确保您的SQL查询生成只能用实际存在于表的列名。 （废话任何将被忽略。）

This will ensure that your SQL query is generated only using column names that actually exist in the table. (Any nonsense will be ignored.)

这presumes，你将在列表中获得至少一个有效的列名。

This presumes that you will get at least one valid column name in the list.

这篇关于ASP - SELECT子句中的SQL注入防护的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！