时间戳

首页 > 解决方案 > 如何解决存储过程中动态SQL的语法错误

sql - 如何解决存储过程中动态SQL的语法错误

问题描述

我对动态 SQL 查询的这一部分有疑问，它在“：”附近显示语法错误，

[PersonnelNo] + '+ :+ ' + [FirstName] + ' ' + [LastName] AS Result

我也不知道如何在查询中使用另一个变量（关键字）：

[FirstName] LIKE N'%'+ @KeyWord +'%'

这是查询：

ALTER PROCEDURE [dbo].[ret_RetiredPersonnel_Search]
@KeyWord nvarchar (32),
@WhereClause varchar(MAX) 
AS
Exec('
SELECT 
    [Guid],
    [FirstName],
    [LastName],
    [PersonnelNo],
    [PersonnelNo] + '+ :+ ' + [FirstName] + ' ' + [LastName] AS Result
FROM ret_RetiredPersonnel 
WHERE 
    ([FirstName] LIKE N'%'+ @KeyWord +'%' OR
    [LastName] LIKE N'%'+ @KeyWord +'%' OR
    [PersonnelNo] LIKE N'%'+ @KeyWord +'%') @WhereClause
ORDER BY [LastName] DESC, [FirstName] DESC, [PersonnelNo] DESC')

标签： sqlsql-server

解决方案

如果粘贴整个 WHERE 子句包含用户直接输入的任何数据，则它是危险的，因为它可能使您面临 SQL 注入攻击。

在你所拥有的方面，你有三个问题：

  1. '必须''在动态 SQL 中转义 ast。
  2. 如果要传递参数，则必须使用sp_executesql而不是。exec
  3. 您应该print使用动态 SQL 对其进行故障排除

所以，像

CREATE OR ALTER PROCEDURE [dbo].[ret_RetiredPersonnel_Search]
@KeyWord nvarchar (32),
@WhereClause varchar(MAX) 
AS
/*
  exec [ret_RetiredPersonnel_Search] 'a', 'and FirstName = ''Fred'''
*/
    begin
    declare @sql nvarchar(max) = N'
    SELECT 
        [Guid],
        [FirstName],
        [LastName],
        [PersonnelNo],
        [PersonnelNo] + ''+ :+ '' + [FirstName] + '' '' + [LastName] AS Result
    FROM ret_RetiredPersonnel 
    WHERE 
        ([FirstName] LIKE N''%''+ @KeyWord +''%'' OR
        [LastName] LIKE N''%''+ @KeyWord +''%'' OR
        [PersonnelNo] LIKE N''%''+ @KeyWord +''%'') ' + @WhereClause + '
    ORDER BY [LastName] DESC, [FirstName] DESC, [PersonnelNo] DESC'

    --print @sql
    exec sp_executesql @sql, N'@KeyWord nvarchar (32)', @KeyWord = @KeyWord
end

推荐阅读