如何在SQLite的EXCEPT子句中为多行提供占位符？

核心问题解决思路 #

你之前的字符串拼接写法存在SQL注入风险，关键问题在于没有正确为多个ID生成对应数量的占位符。下面提供两种安全的实现方式，均使用参数绑定避免注入。

方法1：动态生成占位符结合CTE+EXCEPT #

根据ID列表的长度，生成对应个数的(?)占位符，保留你原本的CTE+EXCEPT逻辑，同时通过参数绑定传递ID值。

修改后的代码片段：

new_video_ids = ["vid1", "vid2", "vid5"]
# 按ID数量生成对应占位符
placeholders = ', '.join(['(?)'] * len(new_video_ids))
query = f"""
WITH cte(id) AS (VALUES {placeholders})
SELECT id FROM cte EXCEPT SELECT id FROM videos
"""
res = cursor.execute(query, new_video_ids)
print(f"filter result: {res.fetchall()}")  # 输出 [('vid5',)]

方法2：使用LEFT JOIN筛选未匹配项 #

通过LEFT JOIN关联临时ID表和目标表，筛选出没有匹配的ID，逻辑更直观。

代码示例：

new_video_ids = ["vid1", "vid2", "vid5"]
placeholders = ', '.join(['(?)'] * len(new_video_ids))
query = f"""
SELECT cte.id
FROM (VALUES {placeholders}) AS cte(id)
LEFT JOIN videos ON cte.id = videos.id
WHERE videos.id IS NULL
"""
res = cursor.execute(query, new_video_ids)
print(f"filter result: {res.fetchall()}")  # 输出 [('vid5',)]

两种方法的差异 #

• EXCEPT会自动对结果去重，若输入ID列表存在重复值，最终结果仅保留一个；
• LEFT JOIN会保留输入中的重复ID，如需去重可在SELECT后添加DISTINCT。

完整可运行代码 #

替换你原示例中的风险代码，完整版本如下：

import sqlite3

db = sqlite3.connect(":memory:")
cursor = db.cursor()

# 创建视频表并插入测试数据
cursor.execute("CREATE TABLE IF NOT EXISTS videos(id TEXT PRIMARY KEY, title TEXT)")
dummy_data = [
    ("vid1", "Video 1"),
    ("vid2", "Video 2"),
    ("vid3", "Video 3"),
]
cursor.executemany("INSERT INTO videos VALUES(?, ?)", dummy_data)
db.commit()

# 验证现有数据
res = cursor.execute("SELECT * FROM videos")
print(f"select* result: {res.fetchall()}")

# 安全筛选未存在的ID
new_video_ids = ["vid1", "vid2", "vid5"]
placeholders = ', '.join(['(?)'] * len(new_video_ids))
query = f"""
WITH cte(id) AS (VALUES {placeholders})
SELECT id FROM cte EXCEPT SELECT id FROM videos
"""
res = cursor.execute(query, new_video_ids)
print(f"filter result: {res.fetchall()}")

db.close()

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