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