[20][04][20] SQL 预编译防止 SQL 注入

1. 预编译的作用

1.1 提高效率

数据库接受到 sql 语句之后,需要进行词法和语法解析校验,优化 sql 语句,制定执行计划.这需要花费一些时间.但是很多情况,我们的一条 sql 语句可能会反复执行,或者每次执行的时候只有个别的值不同 (比如 query 的 where 子句值不同,update 的 set 子句值不同,insert 的 values 值不同)

select id, name, age, address from t_user where name=? and age > ?

这个 sql 语句整体结构是不会改变的,只是 name 和 age 的值会变动, 每次都经历数据库的步骤, 影响效率, 为了解决上面的问题,于是就有了预编译,预编译语句就是将这类语句中的值用占位符替代,可以视为将 sql 语句模板化或者说参数化.一次编译,多次运行,省去了解析优化等过程

预编译语句被数据库的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中 (相当于一个涵数) 就会得到执行.并不是所以预编译语句都一定会被缓存,数据库本身会用一种策略 (内部机制)

预编译的实现方法是通过 PreparedStatement 和占位符来实现的

1.2 防止 SQL 注入

使用预编译后,其后注入的参数将不会再进行 SQL 编译.也就是说其后注入进来的参数系统将不会认为它会是一条 SQL 语句,而默认其是一个参数,参数中的 or 或者 and 等就不是 SQL 语法保留字了

2. 预编译的实现原理

2.1 mysql 的预编译

开启了预编译缓存后,connection 之间,预编译的结果是独立的,是无法共享的,一个 connection 无法得到另外一个 connection 的预编译缓存结果.经过试验,mysql 的预编译功能对性能影响不大,但在 jdbc 中使用 PreparedStatement 是必要的,可以有效地防止 sql 注入.相同 PreparedStatement 的对象,可以不用开启预编译缓存

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true");  
PreparedStatement stmt = conn.prepareStatement("select id, name, age, address from t_user where name=?");  
stmt.setString(1, "aaa");  
ResultSet rs1 = stmt.executeQuery();//第一次执行  
s1.close();  
stmt.setString(1, "ddd");  
ResultSet rs2 = stmt.executeQuery();//第二次执行  
rs2.close();  
stmt.close(); 
//查看mysql日志
Prepare          select id, name, age, address where t_user where name= ?
Execute          select id, name, age, address where t_user where name= 'aaa'
Execute          select id, name, age, address where t_user where name= 'ddd'

mysql 只编译了一次 SQL

2.2 mybatis 是如何实现预编译

mybatis 默认情况下,将对所有的 sql 进行预编译.mybatis 底层使用 PreparedStatement,过程是先将带有占位符 (即"?") 的 sql 模板发送至 mysql 服务器,由服务器对此无参数的 sql 进行编译后,将编译结果缓存,然后直接执行带有真实参数的 sql.核心是通过 #{} 实现的

在预编译之前,#{} 解析为一个 JDBC 预编译语句 (prepared statement) 的参数标记符 ?

// sqlMap 中如下的 sql 语句
select id, name, age, address from t_user where name = #{name};

// 解析成为预编译语句
select id, name, age, address from t_user where name = ?;

如果${ },SQL 解析阶段将会进行变量替换.不能实现预编译

select id, name, age, address from t_user where name like '%${name}%'

// 传递的参数为 "ruhua" 时,解析为如下，然后发送数据库服务器进行编译
select id, name, age, address from t_user where name like '%ruhua%';