Soar项目中添加一条新的SQL审核规则示例

    soar是一个开源的SQL规则审核工具，是一个go语言项目，可以直接编译构建成一个可执行程序，而且是一个命令行工具，我们可以利用archey来调用soar进行sql规则审核以及sql的分析，包括执行计划的查看及sql建议等。

soar中已经有很多规则了，现在来添加一条soar中没有的新的sql审核规则，例如一条新的审核规则：多表关联必须有关联条件，禁止出现笛卡尔积

我的思路是校验联表查询不允许没有on和useing关键词，同时不允许混用逗号和 ANSI 模式

首先在advisor包下的rule.go文件中添加规则说明

	//多表关联必须有关联条件，禁止出现笛卡尔积"JOI.009": {Item:     "JOI.009",Severity: "L4",Summary:  "多表关联必须有关联条件，禁止出现笛卡尔积",Content:  `多表关联必须有关联条件，禁止出现笛卡尔积`,Case:     "SELECT s,p,d FROM tb1,tb2 ",Func:     (*Query4Audit).RuleJoinQueryNoCondition,},

同时会利用到本来用的联表的规则1

	"JOI.001": {Item:     "JOI.001",Severity: "L2",Summary:  "JOIN 语句混用逗号和 ANSI 模式",Content:  `表连接的时候混用逗号和 ANSI JOIN 不便于人类理解，并且MySQL不同版本的表连接行为和优先级均有所不同，当 MySQL 版本变化后可能会引入错误。`,Case:     "select c1,c2,c3 from t1,t2 join t3 on t1.c1=t2.c1,t1.c3=t3,c1 where id>1000",Func:     (*Query4Audit).RuleCommaAnsiJoin,},

 

 然后来添加新的约束规则方法：

在advisor包下的heuristic.go文件中加上新的方法：

//判断联表查询中是否有关联条件 on 或者using 对应需求表中规则编号70
func (q *Query4Audit) RuleJoinQueryNoCondition() Rule {var rule = q.RuleOK()err := sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {switch n := node.(type) {case *sqlparser.Select:ansiJoin := falsecommaJoin := falsefor _, f := range n.From {switch f.(type) {case *sqlparser.JoinTableExpr:ansiJoin = truecase *sqlparser.AliasedTableExpr:commaJoin = true}}if ansiJoin && commaJoin {rule = HeuristicRules["JOI.001"]return false, nil}case *sqlparser.JoinTableExpr: // 处理 JOIN 表达式if n.Condition.On == nil || len(n.Condition.Using) == 0 { // 检查是否有 ON 或 USING 条件rule = HeuristicRules["JOI.009"] // 更新规则return false, nil}}return true, nil}, q.Stmt)common.LogIfError(err, "")return rule
}

今天还加了几个其他的规则判断如下：rule.go文件中

			//为每个字段应添加注释，对应需求表中的开发规则编号15"CLA.011": {Item:     "CLA.011",Severity: "L1",Summary:  "表中的每个字段都应该添加注释",Content:  `为表添加注释能够使得表的意义更明确，从而为日后的维护带来极大的便利。`,Case:     "CREATE TABLE `test1` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`c1` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",Func:     (*Query4Audit).RuleTblCommentCheck,},//禁止查询时 select * 对应开发需求中的规则编号49"COL.001": {Item:     "COL.001",Severity: "L1",Summary:  "SELECT语句必须指定具体字段名称，禁止写成 select*",Content:  `当表结构变更时，使用 * 通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。`,Case:     "select * from tbl where id=1",Func:     (*Query4Audit).RuleForbiddenSelectStar,},	//建表语句以及alter语句修改表结构时候自增列字段使用bigint,.禁止使用int类型 对应需求表中的规则编号19"COL.020": {Item:     "COL.020",Severity: "L2",Summary:  "自增列字段使用bigint,.禁止使用int类型，防止存储溢出",Content:  `自增列字段使用bigint,.禁止使用int类型，防止存储溢出`,Case:     "create table test(`id` int(11) NOT NULL AUTO_INCREMENT)",Func:     (*Query4Audit).RuleAutoIncShouldBigint,},//建表语句以及修改表结构时中禁止使用float、double类型，小数类型使用decimal类型 对应需求表中的规则编号8"COL.021": {Item:     "COL.021",Severity: "L2",Summary:  "建表语句以及修改表结构时中禁止使用float、double类型，小数类型使用decimal类型 ",Content:  `建表语句以及修改表结构时中禁止使用float、double类型，小数类型使用decimal类型`,Case:     "create table test(`id` int(11) NOT NULL AUTO_INCREMENT)",Func:     (*Query4Audit).RuleFloatDoubleCheck,},

对应的方法：heuristic.go文件中：

// RuleTblCommentCheck CLA.011 检查表中每个字段是否都添加注释,对应需求表中的开发规则编号15
func (q *Query4Audit) RuleTblCommentCheck() Rule {var rule = q.RuleOK()switch node := q.Stmt.(type) {case *sqlparser.DDL:if strings.ToLower(node.Action) != "create" {return rule}if node.TableSpec == nil {return rule}if options := node.TableSpec.Options; options == "" {rule = HeuristicRules["CLA.011"]} else {//正则表达式匹配comment,不区分大小写reg := regexp.MustCompile("(?i)comment")if !reg.MatchString(options) {rule = HeuristicRules["CLA.011"]}}}return rule
}// RuleForbiddenSelectStar COL.001 禁止使用select *  对应需求表中的开发规则编号49
func (q *Query4Audit) RuleForbiddenSelectStar() Rule {var rule = q.RuleOK()// 先把count(*)替换为count(1)re := regexp.MustCompile(`(?i)count\s*\(\s*\*\s*\)`)sql := re.ReplaceAllString(q.Query, "count(1)")stmt, err := sqlparser.Parse(sql)if err != nil {common.Log.Debug("RuleSelectStar sqlparser.Parse Error: %v", err)return rule}err = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {switch node.(type) {case *sqlparser.StarExpr:rule = HeuristicRules["COL.001"]return false, nil}return true, nil}, stmt)common.LogIfError(err, "")return rule
}// 建表以及修改表字段时候自增列字段使用bigint,.禁止使用int类型 对应需求表中的规则编号19
func (q *Query4Audit) RuleAutoIncShouldBigint() Rule {var rule = q.RuleOK()switch q.Stmt.(type) {case *sqlparser.DDL:for _, tiStmt := range q.TiStmt {switch node := tiStmt.(type) {case *tidb.CreateTableStmt:for _, col := range node.Cols {if col.Tp == nil {continue}for _, opt := range col.Options {if opt.Tp == tidb.ColumnOptionAutoIncrement {if col.Tp.Tp != mysql.TypeLong { // 检查是否为 bigint 类型rule = HeuristicRules["COL.020"]break}}if rule.Item == "COL.020" {break}}}case *tidb.AlterTableStmt:for _, spec := range node.Specs {switch spec.Tp {case tidb.AlterTableChangeColumn, tidb.AlterTableAlterColumn,tidb.AlterTableModifyColumn, tidb.AlterTableAddColumns:for _, col := range spec.NewColumns {if col.Tp == nil {continue}for _, opt := range col.Options {if opt.Tp == tidb.ColumnOptionAutoIncrement {if col.Tp.Tp != mysql.TypeLong { // 检查是否为 bigint 类型rule = HeuristicRules["COL.020"]break}}if rule.Item == "COL.020" {break}}}}}}}}return rule
}//建表语句以及修改表结构时中禁止使用float、double类型，小数类型使用decimal类型 对应需求表中的规则编号8
func (q *Query4Audit) RuleFloatDoubleCheck() Rule {var rule = q.RuleOK()switch q.Stmt.(type) {case *sqlparser.DDL:for _, tiStmt := range q.TiStmt {switch node := tiStmt.(type) {case *tidb.CreateTableStmt:for _, col := range node.Cols {if col.Tp == nil {continue}if col.Tp.Tp == mysql.TypeFloat || col.Tp.Tp == mysql.TypeDouble {rule = HeuristicRules["COL.021"]break}}case *tidb.AlterTableStmt:// 对 ALTER TABLE 语句的类似检查for _, spec := range node.Specs {switch spec.Tp {case tidb.AlterTableChangeColumn, tidb.AlterTableAlterColumn,tidb.AlterTableModifyColumn, tidb.AlterTableAddColumns:for _, col := range spec.NewColumns {if col.Tp == nil {continue}if col.Tp.Tp == mysql.TypeFloat || col.Tp.Tp == mysql.TypeDouble {rule = HeuristicRules["COL.021"]break}}}}}}}return rule
}

通过代码可以看出一些规则实际上是调用了tidb的审核分析工具包进行处理的。sql语句的解析拆分各个部分当然还是用的sqlparser,sqlparser又在vitess模块依赖包下

 

拆分sql语句靠sqlparser,分析sql则依赖tidb（pingcap公司产品）的相关模块工具依赖包

比如判断mysql的字段的各种数据类型 ，再比如判断sql语句属于类名类型

而且tidb主要用于分析mysql语句