sql - 这个存储过程对 sql 注入(inject)安全吗？

Question

此存储过程使用 sp_executesql 执行带参数的 sql。
sql注入(inject)安全吗？

create procedure ExecutePeopleFilter 
    (@lastNameFilter varchar(20), 
    @companyNameFilter varchar(20), 
    @ageFilter int, 
    @dateFilter datetime)
as
begin
    declare @sql varchar(4000)
    declare @params varchar(1000)
    declare @whereClause varchar(1000)

    set @whereClause = ''

    if ISNULL(@lastNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@lastNameFilter) < 20) set @lastNameFilter += '%'
        set @whereClause += 'LastName like @lastName '
    end

    if ISNULL(@companyNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@companyNameFilter) < 20) set @companyNameFilter += '%'
        set @whereClause += 'CompanyName like @companyName '
    end

    if @ageFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '     
        set @whereClause += 'Age = @age '
    end

    if @dateFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        set @whereClause += 'StartDate = @startDate '
    end


    set @sql = 'select FirstName, LastName, CompanyName, Age, StartDate 
        from People'
    if (LEN(@whereClause) <> 0) set @sql += ' where ' + @whereClause

    set @params = '@lastName varchar(20), 
        @companyName varchar(20), 
        @age int, 
        @startDate datetime'

    execute sp_executesql @sql, @params, 
        @lastName = @lastNameFilter, 
        @companyName = @companyNameFilter, 
        @age = @ageFilter, 
        @startDate = @dateFilter
end

Answer 1

为什么要在存储过程中这样做？更好的解决方案可能是在客户端进行攻击，在调用存储过程之前转义字符串并检查长度。像 MS Enterprise DAAB (.NET) 这样的库通过在将参数添加到命令对象时指定参数的数据类型和长度来提供方便的方法。