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()