def execute_select_check(self, db_name=None, sql=''): """上线单执行前的检查, 返回Review set""" # 进行Inception检查,获取检测结果 config = SysConfig() # 获取一个mysql连接,用来检测select语法 conn = self.get_connection() try: inception_engine = InceptionEngine() inc_check_result = inception_engine.execute_select_check(instance=self.instance, db_name=db_name, sql=sql, conn=conn) except Exception as e: logger.debug(f"Inception检测语句报错:错误信息{traceback.format_exc()}") raise RuntimeError(f"Inception检测语句报错,请注意检查系统配置中Inception配置,错误信息:\n{e}") # 判断Inception检测结果 if inc_check_result.error: logger.debug(f"检测语句报错:错误信息{inc_check_result.error}") raise RuntimeError(f"检测语句报错,错误信息:\n{inc_check_result.error}") # 禁用/高危语句检查 check_critical_result = ReviewSet(full_sql=sql) line = 1 critical_ddl_regex = config.get('critical_ddl_regex', '') p = re.compile(critical_ddl_regex) check_critical_result.syntax_type = 0 # TODO 工单类型 0、其他 1、DDL,2、DML for row in inc_check_result.rows: statement = row.sql # 去除注释 statement = remove_comments(statement, db_type='mysql') # 高危语句 if critical_ddl_regex and p.match(statement.strip().lower()): check_critical_result.is_critical = True result = ReviewResult(id=line, errlevel=2, stagestatus='驳回高危SQL', errormessage='禁止提交匹配' + critical_ddl_regex + '条件的语句!', sql=statement) # 正常语句 else: result = ReviewResult(id=line, errlevel=0, stagestatus='Audit completed', errormessage='None', sql=statement, affected_rows=0, execute_time=0, ) # 没有找出DDL语句的才继续执行此判断 if check_critical_result.syntax_type == 2: if get_syntax_type(statement, parser=False, db_type='mysql') == 'DDL': check_critical_result.syntax_type = 1 check_critical_result.rows += [result] # 遇到禁用和高危语句直接返回 if check_critical_result.is_critical: check_critical_result.error_count += 1 return check_critical_result line += 1 return inc_check_result
def is_auto_review(workflow_id): """ 判断SQL上线是否无需审批,无需审批的提交会自动审核通过 :param workflow_id: :return: """ workflow = SqlWorkflow.objects.get(id=workflow_id) auto_review_tags = SysConfig().get('auto_review_tag', '').split(',') auto_review_db_type = SysConfig().get('auto_review_db_type', '').split(',') # TODO 这里也可以放到engine中实现,但是配置项可能会相对复杂 if workflow.instance.db_type in auto_review_db_type and workflow.instance.instance_tag.filter( tag_code__in=auto_review_tags).exists(): # 获取正则表达式 auto_review_regex = SysConfig().get( 'auto_review_regex', '^alter|^create|^drop|^truncate|^rename|^delete') p = re.compile(auto_review_regex, re.I) # 判断是否匹配到需要手动审核的语句 auto_review = True all_affected_rows = 0 review_content = workflow.sqlworkflowcontent.review_content for review_row in json.loads(review_content): review_result = ReviewResult(**review_row) # 去除SQL注释 https://github.com/hhyo/Archery/issues/949 sql = remove_comments(review_result.sql) # 正则匹配 if p.match(sql): auto_review = False break # 影响行数加测, 总语句影响行数超过指定数量则需要人工审核 all_affected_rows += int(review_result.affected_rows) if all_affected_rows > int(SysConfig().get( 'auto_review_max_update_rows', 50)): auto_review = False else: auto_review = False return auto_review