谁写的？这样的SQL太吓人了吧！

江南一点雨 2024-11-22 07:16 广东

大家平时都是这么写的吗？

昨天笔者在朋友圈发了这样一张图：

很多小伙伴看到了能够快速发现问题，当 company_id 为 null 的时候，会导致全表更新。

但是也有小伙伴不解，自己平时就是这么写的呀，也没什么问题，如果有问题，那么上面的 SQL 该怎么改呢？

笔者来和大家简单聊几句。

一、防止全表更新

如果在生产环境中使用 UPDATE 语句更新表数据，此时如果忘记携带本应该添加的 WHERE 条件，那么后果不堪设想。

那么怎么避免这个问题呢？

二、sql_safe_updates

sql_safe_updates 是 MySQL 数据库中的一个参数，它的作用是增强数据安全性，防止因误操作导致的数据丢失或破坏。

具体来说，当 sql_safe_updates 设置为 ON（启用）时，MySQL 将阻止执行没有明确 WHERE 子句的 UPDATE 或 DELETE 语句。这意味着如果试图运行一个不包含 WHERE 条件来限定更新或删除范围的 DML 语句，MySQL 会抛出一个错误。而当 sql_safe_updates 设置为 OFF（禁用）时，MySQL 不会对此类无条件更新或删除操作进行特殊限制，允许它们按常规方式执行

这个参数可以配置在会话级别或全局级别。

在会话级别，可以通过执行 SET sql_safe_updates = 1; 命令来启用，这只对当前连接有效。

在全局级别，可以通过 SET GLOBAL sql_safe_updates = 1; 命令或在 MySQL 配置文件中设置，这会影响服务器上所有新的会话，但是这个配置不会修改当前会话。

启用 sql_safe_updates 参数可以减少因人为失误引发的重大数据事故，尤其适合开发环境和对数据完整性要求严格的生产环境。

我们可以先执行 SHOW VARIABLES LIKE '%sql_safe_updates%'; 查看当前配置：

然后执行 SET sql_safe_updates = 1; 去更新，更新之后再去查看配置，发现 sql_safe_updates 就已经开启了：

这个时候，假设我们执行如下 SQL：

UPDATE user set username='javaboy';

就会报一个错误：

需要注意的是，启用 sql_safe_updates 参数可能会影响现有应用程序的正常运行，特别是那些依赖于无条件更新或删除操作的程序，因此在生产环境中启用之前，必须确保所有相关的应用程序代码已经过严格审查和适配。

三、SQL 插件

MyBatis-Plus 提供了一个非法 SQL 拦截插件叫做 IllegalSQLInnerInterceptor。这是 MyBatis-Plus 框架中的一个安全控制插件，用于拦截和检查非法 SQL 语句。

这个插件主要提供了四方面的功能：

识别并拦截特定类型的 SQL 语句，如全表更新、删除等高风险操作。

确保在执行查询时使用索引，以提高性能并避免全表扫描。

防止未经授权的全表更新或删除操作，减少数据丢失风险。

对包含 not、or 关键字或子查询的 SQL 语句进行额外检查，以防止逻辑错误或性能问题。

插件用法也简单，配置一个 Bean 即可：

@Configurationpublic class MybatisPlusConfig {
    @Bean    public MybatisPlusInterceptor mybatisPlusInterceptor() {        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();        // 添加非法SQL拦截器        interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor());        return interceptor;    }}