探索一些常见的存储过程奥秘

应用程序开发人员长期以来一直相信将数据库操作放在存储过程中可以得到最佳性能并防止 SQL 注入攻击。他们还认为这些优势值得他们付出与数据库逻辑维护、测试和将数据库逻辑迁移到不同供应商相关的额外成本。近年来，随着开发人员开始质疑这些长久以来的假设，潮流已经从存储过程转向对象关系映射（ORM），例如 Hibernate 或 Entity Framework。

《存储过程是过时的工具吗？》文章重点讲述了一些避免存储过程而支持应用程序代码和 ORM 的原因。本周，我们将探讨上面的两个迷思，看看它们今天是否仍然经得起推敲。

性能优势

在互联网出现的初期，通常的做法是尽量减少网络流量以提高性能。而存储过程只需要将过程名称和参数而不是完整的 SQL 语句传输到服务器来帮助减少网络流量。考虑到某些生产用查询的复杂性和长度，这可能是带来巨大效益。今天，无论你从这种方法中得到什么效益，都很容易被一个事实所抵消：你很可能会在同一个请求中使用相同的参数调用相同的过程两到三次。与此同时，ORM 会查看其恆等映射并识别出它已经检索到该结果集，因此无需进行另一次。此外，应该注意的是，存储过程缓存在服务器上，而即席（ad-hoc）SQL 则不是的说法。这是 Frans Bouma 在他的博客文章《Stored Procedures are bad, m'kay?》中打破的迷思。

存储过程和 SQL 注入

人们常说，存储过程提供了防止 SQL 注入的保护，因为它们将数据与指令分开。这是真的，只要开发人员不在存储过程中使用动态 SQL，原始字符串通过替换占位符的输入参数传递。以下是一个写得很差的过程，它准确地显示了它何将数据库暴露于 SQL 注入的危险中：

create procedure GetStudents(@School nvarchar(50))
as
begin
    declare @sql nvarchar(100)
    set @sql = 'SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ' + @School
    exec @sql
end

你可以使用参数化查询编写消除 SQL 注入漏洞的 SQL。使用 Python、TypeScript 或 Java 等编程语言编写的参数化查询（如下所示）可以净化用户输入，以便在查询中安全使用：

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo%");
ResultSet rs = prepStmt.executeQuery();

可见防止 SQL 注入不是存储过程本身的好处，只是不将 SQL 字符串串联在一起的惯例。

预告

本篇文章探讨了一些关于存储过程长久以来的假设，这些假设在今天并不完全成立。虽然他们本身并不是跳出存储过程潮流框架的充分理由，但强烈建议你是时候重新评估你的应用程序架构了。

往期回顾

Navicat 被投毒了 | 真相来了！

盗版引发设备瘫痪

Navicat 16.1 为OceanBase 社区版

Navicat 成为信通院数据库创新实验室成员

Navicat 学术伙伴计划 - 免费教育版申请

Navicat 技术智库 - 实战演练与各类热门问题解答

免费试用攻略 | Navciat 16 数据库管理工具