Zhihao's Blog

为什么通过正则表达式过滤防止 sql 注入不是个好主意

Scala Sql

为什么通过正则表达式过滤防止 sql 注入不是个好主意?

最近在做一个系统，这个系统可以看做一个巨大的数据库，用户可以通过 REST 请求来进行增删改查的操作。除了查询的其他操作都是通过构造 http request body 来定义查询，后台接收到数据后做一下拼接（在库名、表名、字段名等地方简单粗暴的过滤掉所有的特殊符号）。而关于查询的部分就比较有趣了：

怎么能通过 json 完整的描述 sql 的 where 并且能够防止 sql 注入还要便于使用（不能像 ES 那样！）

答案是不能！sql 的 where 条件很灵活而且可以非常复杂，就算我们能有一个能够解析复杂 json 的方法，拼接 json 的人肯定也想杀了你。

那么能不能直接写 where 条件啊？问题是怎么保证安全！

通过条件过滤

用户传过来一条 where 语句，我们通过判断 where 语句中有没有危险的操作，类似于这样（代码来自sql-injection-safe）：

public class SqlSafeUtil {

    private static final String SQL_TYPES =
            "TABLE, TABLESPACE, PROCEDURE, FUNCTION, TRIGGER, KEY, VIEW, MATERIALIZED VIEW, LIBRARY" +
                    "DATABASE LINK, DBLINK, INDEX, CONSTRAINT, TRIGGER, USER, SCHEMA, DATABASE, PLUGGABLE DATABASE, BUCKET, " +
                    "CLUSTER, COMMENT, SYNONYM, TYPE, JAVA, SESSION, ROLE, PACKAGE, PACKAGE BODY, OPERATOR" +
                    "SEQUENCE, RESTORE POINT, PFILE, CLASS, CURSOR, OBJECT, RULE, USER, DATASET, DATASTORE, " +
                    "COLUMN, FIELD, OPERATOR";

    private static final String[] SQL_REGEXPS = {
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+(true|false)(\\s)*(.*)",
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+(\\w)(\\s)*(\\=)(\\s)*(\\w)(\\s)*(.*)",
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+(equals|not equals)(\\s)+(true|false)(\\s)*(.*)",
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(\\=)(\\s)*([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(.*)",
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(\\!\\=)(\\s)*([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(.*)",
            "(?i)(.*)(\\b)+(OR|AND)(\\s)+([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(\\<\\>)(\\s)*([0-9A-Za-z_'][0-9A-Za-z\\d_']*)(\\s)*(.*)",
            "(?i)(.*)(\\b)+SELECT(\\b)+\\s.*(\\b)(.*)",
//            "(?i)(.*)(\\b)+SELECT(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+INSERT(\\b)+\\s.*(\\b)+INTO(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UPDATE(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DELETE(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UPSERT(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+SAVEPOINT(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+CALL(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+ROLLBACK(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+KILL(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+CREATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+ALTER(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+TRUNCATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+LOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UNLOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+RELEASE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DESC(\\b)+(\\w)*\\s.*(.*)",
            "(?i)(.*)(\\b)+DESCRIBE(\\b)+(\\w)*\\s.*(.*)",
            "(.*)(/\\*|\\*/|;){1,}(.*)",
            "(.*)(-){2,}(.*)",

    };

    ... ...
}

我不是数据库专家，SQL_TYPES里面有挺多我听都没听过的骚操作。那么问题来了，你认为这个 SqlSafeUtil 能够保证我们系统的安全性吗？这是一个根本无法证明的问题，你怎么知道你 cover 到了所有的情况？你怎么证明你 cover 到了所有情况？你所能证明的不过是你能 cover 到已知的情况而已！ 我非常认同 stackoverflow 上的这个回答：

It simply isn’t possible to build a filter that’s smarter than the people who hack SQL for a living.

为什么通过 parser 之后的 sql 是安全的

最简单的情况

假如我们有一个 where 语句：

where name = "peter"

sealed trait Clause
case class FieldValue[T](field: String, operator: Operator.Value, value: T) extends Clause

... ...

case class And(left: Clause, right: Clause) extends Clause
case class Or(left: Clause, right: Clause) extends Clause
... ...

所以上面那条 sql 用 case class 来描述的话应该是

FieldValue("name", EQ, "peter")

更复杂一点

where name = "peter" and (active = true or age >= 30)

用 case class 来描述的话应该是:

And(FieldValue("name", EQ, "peter"), Or(FieldValue("active", EQ, value = true), FieldValue("age", GE, 30)))

写成一行不太直观，画个图吧：

表达式树

这个其实就是表达式树了。然后我们再把表达式树再翻译成 sql就好了。

所以整个过程是这样的：

sql => case class 组成的表达式树 => 再翻译成 sql

更进一步

我们给所有的表名、字段名都加上“``”，然后表达式树生成参数化的 sql，最终结果像这样：

WHERE `name` != ?

然后把值“peter”当做参数，这样就更加安全了！

证明安全性

证明很简单：

在 “sql => case class” 的过程中我们会对库名、表名、字段名对一下简单的过滤（比如不能有特殊符号），然后把 sql 解析为表达式树，如果这个过程能正确处理，那么 sql 就是合法的。因为语法都是我们自己定义的，所以不合法的操作根本无法通过我们的 parser，所以通过 parser 来检测 sql 注入是安全的

举个极端的例子：

WHERE UserId = 105; DROP TABLE Suppliers;

上面的语法根本通不过我们 parser 的检查，因为我们压根儿就没定义 DROP TABLE 的操作！

自己写 parser 的缺点

自己定义的语法可能不够用，需要加功能。其实加起来还算容易，不过为了避免重复造轮子我们可以基于 spark-sql 的库来搞，而且 spark-sql 已经有了完善的 sql 语法了。

还有吗？

如果只是查数据库，又对灵活性要求非常高的话，可以这样：

  • read-only sql connection
dataSource.setReadOnly(true);
  • read-only user

通过只有只读权限的用户连接数据库


Original link：https://izhangzhihao.github.io//2018/01/29/为什么通过正则表达式过滤防止-sql-注入不是个好主意/

Search

    Table of Contents