def execute_skipinc_call_back(workflowId, instance_name, db_name, sql_content, url): workflowDetail = SqlWorkflow.objects.get(id=workflowId) try: # 执行sql t_start = time.time() execute_result = Dao(instance_name=instance_name).mysql_execute( db_name, sql_content) t_end = time.time() execute_time = "%5s" % "{:.4f}".format(t_end - t_start) execute_result['execute_time'] = execute_time + 'sec' workflowDetail = SqlWorkflow.objects.get(id=workflowId) if execute_result.get('Warning'): workflowDetail.status = Const.workflowStatus['exception'] elif execute_result.get('Error'): workflowDetail.status = Const.workflowStatus['exception'] else: workflowDetail.status = Const.workflowStatus['finish'] workflowDetail.finish_time = timezone.now() workflowDetail.execute_result = json.dumps(execute_result) workflowDetail.is_manual = 1 workflowDetail.audit_remark = '' workflowDetail.is_backup = '否' # 关闭后重新获取连接,防止超时 connection.close() workflowDetail.save() except Exception as e: logger.error(e) # 发送消息 send_msg(workflowDetail, url)
def execute_skipinc_call_back(workflowId, instance_name, db_name, sql_content, url): workflowDetail = SqlWorkflow.objects.get(id=workflowId) try: # 执行sql t_start = time.time() execute_result = Dao(instance_name=instance_name).mysql_execute( db_name, sql_content) t_end = time.time() execute_time = "%5s" % "{:.4f}".format(t_end - t_start) execute_result['execute_time'] = execute_time + 'sec' workflowDetail = SqlWorkflow.objects.get(id=workflowId) if execute_result.get('Warning'): workflowDetail.status = Const.workflowStatus['exception'] elif execute_result.get('Error'): workflowDetail.status = Const.workflowStatus['exception'] else: workflowDetail.status = Const.workflowStatus['finish'] workflowDetail.finish_time = timezone.now() workflowDetail.execute_result = json.dumps(execute_result) workflowDetail.is_manual = 1 workflowDetail.audit_remark = '' workflowDetail.is_backup = '否' # 关闭后重新获取连接,防止超时 connection.close() workflowDetail.save() except Exception: logger.error(traceback.format_exc()) # 增加工单日志 # 获取audit_id audit_id = Workflow.auditinfobyworkflow_id( workflow_id=workflowId, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id Workflow.add_workflow_log(audit_id=audit_id, operation_type=6, operation_type_desc='执行结束', operation_info='执行结果:{}'.format( workflowDetail.status), operator='', operator_display='系统') # 发送消息 send_msg(workflowDetail, url)
def execute_skipinc_call_back(workflowId, clusterName, db_name, sql_content, url): workflowDetail = SqlWorkflow.objects.get(id=workflowId) # 获取审核人 reviewMan = workflowDetail.review_man # 获取实例连接信息 masterInfo = getMasterConnStr(clusterName) try: # 执行sql t_start = time.time() execute_result = Dao().mysql_execute(masterInfo['masterHost'], masterInfo['masterPort'], masterInfo['masterUser'], masterInfo['masterPassword'], db_name, sql_content) t_end = time.time() execute_time = "%5s" % "{:.4f}".format(t_end - t_start) execute_result['execute_time'] = execute_time + 'sec' workflowDetail = SqlWorkflow.objects.get(id=workflowId) if execute_result.get('Warning'): workflowDetail.status = Const.workflowStatus['exception'] elif execute_result.get('Error'): workflowDetail.status = Const.workflowStatus['exception'] else: workflowDetail.status = Const.workflowStatus['finish'] workflowDetail.finish_time = timezone.now() workflowDetail.execute_result = json.dumps(execute_result) workflowDetail.is_manual = 1 workflowDetail.audit_remark = '' workflowDetail.is_backup = '否' # 关闭后重新获取连接,防止超时 connection.close() workflowDetail.save() except Exception as e: logger.error(e) # 发送消息 send_msg(workflowDetail, url)
def query(request): instance_name = request.POST.get('instance_name') sql_content = request.POST.get('sql_content') db_name = request.POST.get('db_name') limit_num = request.POST.get('limit_num') result = {'status': 0, 'msg': 'ok', 'data': {}} # 服务器端参数验证 if sql_content is None or db_name is None or instance_name is None or limit_num is None: result['status'] = 1 result['msg'] = '页面提交参数可能为空' return HttpResponse(json.dumps(result), content_type='application/json') sql_content = sql_content.strip() # 获取用户信息 user = request.user # 过滤注释语句和非查询的语句 sql_content = ''.join( map( lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub( '', x, count=1), sql_content.splitlines(1))).strip() # 去除空行 sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content) sql_list = sql_content.strip().split('\n') for sql in sql_list: if re.match(r"^select|^show|^explain", sql.lower()): break else: result['status'] = 1 result['msg'] = '仅支持^select|^show|^explain语法,请联系管理员!' return HttpResponse(json.dumps(result), content_type='application/json') # 按照分号截取第一条有效sql执行 sql_content = sql_content.strip().split(';')[0] try: # 查询权限校验 priv_check_info = query_priv_check(user, instance_name, db_name, sql_content, limit_num) if priv_check_info['status'] == 0: limit_num = priv_check_info['data']['limit_num'] priv_check = priv_check_info['data']['priv_check'] else: return HttpResponse(json.dumps(priv_check_info), content_type='application/json') if re.match(r"^explain", sql_content.lower()): limit_num = 0 # 对查询sql增加limit限制 if re.match(r"^select", sql_content.lower()): if re.search(r"limit\s+(\d+)$", sql_content.lower()) is None: if re.search(r"limit\s+\d+\s*,\s*(\d+)$", sql_content.lower()) is None: sql_content = sql_content + ' limit ' + str(limit_num) sql_content = sql_content + ';' # 执行查询语句,统计执行时间 t_start = time.time() sql_result = Dao(instance_name=instance_name).mysql_query( str(db_name), sql_content, limit_num) t_end = time.time() cost_time = "%5s" % "{:.4f}".format(t_end - t_start) sql_result['cost_time'] = cost_time # 数据脱敏,同样需要检查配置,是否开启脱敏,语法树解析是否允许出错继续执行 hit_rule = 0 if re.match(r"^select", sql_content.lower( )) else 2 # 查询是否命中脱敏规则,0, '未知', 1, '命中', 2, '未命中' masking = 2 # 查询结果是否正常脱敏,1, '是', 2, '否' t_start = time.time() # 仅对查询语句进行脱敏 if SysConfig().sys_config.get('data_masking') and re.match( r"^select", sql_content.lower()): try: masking_result = datamasking.data_masking( instance_name, db_name, sql_content, sql_result) if masking_result['status'] != 0 and SysConfig( ).sys_config.get('query_check'): return HttpResponse(json.dumps(masking_result), content_type='application/json') else: hit_rule = masking_result['data']['hit_rule'] masking = 1 if hit_rule == 1 else 2 except Exception: logger.error(traceback.format_exc()) hit_rule = 0 masking = 2 if SysConfig().sys_config.get('query_check'): result['status'] = 1 result['msg'] = '脱敏数据报错,请联系管理员' return HttpResponse(json.dumps(result), content_type='application/json') t_end = time.time() masking_cost_time = "%5s" % "{:.4f}".format(t_end - t_start) sql_result['masking_cost_time'] = masking_cost_time result['data'] = sql_result # 成功的查询语句记录存入数据库 if sql_result.get('Error'): pass else: query_log = QueryLog() query_log.username = user.username query_log.user_display = user.display query_log.db_name = db_name query_log.instance_name = instance_name query_log.sqllog = sql_content if int(limit_num) == 0: limit_num = int(sql_result['effect_row']) else: limit_num = min(int(limit_num), int(sql_result['effect_row'])) query_log.effect_row = limit_num query_log.cost_time = cost_time query_log.priv_check = priv_check query_log.hit_rule = hit_rule query_log.masking = masking # 防止查询超时 try: query_log.save() except: connection.close() query_log.save() except Exception as e: logger.error(traceback.format_exc()) result['status'] = 1 result['msg'] = str(e) # 返回查询结果 try: return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json') except Exception: return HttpResponse(json.dumps(result, default=str, bigint_as_string=True, encoding='latin1'), content_type='application/json')
def query(request): instance_name = request.POST.get('instance_name') sqlContent = request.POST.get('sql_content') dbName = request.POST.get('db_name') limit_num = request.POST.get('limit_num') finalResult = {'status': 0, 'msg': 'ok', 'data': {}} # 服务器端参数验证 if sqlContent is None or dbName is None or instance_name is None or limit_num is None: finalResult['status'] = 1 finalResult['msg'] = '页面提交参数可能为空' return HttpResponse(json.dumps(finalResult), content_type='application/json') sqlContent = sqlContent.strip() if sqlContent[-1] != ";": finalResult['status'] = 1 finalResult['msg'] = 'SQL语句结尾没有以;结尾,请重新修改并提交!' return HttpResponse(json.dumps(finalResult), content_type='application/json') # 获取用户信息 user = request.user # 过滤注释语句和非查询的语句 sqlContent = ''.join( map(lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub('', x, count=1), sqlContent.splitlines(1))).strip() # 去除空行 sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent) sql_list = sqlContent.strip().split('\n') for sql in sql_list: if re.match(r"^select|^show|^explain", sql.lower()): break else: finalResult['status'] = 1 finalResult['msg'] = '仅支持^select|^show|^explain语法,请联系管理员!' return HttpResponse(json.dumps(finalResult), content_type='application/json') # 取出该实例的连接方式,查询只读账号,按照分号截取第一条有效sql执行 slave_info = Instance.objects.get(instance_name=instance_name) sqlContent = sqlContent.strip().split(';')[0] # 查询权限校验 priv_check_info = query_priv_check(user, instance_name, dbName, sqlContent, limit_num) if priv_check_info['status'] == 0: limit_num = priv_check_info['data'] else: return HttpResponse(json.dumps(priv_check_info), content_type='application/json') if re.match(r"^explain", sqlContent.lower()): limit_num = 0 # 对查询sql增加limit限制 if re.match(r"^select", sqlContent.lower()): if re.search(r"limit\s+(\d+)$", sqlContent.lower()) is None: if re.search(r"limit\s+\d+\s*,\s*(\d+)$", sqlContent.lower()) is None: sqlContent = sqlContent + ' limit ' + str(limit_num) sqlContent = sqlContent + ';' # 执行查询语句,统计执行时间 t_start = time.time() sql_result = Dao(instance_name=instance_name).mysql_query(str(dbName), sqlContent, limit_num) t_end = time.time() cost_time = "%5s" % "{:.4f}".format(t_end - t_start) sql_result['cost_time'] = cost_time # 数据脱敏,同样需要检查配置,是否开启脱敏,语法树解析是否允许出错继续执行 t_start = time.time() if SysConfig().sys_config.get('data_masking') == 'true': # 仅对查询语句进行脱敏 if re.match(r"^select", sqlContent.lower()): try: masking_result = datamasking.data_masking(instance_name, dbName, sqlContent, sql_result) except Exception: if SysConfig().sys_config.get('query_check') == 'true': finalResult['status'] = 1 finalResult['msg'] = '脱敏数据报错,请联系管理员' return HttpResponse(json.dumps(finalResult), content_type='application/json') else: if masking_result['status'] != 0: if SysConfig().sys_config.get('query_check') == 'true': return HttpResponse(json.dumps(masking_result), content_type='application/json') t_end = time.time() masking_cost_time = "%5s" % "{:.4f}".format(t_end - t_start) sql_result['masking_cost_time'] = masking_cost_time finalResult['data'] = sql_result # 成功的查询语句记录存入数据库 if sql_result.get('Error'): pass else: query_log = QueryLog() query_log.username = user.username query_log.user_display = user.display query_log.db_name = dbName query_log.instance_name = instance_name query_log.sqllog = sqlContent if int(limit_num) == 0: limit_num = int(sql_result['effect_row']) else: limit_num = min(int(limit_num), int(sql_result['effect_row'])) query_log.effect_row = limit_num query_log.cost_time = cost_time # 防止查询超时 try: query_log.save() except: connection.close() query_log.save() # 返回查询结果 return HttpResponse(json.dumps(finalResult, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')