使用Drizzle执行带模板字符串的ILIKE查询时遇参数类型错误的安全处理

问题背景 #

现有数据表定义：

CREATE TABLE items (
  id SERIAL PRIMARY KEY, 
  name VARCHAR (50) UNIQUE NOT NULL, 
  price decimal NOT NULL
);

INSERT INTO items (name, price)
VALUES 
  ('sprocket1-2a', 50.0),
  ('bolt-2x', 39.10),
  ('nut2-7a', 88.50);

使用Drizzle执行以下原生SQL查询时：

db.execute(
  sql`SELECT * FROM items WHERE name ILIKE '%${search}%'`,
),

出现错误：

Error: could not determine data type of parameter $1

已知可以用${sql.raw(search)}规避，但该参数来自用户输入，存在SQL注入风险，因此需要安全的处理方式。

解决方案 #

有两种安全且能解决类型推断问题的方法：

方法一：提前拼接通配符作为完整参数 #

将用户输入的search与通配符%拼接成完整的字符串，再作为参数传递，这样PostgreSQL能明确识别参数类型，同时保持参数绑定的安全性：

const searchPattern = `%${search}%`;
await db.execute(sql`SELECT * FROM items WHERE name ILIKE ${searchPattern}`);

方法二：使用Drizzle的sql.concat构建匹配模式 #

利用Drizzle提供的sql.concat函数来组合通配符和用户输入，这种方式更贴合Drizzle的使用规范，同样安全可靠：

await db.execute(sql`SELECT * FROM items WHERE name ILIKE ${sql.concat('%', search, '%')}`);

原因说明 #

原写法中，%${search}%会被解析为包含参数占位符的字符串常量，PostgreSQL无法推断占位符对应参数的数据类型。而上述两种方法都是将包含通配符的完整内容作为明确的字符串参数传递，数据库能正确识别参数类型，同时通过参数绑定避免了SQL注入风险。

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