数据库系统最佳实践系列 --- 使用 prepared statement

2022 年 5 月 23 日
本文字数：2026 字
阅读完需：约 7 分钟

Prepared statement 是当前主流关系数据库都支持的一个功能，昆仑数据库系统也支持此功能。

今天我和大家讲讲使用 prepared statement 的好处。主要有两点，首先可以大大提升信息系统的安全性，防止 SQL 注入；还可以在一定程度提升查询性能。

所以强烈建议大家使用 prepared statement 功能。至于 prepared statement 的具体编程和 API 用法，本文不在此赘述，可以参考相关数据库的 client API 的文档。下面详细讲解 prepared statement 功能的这两个巨大的优势的工作原理。

Prepared statement 为什么可以阻止 SQL 注入

SQL 注入的原理

当前大多数信息系统都是用一个软件或者网页作为前端接收用户输入，然后在服务器的应用层（也就是使用 java，php，python，CGI 等开发的服务器应用软件系统）使用这些输入，实现应用逻辑，最终使用用户输入作为参数操作后台数据库做数据的增删改查。

很多应用开发者直接把前端用户的输入字符串作为 SQL 查询的一部分，拼接成一个 SQL 语句，这是一种非常差的编程方式。假如有一个系统，它的前端做一个删除产品的功能，它使用一个文本框接收用户输入一个产品编号，然后后端使用这个编号作为参数来删除产品。拼接 SQL 字符串的伪代码是

sqlstr = 'delete from products where pcode=\'' + textbox.string_value + '\'';

复制代码

此时如果用户在前端输入这样一个字符串： xx‘ or ‘1，那么最终送给数据库的 sql 语句就是

delete from products where pcode=’xx’ or ’1‘;

复制代码

那么这个语句就会把系统 products 表的所有数据都删除。

通常这类问题中，如果用户输入参数作为数值等非字符串参数值使用，那么有一定编程经验的开发者会把用户输入转换为数值等所需类型的常量然后再做拼接，这样就可以避免上述注入。以上面的查询为例，如果 pcode 列是 int 类型，那么用户的这个输入：xx‘ or ‘1

这个字符串经过数值转换，转为 0，然后应用再做 sql 语句拼接，最终送给后端的 sql 语句就是delete from products where pcode=0

这样虽然可能会误删另一行，但是至少不会丢失全表数据。但是一些小白开发者连上面的数值类型转换都不做，那么就无法在这种简单情况下避坑了。

而如果上例的 pcode 确实就是字符串类型，现在有一些应用层的工具和开发范例来避免 SQL 注入，比如禁止各种运算符字符（比如 +=-/|&等）或者禁止某些 SQL 关键字（比如 select， or，and 等），但是无法完全杜绝 SQL 注入。

可以说，只要用户输入的字符串仍然会直接被关系数据库的语法解析器来解析，那么 SQL 注入就一定是可以实现的。换个通俗点的说法：只要你还在应用层直接使用用用户输入的字符串（部分或者全部）来拼接 SQL 语句，那么 SQL 注入就一定会发生。

那么如何一劳永逸的避免 SQL 注入呢？这就要使用 prepared statement。

Prepared statement 的工作原理

以上面的 sql 语句为例，prepared statement 就是delete from products where pcode=?;

关系数据库的 SQL 解析器解析和优化这个语句，形成一个查询计划。在客户端用户程序得到的是这个 prepared 查询语句的一个句柄（handle），在 session 有效期内有效。这个查询计划就是执行这个查询语句的方法，但是在没有得到具体的参数之前，这个查询计划是无法执行的。

当用户通过其句柄（handle）绑定（bind）参数给这个 prepared statement 之后，用户就可以执行这个查询计划了。此时无论用户绑定什么样的参数，这个参数并不会被 SQL 解析器再次解析，它只是作为指定类型的参数值被这个查询计划使用来完成查询执行。所以，即时用户绑定的参数是xx‘ or '1

那么最终相当于执行的 SQL 语句是delete from products where pcode=‘xx\' or \'1';

这样就不会有任何数据损失的危险。

并且，用户可以多次反复绑定不同的参数给这个 prepared statement，这样，假如这个结构的 sql 语句数量很大的话，数据库系统就避免了大量地解析和优化同一结构的 SQL 语句，从而提升系统性能。

END

昆仑数据库是一个 HTAP NewSQL 分布式数据库管理系统，可以满足用户对海量关系数据的存储管理和利用的全方位需求。应用开发者和 DBA 的使用昆仑数据库的体验与单机 MySQL 和单机 PostgreSQL 几乎完全相同，因为首先昆仑数据库支持 PostgreSQL 和 MySQL 双协议，支持标准 SQL:2011 的 DML 语法和功能以及 PostgreSQL 和 MySQL 对标准 SQL 的扩展。同时，昆仑数据库集群支持水平弹性扩容，数据自动拆分，分布式事务处理和分布式查询处理，健壮的容错容灾能力，完善直观的监测分析告警能力，集群数据备份和恢复等常用的 DBA 数据管理和操作。所有这些功能无需任何应用系统侧的编码工作，也无需 DBA 人工介入，不停服不影响业务正常运行。昆仑数据库具备全面的 OLAP 数据分析能力，通过了 TPC-H 和 TPC-DS 标准测试集，可以实时分析最新的业务数据，帮助用户发掘出数据的价值。昆仑数据库支持公有云和私有云环境的部署，可以与 docker，k8s 等云基础设施无缝协作，可以轻松搭建云数据库服务。请访问 http://www.zettadb.com/ 获取更多信息并且下载昆仑数据库软件、文档和资料。
KunlunDB 项目已开源
【GitHub：】https://github.com/zettadb
【Gitee：】https://gitee.com/zettadb