MyBatis 的动态 SQL 和 SQL 参数校验

java / mybatis / sql / dynamic sql

MyBatis 一般是用一个 mapper 的方法来对应一个 SQL 语句，对于固定参数的查询来说， 这不是什么问题，但是如果对应多个字段的灵活查询，就有点力不从心了， 今天我们思考下这个问题。

环境

假设我们有一个用户表：

CREATE TABLE `user` (
  `pk_id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `number` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `status` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comment` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

如果我们要获取数据的数量，可以在 Mapper 中这样写：

@Mapper
public interface UserMapper {
    @Select({
            "SELECT COUNT(*) FROM `user`"
    })
    int getCount();
}

目前来说很简单，但是如果我们为查询增加条件，复杂程度就会指数级增长， 比如我们要查询名字包含某个字符串的数量，就会变成：

@Mapper
public interface UserMapper {
    @Select({
            "SELECT COUNT(*) FROM `user`",
            "WHERE `name` like CONCAT('%', #{name}, '%')"
    })
    int getCount(String name);
}

这时我们就开始遇到这个问题，如果 name 没有指定， 我们应该去调用没有参数的 getCount()，如果指定了，就要调用 getCount(name)。 一个参数时我们就要在两个 SQL 语句中做出选择，两个参数时，SQL 语句就会变成四个， 当参数更多时，谁来拯救我们？

解决

MyBatis 已经提供了动态生成 SQL 语句的工具，所以我们可以这样写 Mapper：

@Mapper
public interface UserMapper {
    @SelectProvider(type = UserSQLProvider.class, method = "getCount")
    int getCount(String name);
}

然后再实现一个 UserSQLProvider 即可：

public class UserSQLProvider {
    public String getCount(String name) {
        SQL sql = new SQL() {
            {
                SELECT("COUNT(*)");
                FROM("user");
            }
        };
        if (StringUtils.hasText(name)) {
            sql.WHERE("`name` like CONCAT('%', #{name}, '%')");
        }
        return sql.toString();
    }
}

这样，我们的 mapper 只有一个函数，就可以灵活应对有无 name 的两种情况， 即使有再多的查询条件，一个函数也足够了。

不过可能有朋友会觉得，如果查询字段过多的话，传入的参数岂不是会变得非常多，别急， 我们可以这样做：

@Mapper
public interface UserMapper {
    @SelectProvider(type = UserSQLProvider.class, method = "getCount")
    int getCount(Map<String, String> map);
}

对，把参数存入容器，这样后续我们就可以在 UserSQLProvider 中组装更为复杂的 SQL 了。有一点我觉得需要说明一下，如果需要精确控制 WHERE 语句的顺序， 可能采用 List<Param> 之类的容器会比较合适，你也可以在 Param 中定制查询的字段、 条件和值。不过简单起见，我们还是以 Map 为例，key 代表字段，value 代表查询值， 我们只处理 like 的情况。有了 SQLProvider 这个杀器， 我们似乎可以在 SQLProvider 中完成全部内容：

public class UserSQLProvider {
    public String getCount(Map<String, String> map) {
        SQL sql = new SQL() {
            {
                SELECT("COUNT(*)");
                FROM("user");
            }
        };
        map.entrySet().stream().forEach(e -> {
            sql.WHERE(
                    String.format("`%s` like CONCAT('%%', '%s', '%%')", e.getKey(), e.getValue()));
        });
        return sql.toString();
    }
}

千万不要这么做！ 还记得我们之前都是以 "#{name}" 格式传入 SQL 参数吗， 这样我们可以确保传入值难以被注入恶意语句，而上面的做法， 需要我们自己对参数的值进行检查，但是一般说来，我们是很难防止被利用注入的， 所以我们还是要用到这种机制，那么 map 也可以用到吗？答案是可以：

public class UserSQLProvider {
    public String getCount(Map<String, String> map) {
        SQL sql = new SQL() {
            {
                SELECT("COUNT(*)");
                FROM("user");
            }
        };
        map.entrySet().stream().forEach(e -> {
            sql.WHERE(
                    String.format("`%s` like CONCAT('%%', #{map.%s}, '%%')", e.getKey(), e.getKey()));
        });
        return sql.toString();
    }
}

当然 map 的 key 还是会有注入的风险，不过一般 key 是后端插入的，与用户输入无关， 所以我们只要检查是否是表格的字段就好，风险较低，这里就不再赘述了。

总结

能力越大，责任越大。我们掌握一个强大的力量时， 首先需要考虑的不是享受这种力量的强大或便捷，而是要考虑如何正确的限制和使用它， 我觉得放在这里挺合适的：动态 SQL 固然很好用，但是利用不当会为我们带来更多风险。