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 []string
switch q.Stmt.(type) {
case *sqlparser.Union:
return rule
default:
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语句
扫一扫
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
