def query_tree(self, sql_content, instance_name, db_name): try: inception_engine = InceptionEngine() instance = Instance.objects.get(instance_name=instance_name) print_info = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql_content).rows except Exception as e: raise Exception('通过inception获取语法树异常,请检查inception配置,确保inception可以访问实例:' + str(e)) else: if print_info: id = print_info[0][0] statement = print_info[0][1] # 返回值为非0的情况下,说明是有错的,1表示警告,不影响执行,2表示严重错误,必须修改 errlevel = print_info[0][2] query_tree = print_info[0][3] errmsg = print_info[0][4] # 提交给inception语法错误的情况 if errmsg == 'Global environment': errlevel = 2 errmsg = 'Global environment: ' + query_tree if errlevel == 0: pass # print(json.dumps(json.loads(query_tree), indent=4, sort_keys=False, ensure_ascii=False)) return {'id': id, 'statement': statement, 'errlevel': errlevel, 'query_tree': query_tree, 'errmsg': errmsg} else: return None
def test_query(self, _conn, _cursor, _execute): _conn.return_value.cursor.return_value.fetchall.return_value = [(1, )] new_engine = InceptionEngine() query_result = new_engine.query(db_name=0, sql='select 1', limit_num=100) self.assertIsInstance(query_result, ResultSet)
def test_get_rollback_list(self, _connect): self.wf.sqlworkflowcontent.execute_result = """[{ "id": 1, "stage": "RERUN", "errlevel": 0, "stagestatus": "Execute Successfully", "errormessage": "None", "sql": "use archer_test", "affected_rows": 0, "sequence": "'1554135032_13038_0'", "backup_dbname": "None", "execute_time": "0.000", "sqlsha1": "", "actual_affected_rows": 0 }, { "id": 2, "stage": "EXECUTED", "errlevel": 0, "stagestatus": "Execute Successfully Backup successfully", "errormessage": "None", "sql": "insert into tt1 (user_name)values('A'),('B'),('C')", "affected_rows": 3, "sequence": "'1554135032_13038_1'", "backup_dbname": "mysql_3306_archer_test", "execute_time": "0.000", "sqlsha1": "", "actual_affected_rows": 3 }]""" self.wf.sqlworkflowcontent.save() new_engine = InceptionEngine() rollback_sql = new_engine.get_rollback(self.wf)
def test_execute_check_normal_sql(self, _query): sql = 'update user set id=100' row = [1, 'CHECKED', 0, 'Audit completed', 'None', 'use archery', 0, "'0_0_0'", 'None', '0', ''] _query.return_value = ResultSet(full_sql=sql, rows=[row]) new_engine = InceptionEngine() check_result = new_engine.execute_check(instance=self.ins, db_name=0, sql=sql) self.assertIsInstance(check_result, ReviewSet)
def data_masking(instance, db_name, sql, sql_result): """脱敏数据""" try: if SysConfig().get('query_check'): # 解析查询语句,禁用部分Inception无法解析关键词 p = sqlparse.parse(sql)[0] for token in p.tokens: if token.ttype is Keyword and token.value.upper() in [ 'UNION', 'UNION ALL' ]: logger.warning(f'数据脱敏异常,错误信息:不支持该查询语句脱敏!请联系管理员') sql_result.error = '不支持该查询语句脱敏!请联系管理员' sql_result.status = 1 return sql_result # 通过inception获取语法树,并进行解析 inception_engine = InceptionEngine() query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql) # 分析语法树获取命中脱敏规则的列数据 table_hit_columns, hit_columns = analyze_query_tree( query_tree, instance) sql_result.mask_rule_hit = True if table_hit_columns or hit_columns else False except Exception as msg: logger.warning(f'数据脱敏异常,错误信息:{traceback.format_exc()}') sql_result.error = str(msg) sql_result.status = 1 else: # 存在select * 的查询,遍历column_list,获取命中列的index,添加到hit_columns if table_hit_columns and sql_result.rows: column_list = sql_result.column_list table_hit_column = dict() for column_info in table_hit_columns: table_hit_column[ column_info['column_name']] = column_info['rule_type'] for index, item in enumerate(column_list): if item in table_hit_column.keys(): hit_columns.append({ "column_name": item, "index": index, "rule_type": table_hit_column.get(item) }) # 对命中规则列hit_columns的数据进行脱敏 # 获取全部脱敏规则信息,减少循环查询,提升效率 masking_rules = DataMaskingRules.objects.all() if hit_columns and sql_result.rows: rows = list(sql_result.rows) for column in hit_columns: index = column['index'] for idx, item in enumerate(rows): rows[idx] = list(item) rows[idx][index] = regex(masking_rules, column['rule_type'], rows[idx][index]) sql_result.rows = rows # 脱敏结果 sql_result.is_masked = True return sql_result
def test_execute_check_critical_sql(self): sql = 'alter table user' row = ReviewResult(id=1, errlevel=2, stagestatus='SQL语法错误', errormessage='ALTER TABLE 必须带有选项', sql=sql) new_engine = InceptionEngine() check_result = new_engine.execute_check(db_name=0, sql=sql) self.assertIsInstance(check_result, ReviewSet) self.assertEqual(check_result.rows[0].__dict__, row.__dict__)
def test_execute_finish(self, _query): sql = 'update user set id=100' row = [1, 'CHECKED', 0, 'Execute Successfully', 'None', 'use archery', 0, "'0_0_0'", 'None', '0', ''] column_list = ['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'] _query.return_value = ResultSet(full_sql=sql, rows=[row], column_list=column_list) new_engine = InceptionEngine() execute_result = new_engine.execute(workflow=self.wf) self.assertIsInstance(execute_result, ReviewSet)
def test_query_print(self, _query): sql = 'update user set id=100' row = [1, 'select * from sql_instance limit 100', 0, '{"command":"select","select_list":[{"type":"FIELD_ITEM","field":"*"}],"table_ref":[{"db":"archery","table":"sql_instance"}],"limit":{"limit":[{"type":"INT_ITEM","value":"100"}]}}', 'None'] column_list = ['ID', 'statement', 'errlevel', 'query_tree', 'errmsg'] _query.return_value = ResultSet(full_sql=sql, rows=[row], column_list=column_list) new_engine = InceptionEngine() print_result = new_engine.query_print(self.ins, db_name=None, sql=sql) self.assertDictEqual(print_result, json.loads(_repair_json_str(row[3])))
def data_masking(instance, db_name, sql, sql_result): try: # 通过inception获取语法树,并进行解析 inception_engine = InceptionEngine() query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql) # 分析语法树获取命中脱敏规则的列数据 table_hit_columns, hit_columns = analyze_query_tree( query_tree, instance) sql_result.mask_rule_hit = 1 if table_hit_columns or hit_columns else 2 except Exception as msg: sql_result.error = str(msg) sql_result.status = 1 else: # 存在select * 的查询,遍历column_list,获取命中列的index,添加到hit_columns if table_hit_columns and sql_result.rows: column_list = sql_result.column_list table_hit_column = dict() for column_info in table_hit_columns: table_hit_column_info = {} rule_type = column_info['rule_type'] table_hit_column_info[column_info['column_name']] = rule_type table_hit_column.update(table_hit_column_info) for index, item in enumerate(column_list): if item in table_hit_column.keys(): column = dict() column['column_name'] = item column['index'] = index column['rule_type'] = table_hit_column.get(item) hit_columns.append(column) # 对命中规则列hit_columns的数据进行脱敏 # 获取全部脱敏规则信息,减少循环查询,提升效率 masking_rules = DataMaskingRules.objects.all() if hit_columns and sql_result.rows: rows = list(sql_result.rows) for column in hit_columns: index = column['index'] for idx, item in enumerate(rows): rows[idx] = list(item) rows[idx][index] = regex(masking_rules, column['rule_type'], rows[idx][index]) sql_result.rows = rows # 脱敏结果 sql_result.is_masked = 1 return sql_result
def data_masking(instance, db_name, sql, sql_result): """脱敏数据""" try: # 通过inception获取语法树,并进行解析 inception_engine = InceptionEngine() query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql) # 分析语法树获取命中脱敏规则的列数据 table_hit_columns, hit_columns = analyze_query_tree( query_tree, instance) sql_result.mask_rule_hit = True if table_hit_columns or hit_columns else False except Exception as msg: logger.error(f'数据脱敏异常,错误信息:{traceback.format_exc()}') sql_result.error = str(msg) sql_result.status = 1 else: # 存在select * 的查询,遍历column_list,获取命中列的index,添加到hit_columns if table_hit_columns and sql_result.rows: column_list = sql_result.column_list table_hit_column = dict() for column_info in table_hit_columns: table_hit_column[ column_info['column_name']] = column_info['rule_type'] for index, item in enumerate(column_list): if item in table_hit_column.keys(): hit_columns.append({ "column_name": item, "index": index, "rule_type": table_hit_column.get(item) }) # 对命中规则列hit_columns的数据进行脱敏 # 获取全部脱敏规则信息,减少循环查询,提升效率 masking_rules = DataMaskingRules.objects.all() if hit_columns and sql_result.rows: rows = list(sql_result.rows) for column in hit_columns: index = column['index'] for idx, item in enumerate(rows): rows[idx] = list(item) rows[idx][index] = regex(masking_rules, column['rule_type'], rows[idx][index]) sql_result.rows = rows # 脱敏结果 sql_result.is_masked = True return sql_result
def _table_ref(sql_content, instance, db_name): """ 解析语法树,获取语句涉及的表,用于查询权限限制 :param sql_content: :param instance: :param db_name: :return: """ inception_engine = InceptionEngine() query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql_content) table_ref = query_tree.get('table_ref', []) db_list = [table_info['db'] for table_info in table_ref] table_list = [table_info['table'] for table_info in table_ref] # 异常解析的情形 if '' in db_list or '*' in table_list: raise RuntimeError('Inception Error: 存在空数据库表信息') if not (db_list or table_list): raise RuntimeError('Inception Error: 未解析到任何库表信息') return table_ref
def test_get_backup_connection(self, _connect): new_engine = InceptionEngine() new_engine.get_backup_connection() _connect.assert_called_once()