如何使用Qmark风格安全编写带IN条件的SQL查询？

问题背景 #

表结构 #

CREATE TABLE foo (
  col1   CHAR(25)    PRIMARY KEY,
  col2   CHAR(2)     NOT NULL,
  col3   CHAR(1)     NOT NULL,
  CONSTRAINT c_col2 (col2 = 'an' OR col2 = 'bx' OR col2 = 'zz'),
  CONSTRAINT c_col3 (col3 = 'a' OR col3 = 'b' OR col3 = 'n')
)

已插入数据 #

cur.executemany('INSERT INTO foo VALUES(?, ?, ?)', [('xxx', 'bx', 'a'),
                                                    ('yyy', 'bx', 'b'),
                                                    ('zzz', 'an', 'b')])

查询需求 #

需要根据以下匹配列表筛选符合条件的行：

• row2 = ['bx', 'zz']：筛选col2为bx或zz的行
• row3 = ['b']：筛选col3为b的行

期望输出仅为yyy，但原字符串拼接的查询方式存在SQL注入风险，需要改用安全的Qmark风格实现。

安全实现方案 #

要实现安全的Qmark风格IN查询，核心思路是动态生成对应数量的占位符?，再将所有查询参数统一传递给执行方法，完全避免手动拼接字符串带来的风险。

1. 动态构建查询语句与参数列表 #

row2 = ['bx', 'zz']
row3 = ['b']

# 存储查询条件和参数
conditions = []
params = []

# 处理col2的IN条件
if row2:
    # 生成对应长度的占位符
    placeholders = ', '.join(['?'] * len(row2))
    conditions.append(f'col2 IN ({placeholders})')
    # 将列表元素加入参数列表
    params.extend(row2)

# 处理col3的IN条件
if row3:
    placeholders = ', '.join(['?'] * len(row3))
    conditions.append(f'col3 IN ({placeholders})')
    params.extend(row3)

# 拼接完整查询语句
query = 'SELECT col1 FROM foo WHERE ' + ' AND '.join(conditions)

2. 执行安全查询 #

将生成的参数列表传递给execute方法，数据库会自动处理参数的转义：

for row in cur.execute(query, params):
    print(row)

效果说明 #

这段代码会生成安全的查询语句：

SELECT col1 FROM foo WHERE col2 IN (?, ?) AND col3 IN (?)

并将参数['bx', 'zz', 'b']传递给数据库，既完全规避了SQL注入风险，又能精准筛选出期望的yyy结果。

内容的提问来源于stack exchange，提问作者Lance E.T. Compte