Python操作SQLite动态拼接表名插入数据报语法错误如何解决

问题原因 #

报错的核心原因有两点：

• 直接用f字符串拼接SQL时，datetime类型、字符串类型的参数没有被自动包裹引号，比如你的时间参数2021-10-24 17:01:21拼接进SQL后，空格会打断语法解析，最终触发near "17": syntax error报错。
• 直接拼接SQL还存在SQL注入安全风险，你写死表名时用参数化查询能正常运行，正是因为参数化会自动处理各类值的格式、转义问题。

SQLite的参数化查询仅支持值参数绑定，不支持表名、字段名这类SQL标识符作为参数传入，所以要实现动态表名需求，需要做分层处理。

解决方案 #

分两步处理即可同时满足动态表名、语法合规、安全三个要求：

1. 提前定义业务允许操作的表名白名单，对传入的表名校验，避免SQL注入风险
2. 表名校验通过后，仅把合法表名拼接到SQL语句中，剩余字段值依旧用参数化?占位符绑定

示例代码如下：

# 提前定义业务允许操作的表名白名单
ALLOWED_TABLES = {"Sugar_before_eat_after_sleep", "你业务用到的其他表名1", "你业务用到的其他表名2"}

def insert_into_db_before_eat(table, id_row, time, sugar, actra_ui, xe):
    # 表名合法性校验，不合法直接拦截
    if table not in ALLOWED_TABLES:
        raise ValueError(f"非法表名: {table}")
    # 仅拼接合法表名，值部分保留参数化占位符
    sql = f"INSERT INTO {table} VALUES (?, ?, ?, ?, ?)"
    cur.execute(sql, (id_row, time, sugar, actra_ui, xe))
    con.commit()

注意事项 #

• 白名单必须提前硬编码定义，不能从用户输入中动态生成，彻底规避注入风险
• 如果表名包含特殊字符或者是SQL关键字，可以用反引号包裹拼接的表名，写法改为INSERT INTO {table} VALUES (?, ?, ?, ?, ?)，反引号为英文输入法下的符号

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