参数化查询(prepared statement / parameterized statement)的意义主要有两点,一是可以有效防止 SQL 注入攻击,二是可以减少每次执行 SQL 语句的所需要的解析语句开销。

关于第一点的理解,很多人认为是语句在传给数据库服务器之前,客户端将语句里的参数转义并发送,虽然这样可以有效防止 SQL 注入,但这并不是真正的参数化查询。

参数化查询是指数据库服务器在解析语句时,只解析非参数部分,参数部分采用模板参数占位的形式,在每次执行语句时,将某些常量值替换至相应的模板参数位置。

这样一来,数据库服务器不会将参数的内容视为 SQL 的一部分来处理,而是在数据库完成 SQL 编译后,才将参数套用并运行。解析器编译执行相同的语句时,也只需要编译一次,因为通常,数据库应用处理着大量几乎相同的语句,只不过语句中的部分文字或变量不同(如 WHERESETVALUES 后的变量)。

因此,即使参数中有破坏性的语句,也只会被数据库服务器当作参数的一部分,而不会被解析器编译运行。这才是参数化查询可以有效防止注入攻击的真正原因。

select * from users where username = 'user_a';
select * from users where id = 'user_b' or '1=1';

以 MySQL 为例,如以上语句,第一个语句的 'user_a' 和第二个语句的 'user_b' or '1=1',都会作为语句的一部分被解析器一起编译,因此解析器编译后的第二个语句会执行其中破坏性的部分。

prepare stmt from 'select * from users where username = ?;
set @a = 'user_a';
execute stmt using @a;
deallocate prepare stmt;

以上才是真正使用 MySQL 自己的参数化查询。prepare 是预编译语句,set 将变量赋值给参数,executeusing 使用赋值参数套入编译后的语句并执行,deallocate 从内存中释放预编译的语句。

预编译语句是与创建它的会话绑定的,如果关闭会话而没有 deallocate 预编译语句,服务器也会自动释放其内存。服务器允许最大同时存在的预编译语句与 max_prepared_stmt_count 系统变量有关,默认值是 16382,参考

客户端绑定参数

例如,SQLAlchemy 提供了后绑定参数的模式,且支持具名参数。

from sqlalchemy.sql import text
stmt = text('select * from users where username = :un')
session.execute(stmt, {'un': 'tim'}) # use scoped session

无论如何,也不要使用 %.format 等形式将参数填入,会有很高的注入风险。