? ? soar是一個(gè)開源的SQL規(guī)則審核工具，是一個(gè)go語言項(xiàng)目，可以直接編譯構(gòu)建成一個(gè)可執(zhí)行程序，而且是一個(gè)命令行工具，我們可以利用archey來調(diào)用soar進(jìn)行sql規(guī)則審核以及sql的分析，包括執(zhí)行計(jì)劃的查看及sql建議等。

soar中已經(jīng)有很多規(guī)則了，現(xiàn)在來添加一條soar中沒有的新的sql審核規(guī)則，例如一條新的審核規(guī)則：多表關(guān)聯(lián)必須有關(guān)聯(lián)條件，禁止出現(xiàn)笛卡爾積

我的思路是校驗(yàn)聯(lián)表查詢不允許沒有on和useing關(guān)鍵詞，同時(shí)不允許混用逗號(hào)和 ANSI 模式

首先在advisor包下的rule.go文件中添加規(guī)則說明

	//多表關(guān)聯(lián)必須有關(guān)聯(lián)條件，禁止出現(xiàn)笛卡爾積"JOI.009": {Item:     "JOI.009",Severity: "L4",Summary:  "多表關(guān)聯(lián)必須有關(guān)聯(lián)條件，禁止出現(xiàn)笛卡爾積",Content:  `多表關(guān)聯(lián)必須有關(guān)聯(lián)條件，禁止出現(xiàn)笛卡爾積`,Case:     "SELECT s,p,d FROM tb1,tb2 ",Func:     (*Query4Audit).RuleJoinQueryNoCondition,},

同時(shí)會(huì)利用到本來用的聯(lián)表的規(guī)則1

	"JOI.001": {Item:     "JOI.001",Severity: "L2",Summary:  "JOIN 語句混用逗號(hào)和 ANSI 模式",Content:  `表連接的時(shí)候混用逗號(hào)和 ANSI JOIN 不便于人類理解，并且MySQL不同版本的表連接行為和優(yōu)先級(jí)均有所不同，當(dāng) MySQL 版本變化后可能會(huì)引入錯(cuò)誤。`,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,},

?

?然后來添加新的約束規(guī)則方法：

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

//判斷聯(lián)表查詢中是否有關(guān)聯(lián)條件 on 或者using 對(duì)應(yīng)需求表中規(guī)則編號(hào)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 表達(dá)式if n.Condition.On == nil || len(n.Condition.Using) == 0 { // 檢查是否有 ON 或 USING 條件rule = HeuristicRules["JOI.009"] // 更新規(guī)則return false, nil}}return true, nil}, q.Stmt)common.LogIfError(err, "")return rule
}

今天還加了幾個(gè)其他的規(guī)則判斷如下：rule.go文件中

			//為每個(gè)字段應(yīng)添加注釋，對(duì)應(yīng)需求表中的開發(fā)規(guī)則編號(hào)15"CLA.011": {Item:     "CLA.011",Severity: "L1",Summary:  "表中的每個(gè)字段都應(yīng)該添加注釋",Content:  `為表添加注釋能夠使得表的意義更明確，從而為日后的維護(hù)帶來極大的便利。`,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,},//禁止查詢時(shí) select * 對(duì)應(yīng)開發(fā)需求中的規(guī)則編號(hào)49"COL.001": {Item:     "COL.001",Severity: "L1",Summary:  "SELECT語句必須指定具體字段名稱，禁止寫成 select*",Content:  `當(dāng)表結(jié)構(gòu)變更時(shí)，使用 * 通配符選擇所有列將導(dǎo)致查詢的含義和行為會(huì)發(fā)生更改，可能導(dǎo)致查詢返回更多的數(shù)據(jù)。`,Case:     "select * from tbl where id=1",Func:     (*Query4Audit).RuleForbiddenSelectStar,},	//建表語句以及alter語句修改表結(jié)構(gòu)時(shí)候自增列字段使用bigint,.禁止使用int類型 對(duì)應(yīng)需求表中的規(guī)則編號(hào)19"COL.020": {Item:     "COL.020",Severity: "L2",Summary:  "自增列字段使用bigint,.禁止使用int類型，防止存儲(chǔ)溢出",Content:  `自增列字段使用bigint,.禁止使用int類型，防止存儲(chǔ)溢出`,Case:     "create table test(`id` int(11) NOT NULL AUTO_INCREMENT)",Func:     (*Query4Audit).RuleAutoIncShouldBigint,},//建表語句以及修改表結(jié)構(gòu)時(shí)中禁止使用float、double類型，小數(shù)類型使用decimal類型 對(duì)應(yīng)需求表中的規(guī)則編號(hào)8"COL.021": {Item:     "COL.021",Severity: "L2",Summary:  "建表語句以及修改表結(jié)構(gòu)時(shí)中禁止使用float、double類型，小數(shù)類型使用decimal類型 ",Content:  `建表語句以及修改表結(jié)構(gòu)時(shí)中禁止使用float、double類型，小數(shù)類型使用decimal類型`,Case:     "create table test(`id` int(11) NOT NULL AUTO_INCREMENT)",Func:     (*Query4Audit).RuleFloatDoubleCheck,},

對(duì)應(yīng)的方法：heuristic.go文件中：

// RuleTblCommentCheck CLA.011 檢查表中每個(gè)字段是否都添加注釋,對(duì)應(yīng)需求表中的開發(fā)規(guī)則編號(hào)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 {//正則表達(dá)式匹配comment,不區(qū)分大小寫reg := regexp.MustCompile("(?i)comment")if !reg.MatchString(options) {rule = HeuristicRules["CLA.011"]}}}return rule
}// RuleForbiddenSelectStar COL.001 禁止使用select *  對(duì)應(yīng)需求表中的開發(fā)規(guī)則編號(hào)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
}// 建表以及修改表字段時(shí)候自增列字段使用bigint,.禁止使用int類型 對(duì)應(yīng)需求表中的規(guī)則編號(hào)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
}//建表語句以及修改表結(jié)構(gòu)時(shí)中禁止使用float、double類型，小數(shù)類型使用decimal類型 對(duì)應(yīng)需求表中的規(guī)則編號(hào)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:// 對(duì) 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
}

通過代碼可以看出一些規(guī)則實(shí)際上是調(diào)用了tidb的審核分析工具包進(jìn)行處理的。sql語句的解析拆分各個(gè)部分當(dāng)然還是用的sqlparser,sqlparser又在vitess模塊依賴包下

?

拆分sql語句靠sqlparser,分析sql則依賴tidb（pingcap公司產(chǎn)品）的相關(guān)模塊工具依賴包

比如判斷mysql的字段的各種數(shù)據(jù)類型 ，再比如判斷sql語句屬于類名類型

而且tidb主要用于分析mysql語句?