def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
config = SysConfig()
check_result = ReviewSet(full_sql=sql)
# 禁用/高危语句检查
line = 1
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
# 把所有SQL转换成SqlItem List。 如有多行(内部有多个;)执行块，约定以delimiter $$作为开始, 以$$结束
# 需要在函数里实现单条SQL做sqlparse.format(sql, strip_comments=True)
sqlitemList = get_full_sqlitem_list(sql, db_name)
for sqlitem in sqlitemList:
# 禁用语句
if re.match(r"^\s*select", sqlitem.statement.lower(), re.I):
check_result.is_critical = True
result = ReviewResult(id=line, errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sqlitem.statement)
# 高危语句
elif critical_ddl_regex and p.match(sqlitem.statement.strip().lower()):
check_result.is_critical = True
result = ReviewResult(id=line, errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' + critical_ddl_regex + '条件的语句！',
sql=sqlitem.statement)
# 正常语句
else:
result = ReviewResult(id=line, errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0, )
# 判断工单类型
if get_syntax_type(sqlitem.statement) == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回，提高效率
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
return check_result
def execute_check(self, db_name=None, sql='', close_conn=True):
"""
上线单执行前的检查, 返回Review set
update by Jan.song 20200302
使用explain对数据修改预计进行检测
"""
config = SysConfig()
check_result = ReviewSet(full_sql=sql)
# explain支持的语法
explain_re = r"^merge|^update|^delete|^insert|^create\s+table|^create\s+index|^create\s+unique\s+index"
# 禁用/高危语句检查
line = 1
# 保存SQL中的新建对象
object_name_list = set()
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
try:
sqlitemList = get_full_sqlitem_list(sql, db_name)
for sqlitem in sqlitemList:
sql_lower = sqlitem.statement.lower().rstrip(';')
# 禁用语句
if re.match(r"^select|^with|^explain", sql_lower):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sqlitem.statement)
# 高危语句
elif critical_ddl_regex and p.match(sql_lower.strip()):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=sqlitem.statement)
# 驳回未带where数据修改语句，如确实需做全部删除或更新，显示的带上where 1=1
elif re.match(r"^update((?!where).)*$|^delete((?!where).)*$",
sql_lower):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回未带where数据修改',
errormessage='数据修改需带where条件！',
sql=sqlitem.statement)
# 驳回事务控制，会话控制SQL
elif re.match(r"^set|^rollback|^exit", sql_lower):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='SQL中不能包含^set|^rollback|^exit',
errormessage='SQL中不能包含^set|^rollback|^exit',
sql=sqlitem.statement)
# 通过explain对SQL做语法语义检查
elif re.match(explain_re,
sql_lower) and sqlitem.stmt_type == 'SQL':
if self.check_create_index_table(
db_name=db_name,
sql=sql_lower,
object_name_list=object_name_list):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
object_name_list.add(object_name)
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='WARNING:新建表的新建索引语句暂无法检测！',
errormessage='WARNING:新建表的新建索引语句暂无法检测！',
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
sql=sqlitem.statement)
elif len(object_name_list) > 0 and self.get_dml_table(
db_name=db_name,
sql=sql_lower,
object_name_list=object_name_list):
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='WARNING:新建表的数据修改暂无法检测！',
errormessage='WARNING:新建表的数据修改暂无法检测！',
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
sql=sqlitem.statement)
else:
result_set = self.explain_check(db_name=db_name,
sql=sqlitem.statement,
close_conn=False)
if result_set['msg']:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='explain语法检查未通过！',
errormessage=result_set['msg'],
sql=sqlitem.statement)
else:
# 对create table\create index\create unique index语法做对象存在性检测
if re.match(
r"^create\s+table|^create\s+index|^create\s+unique\s+index",
sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
# 保存create对象对后续SQL做存在性判断
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if self.object_name_check(
db_name=db_name,
object_name=object_name
) or object_name in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象已经存在！""",
errormessage=
f"""{object_name}对象已经存在！""",
sql=sqlitem.statement)
else:
object_name_list.add(object_name)
if result_set['rows'] > 1000:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='影响行数大于1000，请关注',
errormessage='影响行数大于1000，请关注',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
if result_set['rows'] > 1000:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='影响行数大于1000，请关注',
errormessage='影响行数大于1000，请关注',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
# 其它无法用explain判断的语句
else:
# 对alter table做对象存在性检查
if re.match(r"^alter\s+table\s", sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if not self.object_name_check(
db_name=db_name, object_name=object_name
) and object_name not in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象不存在！""",
errormessage=f"""{object_name}对象不存在！""",
sql=sqlitem.statement)
else:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
# 对create做对象存在性检查
elif re.match(r"^create", sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if self.object_name_check(
db_name=db_name, object_name=object_name
) or object_name in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象已经存在！""",
errormessage=f"""{object_name}对象已经存在！""",
sql=sqlitem.statement)
else:
object_name_list.add(object_name)
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
# 判断工单类型
if get_syntax_type(sql=sqlitem.statement,
db_type='oracle') == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回，提高效率
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
except Exception as e:
logger.warning(
f"Oracle 语句执行报错，第{line}个SQL：{sqlitem.statement}，错误信息{traceback.format_exc()}"
)
check_result.error = str(e)
finally:
if close_conn:
self.close()
return check_result