tidb sql parser判断sql是否只读

tidb需要解析sql，生成执行计划，用到parser工具 https://github.com/pingcap/parser。

一般上，SQL 语言通常按照功能划分成以下的 4 个部分：

• DDL (Data Definition Language)：数据定义语言，用来定义数据库对象，包括库、表、视图和索引等。
• DML (Data Manipulation Language)：数据操作语言，用来操作和业务相关的记录。
• DQL (Data Query Language)：数据查询语言，用来查询经过条件筛选的记录。
• DCL (Data Control Language)：数据控制语言，用来定义访问权限和安全级别。

常见的是ddl与dml。

背景

之前遇到一个数据服务，上面维护了大量的数据库凭证信息，而且产品或者开发人员，可以直接触发sql查询数据。这些数据库凭证信息，有些是只读权限，但有些是有写权限的。所以，在触发sql前，需要检查sql是否会动用到写权限，如果是，则拦截掉。当然，很多时候sql复杂度高的时候，就很难通过正则的方式来判断sql是否包含只读操作，

sql可以通过parser工具，解析成抽象语法树（Abstract Syntax Tree，AST）。然后再通过遍历ast的Node，判断里面每个操作是否只读，就可以完成这个需求。

从简单sql看背后的数据结构

代码就不贴了，这里用到spew包来跟踪结构体
这是 SELECT a, b FROM t 解析出来的数据

(*ast.SelectStmt)(0xc0001584e0)({
 dmlNode: (ast.dmlNode) {
  stmtNode: (ast.stmtNode) {
   node: (ast.node) {
    text: (string) (len=18) "SELECT a, b FROM t"
   }
  }
 },
 resultSetNode: (ast.resultSetNode) {
  resultFields: ([]*ast.ResultField) <nil>
 },
 SelectStmtOpts: (*ast.SelectStmtOpts)(0xc00009dbf0)({
  Distinct: (bool) false,
  SQLBigResult: (bool) false,
  SQLBufferResult: (bool) false,
  SQLCache: (bool) true,
  SQLSmallResult: (bool) false,
  CalcFoundRows: (bool) false,
  StraightJoin: (bool) false,
  Priority: (mysql.PriorityEnum) 0,
  TableHints: ([]*ast.TableOptimizerHint) <nil>
 }),
 Distinct: (bool) false,
 From: (*ast.TableRefsClause)(0xc00000ea00)({
  node: (ast.node) {
   text: (string) ""
  },
  TableRefs: (*ast.Join)(0xc0000f4280)({
   node: (ast.node) {
    text: (string) ""
   },
   resultSetNode: (ast.resultSetNode) {
    resultFields: ([]*ast.ResultField) <nil>
   },
   Left: (*ast.TableSource)(0xc0000306c0)({
    node: (ast.node) {
     text: (string) ""
    },
    Source: (*ast.TableName)(0xc0000ce370)({
     node: (ast.node) {
      text: (string) ""
     },
     resultSetNode: (ast.resultSetNode) {
      resultFields: ([]*ast.ResultField) <nil>
     },
     Schema: (model.CIStr) ,
     Name: (model.CIStr) t,
     DBInfo: (*model.DBInfo)(<nil>),
     TableInfo: (*model.TableInfo)(<nil>),
     IndexHints: ([]*ast.IndexHint) {
     },
     PartitionNames: ([]model.CIStr) {
     }
    }),
    AsName: (model.CIStr)
   }),
   Right: (ast.ResultSetNode) <nil>,
   Tp: (ast.JoinType) 0,
   On: (*ast.OnCondition)(<nil>),
   Using: ([]*ast.ColumnName) <nil>,
   NaturalJoin: (bool) false,
   StraightJoin: (bool) false
  })
 }),
 Where: (ast.ExprNode) <nil>,
 Fields: (*ast.FieldList)(0xc00009dc20)({
  node: (ast.node) {
   text: (string) ""
  },
  Fields: ([]*ast.SelectField) (len=2 cap=2) {
   (*ast.SelectField)(0xc00007b5c0)({
    node: (ast.node) {
     text: (string) (len=1) "a"
    },
    Offset: (int) 7,
    WildCard: (*ast.WildCardField)(<nil>),
    Expr: (*ast.ColumnNameExpr)(0xc0000f4180)({
     exprNode: (ast.exprNode) {
      node: (ast.node) {
       text: (string) ""
      },
      Type: (types.FieldType) unspecified,
      flag: (uint64) 8
     },
     Name: (*ast.ColumnName)(0xc0001890a0)(a),
     Refer: (*ast.ResultField)(<nil>)
    }),
    AsName: (model.CIStr) ,
    Auxiliary: (bool) false
   }),
   (*ast.SelectField)(0xc00007b620)({
    node: (ast.node) {
     text: (string) (len=1) "b"
    },
    Offset: (int) 10,
    WildCard: (*ast.WildCardField)(<nil>),
    Expr: (*ast.ColumnNameExpr)(0xc0000f4200)({
     exprNode: (ast.exprNode) {
      node: (ast.node) {
       text: (string) ""
      },
      Type: (types.FieldType) unspecified,
      flag: (uint64) 8
     },
     Name: (*ast.ColumnName)(0xc000189110)(b),
     Refer: (*ast.ResultField)(<nil>)
    }),
    AsName: (model.CIStr) ,
    Auxiliary: (bool) false
   })
  }
 }),
 GroupBy: (*ast.GroupByClause)(<nil>),
 Having: (*ast.HavingClause)(<nil>),
 WindowSpecs: ([]ast.WindowSpec) <nil>,
 OrderBy: (*ast.OrderByClause)(<nil>),
 Limit: (*ast.Limit)(<nil>),
 LockTp: (ast.SelectLockType) none,
 TableHints: ([]*ast.TableOptimizerHint) <nil>,
 AfterSetOperator: (*ast.SetOprType)(<nil>),
 IsInBraces: (bool) false,
 QueryBlockOffset: (int) 0,
 SelectIntoOpt: (*ast.SelectIntoOption)(<nil>)
})

可以看到是这个sql解析只有一个SelectStmt的操作。其它字段的功能可以查看源码了解。

其它stmt

可以参考
https://github.com/pingcap/parser/blob/v4.0.2/ast/ddl.go

	_ DDLNode = &AlterTableStmt{}
	_ DDLNode = &CreateDatabaseStmt{}
	_ DDLNode = &CreateIndexStmt{}
	_ DDLNode = &CreateTableStmt{}
	_ DDLNode = &CreateViewStmt{}
	_ DDLNode = &CreateSequenceStmt{}
	_ DDLNode = &DropDatabaseStmt{}
	_ DDLNode = &DropIndexStmt{}
	_ DDLNode = &DropTableStmt{}
	_ DDLNode = &DropSequenceStmt{}
	_ DDLNode = &RenameTableStmt{}
	_ DDLNode = &TruncateTableStmt{}
	_ DDLNode = &RepairTableStmt{}

https://github.com/pingcap/parser/blob/v4.0.2/ast/dml.go

	_ DMLNode = &DeleteStmt{}
	_ DMLNode = &InsertStmt{}
	_ DMLNode = &SetOprStmt{}
	_ DMLNode = &UpdateStmt{}
	_ DMLNode = &SelectStmt{}
	_ DMLNode = &ShowStmt{}
	_ DMLNode = &LoadDataStmt{}
	_ DMLNode = &SplitRegionStmt{}

看到这里，是不是根常规sql操作对应上了？

解决思路

由于项目只是为了提供只读权限，只需要遍历解析出来的ast，只允许SelectStmt存在即可