如何防止SQL注入 [英] how to prevent SQL Injection

问题描述

我正在使用存储过程.为了节省时间，我做了一些通用的程序，使用动态sql来更新.这种通用程序是:

I am using stored procedures. In order to save time, I made some generic procedures that uses dynamic sqlin order to update. Such generic procedure is:

CREATE PROCEDURE [dbo].[SetField]
 @company_id uniqueidentifier,
 @id bigint,
 @field_code nvarchar(50),
 @value nvarchar(50)
AS
BEGIN
 DECLARE @field_name nvarchar(50)
 SET @field_name = NULL
 SELECT @field_name=field_name
 FROM dbo.FIELD_DEFINITION
 WHERE field_code=@field_code

 IF @field_name IS NOT NULL
 BEGIN

  IF @value IS NULL OR @value=''
  BEGIN
   SET @value='NULL'
  END
  ELSE
  BEGIN
   IF @field_code='START_DATE' OR @field_code='END_DATE'
   BEGIN
    SET @value = CONVERT(datetime, @value ,103)
   END
   SET @value=''''+@value+''''
  END

  DECLARE @sql nvarchar(1000)
  SET @sql = 'UPDATE dbo.TABLE '+
     'SET '+@field_name+'='+@value+' '+
     'WHERE company_id=''' + CAST(@company_id as nvarchar(36)) + ''' AND '+
     'id='+CAST(@id as nvarchar)
  EXEC(@sql)
 END
END

如何防止使用此代码进行 sql 注入?

How can I prevent sql injection with this code?

推荐答案

关于 SQL 注入要记住的重要方面是，如果可能的话，永远不要将用户提供的值直接嵌入到 SQL 中.这并不意味着您不能使用动态 sql(尽管如果您不，它肯定会让事情变得更容易)，但它有时会变得更加危险.

The important aspect to remember about SQL injection is that means that, if at all possible, you should never embed user-supplied values directly into your SQL. This doesn't mean that you can't use dynamic sql (though it definitely makes things easier if you don't), but it does become more dangerous at times.

在您的具体示例中，您可以保留除 @field_name 之外的所有内容的参数化.不幸的是，这必须直接嵌入到 SQL 中；其他一切都可以再次作为参数传递，因此无需担心它们的内容.

In your specific example, you can keep the parameterization of everything except @field_name. This, unfortunately, must be embedded directly into the SQL; everything else can be passed as a parameter again, so there's no need to worry about their content.

在此特定示例中，您可以做的最安全的事情如下:

The safest thing that you can do in this specific example is the following:

if(exists (select 1 from INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Table' and TABLE_SCHEMA = 'dbo' and COLUMN_NAME = @fieldName))
begin
    DECLARE @sql nvarchar(1000)
    SET @sql = 'UPDATE dbo.TABLE '+
       'SET ' + QUOTENAME(@field_name) + '=@value ' + 
       'WHERE company_id=@company_id AND '+
       'id=@id'

    exec sp_executesql @sql,N'@id bigint, @company_id uniqueidentifier, @value nvarchar(50)',@id,@company_id,@value
end

这有两件事:

1. 它验证表中是否确实存在具有该名称的列.如果用户将任何其他 SQL 语句嵌入到该字段中，则此检查将失败并且不会执行该语句.你也可以调用 raiseerror 来报告错误，但我会把这个练习留给你.
2. 它将字段名称括在方括号中，以便包含空格或保留字的字段名称不会破坏语句.这对您来说可能不是问题，但如果您自己生成 SQL，这始终是一种很好的做法.

1. It verifies that there is actually a column with that name in the table. If the user were to embed any other SQL statements into the field, then this check would fail and the statement would not be executed. You could also call raiseerror to report the error, but I'll leave that exercise up to you.
2. It encloses the field name in square braces so that field names that contain spaces or reserved words will not break the statement. This may not be an issue for you, but it's always good practice if you're generating the SQL yourself.

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