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()var tables []stringswitch q.Stmt.(type) {case *sqlparser.Union:return ruledefault:err := sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {switch n := node.(type) {case *sqlparser.AliasedTableExpr:switch table := n.Expr.(type) {case sqlparser.TableName:for _, t := range tables {if t == table.Name.String() {rule = HeuristicRules["JOI.002"]return false, nil}}tables = append(tables, table.Name.String())}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语句