如何用T-SQL替代拼接文本的EXECUTE方法？附注入安全疑问

1. PostgreSQL版本的SQL注入风险判断 #

如果你的searchlistings函数是直接将搜索查询词、评分数组拼接成字符串后执行EXECUTE（比如用||直接拼接变量到SQL语句中），那绝对存在SQL注入风险。比如当搜索查询词传入' OR 1=1; DROP TABLE listings; --这类恶意内容时，会被直接拼入SQL执行，导致数据泄露或破坏。

正确的防注入写法必须使用参数化查询，通过EXECUTE ... USING子句传递参数，而非直接拼接变量。示例如下：

CREATE OR REPLACE FUNCTION searchlistings(search_query text, ratings int[])
RETURNS SETOF listings AS $$
BEGIN
  RETURN QUERY EXECUTE 
    'SELECT * FROM listings 
     WHERE title ILIKE CONCAT(''%'', $1, ''%'')
     AND rating = array_greatest($2)
     ORDER BY rating DESC'
  USING search_query, ratings; -- 用USING传递参数，避免注入
END;
$$ LANGUAGE plpgsql;

这种写法中，变量会被PostgreSQL安全转义，不会被解析为SQL语句的一部分，彻底规避注入风险。

2. T-SQL（SQL Server）无动态SQL实现方案 #

完全可以不用EXECUTE（动态SQL）实现需求，核心是用表值参数或JSON数组解析处理评分数组，结合静态SQL完成逻辑，无需拼接语句。

方案一：使用表值参数传递评分数组 #

首先定义表值类型：

CREATE TYPE RatingList AS TABLE (Rating int NOT NULL);

然后创建函数：

CREATE FUNCTION dbo.SearchListings(
  @searchQuery nvarchar(255),
  @ratings RatingList READONLY
)
RETURNS TABLE
AS
RETURN
(
  SELECT l.*
  FROM listings l
  -- 模糊匹配搜索词
  WHERE l.title LIKE '%' + @searchQuery + '%'
    -- 对应PostgreSQL的array_greatest，取评分数组最大值筛选
    AND l.rating = (SELECT MAX(Rating) FROM @ratings)
  ORDER BY l.rating DESC
);

方案二：使用JSON数组传递评分数组 #

如果不想定义表值类型，可直接传递JSON格式的评分数组：

CREATE FUNCTION dbo.SearchListings(
  @searchQuery nvarchar(255),
  @ratingsJson nvarchar(max) -- 格式示例：'[1,3,5]'
)
RETURNS TABLE
AS
RETURN
(
  SELECT l.*
  FROM listings l
  WHERE l.title LIKE '%' + @searchQuery + '%'
    AND l.rating = (SELECT MAX(CAST(value AS int)) FROM OPENJSON(@ratingsJson))
  ORDER BY l.rating DESC
);

如果你的需求是筛选评分属于数组中任意值（而非取最大值），只需将条件改为：

-- 表值参数版本
AND l.rating IN (SELECT Rating FROM @ratings)

-- JSON数组版本
AND l.rating IN (SELECT CAST(value AS int) FROM OPENJSON(@ratingsJson))

你之前尝试CASE WHEN未成功，大概率是用错了场景——这个需求不需要CASE分支判断，直接通过子查询或IN/EXISTS就能实现逻辑。

内容的提问来源于stack exchange，提问作者Daisy