def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = int(request.POST.get('limit_num', 0))
schema_name = request.POST.get('schema_name', None)
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, limit_num]:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, 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:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
# explain的limit_num设置为0
limit_num = 0 if re.match(r"^explain",
sql_content.lower()) else limit_num
# 对查询sql增加limit限制或者改写语句
sql_content = query_engine.filter_sql(sql=sql_content,
limit_num=limit_num)
# 先获取查询连接，用于后面查询复用连接以及终止会话
query_engine.get_connection(db_name=db_name)
thread_id = query_engine.thread_id
max_execution_time = int(config.get('max_execution_time', 60))
# 执行查询语句，并增加一个定时终止语句的schedule，timeout=max_execution_time
if thread_id:
schedule_name = f'query-{time.time()}'
run_date = (datetime.datetime.now() +
datetime.timedelta(seconds=max_execution_time))
add_kill_conn_schedule(schedule_name, run_date, instance.id,
thread_id)
with FuncTimer() as t:
# 获取主从延迟信息
seconds_behind_master = query_engine.seconds_behind_master
if instance.db_type == 'pgsql': # TODO 此处判断待优化，请在 修改传参方式后去除
query_result = query_engine.query(db_name,
sql_content,
limit_num,
schema_name=schema_name)
else:
query_result = query_engine.query(db_name, sql_content,
limit_num)
query_result.query_time = t.cost
# 返回查询结果后删除schedule
if thread_id:
del_schedule(schedule_name)
# 查询异常
if query_result.error:
result['status'] = 1
result['msg'] = query_result.error
# 数据脱敏，仅对查询无错误的结果集进行脱敏，并且按照query_check配置是否返回
elif config.get('data_masking'):
try:
with FuncTimer() as t:
masking_result = query_engine.query_masking(
db_name, sql_content, query_result)
masking_result.mask_time = t.cost
# 脱敏出错
if masking_result.error:
# 开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常：{masking_result.error}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
logger.warning(
f'数据脱敏异常，按照配置放行，查询语句：{sql_content}，错误信息：{masking_result.error}'
)
query_result.error = None
result['data'] = query_result.__dict__
# 正常脱敏
else:
result['data'] = masking_result.__dict__
except Exception as msg:
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常，请联系管理员，错误信息：{msg}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
logger.warning(
f'数据脱敏异常，按照配置放行，查询语句：{sql_content}，错误信息：{msg}')
query_result.error = None
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
result['data']['seconds_behind_master'] = seconds_behind_master
if int(limit_num) == 0:
limit_num = int(query_result.affected_rows)
else:
limit_num = min(int(limit_num),
int(query_result.affected_rows))
query_log = QueryLog(username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=query_result.mask_rule_hit,
masking=query_result.is_masked)
# 防止查询超时
try:
query_log.save()
except OperationalError:
connection.close()
query_log.save()
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoderFTime,
bigint_as_string=True),
content_type='application/json')
# 虽然能正常返回，但是依然会乱码
except UnicodeDecodeError:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = int(request.POST.get('limit_num', 0))
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
if not (sql_content and db_name and instance_name and limit_num):
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and SysConfig().get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, 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:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
# explain的limit_num设置为0
limit_num = 0 if re.match(r"^explain",
sql_content.lower()) else limit_num
# 对查询sql增加limit限制或者改写语句
sql_content = query_engine.filter_sql(sql=sql_content,
limit_num=limit_num)
# 执行查询语句,统计执行时间
t_start = time.time()
query_result = query_engine.query(db_name=str(db_name),
sql=sql_content,
limit_num=limit_num)
t_end = time.time()
query_result.query_time = "%5s" % "{:.4f}".format(t_end - t_start)
# 数据脱敏，仅对查询无错误的结果集进行脱敏
if SysConfig().get('data_masking') and query_result.error is None:
try:
# 记录脱敏时间
t_start = time.time()
masking_result = query_engine.query_masking(
db_name=db_name, sql=sql_content, resultset=query_result)
t_end = time.time()
masking_result.mask_time = "%5s" % "{:.4f}".format(t_end -
t_start)
# 脱敏出错，并且开启query_check，直接返回异常，禁止执行
if masking_result.error and SysConfig().get('query_check'):
result['status'] = 1
result['msg'] = masking_result.error
# 脱敏出错，关闭query_check，忽略错误信息，返回未脱敏数据
elif masking_result.error and not SysConfig().get(
'query_check'):
query_result.error = None
result['data'] = query_result.__dict__
# 正常脱敏
else:
result['data'] = masking_result.__dict__
except Exception as e:
logger.error(
f'数据脱敏异常，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}'
)
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if SysConfig().get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常，请联系管理员，错误信息：{e}'
# 关闭query_check，忽略错误信息，返回未脱敏数据
else:
query_result.error = None
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
if query_result.error:
result['status'] = 1
result['msg'] = query_result.error
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
if int(limit_num) == 0:
limit_num = int(query_result.affected_rows)
else:
limit_num = min(int(limit_num),
int(query_result.affected_rows))
query_log = QueryLog(username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=query_result.mask_rule_hit,
masking=query_result.is_masked)
# 防止查询超时
try:
query_log.save()
except:
connection.close()
query_log.save()
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
# 虽然能正常返回，但是依然会乱码
except UnicodeDecodeError:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = int(request.POST.get('limit_num', 0))
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, limit_num]:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, 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:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
# explain的limit_num设置为0
limit_num = 0 if re.match(r"^explain",
sql_content.lower()) else limit_num
# 对查询sql增加limit限制或者改写语句
sql_content = query_engine.filter_sql(sql=sql_content,
limit_num=limit_num)
# 执行查询语句，timeout=max_execution_time
max_execution_time = int(config.get('max_execution_time', 60))
query_task_id = async_task(query_engine.query,
db_name=str(db_name),
sql=sql_content,
limit_num=limit_num,
timeout=max_execution_time,
cached=60)
# 等待执行结果，max_execution_time后还没有返回结果代表将会被终止
query_task = fetch(query_task_id,
wait=max_execution_time * 1000,
cached=True)
# 在max_execution_time内执行结束
if query_task:
if query_task.success:
query_result = query_task.result
query_result.query_time = query_task.time_taken()
else:
query_result = ResultSet(full_sql=sql_content)
query_result.error = query_task.result
# 等待超时，async_task主动关闭连接
else:
query_result = ResultSet(full_sql=sql_content)
query_result.error = f'查询时间超过 {max_execution_time} 秒，已被主动终止，请优化语句或者联系管理员。'
# 查询异常
if query_result.error:
result['status'] = 1
result['msg'] = query_result.error
# 数据脱敏，仅对查询无错误的结果集进行脱敏，并且按照query_check配置是否返回
elif config.get('data_masking'):
query_masking_task_id = async_task(query_engine.query_masking,
db_name=db_name,
sql=sql_content,
resultset=query_result,
cached=60)
query_masking_task = fetch(query_masking_task_id,
wait=60 * 1000,
cached=True)
if query_masking_task.success:
masking_result = query_masking_task.result
masking_result.mask_time = query_masking_task.time_taken()
# 脱敏出错
if masking_result.error:
# 开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = masking_result.error
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
# 正常脱敏
else:
result['data'] = masking_result.__dict__
else:
logger.error(
f'数据脱敏异常，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}'
)
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result[
'msg'] = f'数据脱敏异常，请联系管理员，错误信息：{query_masking_task.result}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
if int(limit_num) == 0:
limit_num = int(query_result.affected_rows)
else:
limit_num = min(int(limit_num),
int(query_result.affected_rows))
query_log = QueryLog(username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=query_result.mask_rule_hit,
masking=query_result.is_masked)
# 防止查询超时
try:
query_log.save()
except OperationalError:
connection.close()
query_log.save()
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
# 虽然能正常返回，但是依然会乱码
except UnicodeDecodeError:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def newquery(request):
"""正式提交SQL, 此处生成工单"""
workflow_name = request.POST.get('workflow_name')
demand_url = request.POST.get('demand_url', '')
group_name = request.POST.get('group_name')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
first_run_time = request.POST.get('first_run_time')
schedule_type = request.POST.get('period')
minutes = request.POST.get('minutes')
receivers = request.POST.getlist('receivers')
cc_list = request.POST.getlist('cc_list')
is_backup = False
sql_content = request.POST.get('sql_content').strip()
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if not all([
workflow_name, group_name, instance_name, db_name, first_run_time,
schedule_type, sql_content
]):
result['status'] = 1
result['msg'] = '请检查提交工单填写内容是否完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
user = request.user
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance = Instance.objects.get(instance_name=instance_name)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(user,
tag_codes=['can_read']).get(instance_name=instance_name)
except instance.DoesNotExist:
result['status'] = 1
result['msg'] = '你所在组未关联该实例'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, db_name,
sql_content, 0)
if priv_check_info['status'] != 0:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
check_result = ReviewSet(full_sql=sql_content)
check_result.rows = [
ReviewResult(
id=1,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sql_content,
affected_rows=0,
execute_time=0,
)
]
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
order_type="sqlcron_order",
workflow_name=workflow_name,
demand_url=demand_url,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status='workflow_manreviewing',
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=3, #0、未知，1、DDL，2、DML 3、QUERY'
create_time=timezone.now(),
run_date_start=None,
run_date_end=None,
cc_list=cc_list,
)
sql_workflow.receivers.set(
Users.objects.filter(username__in=receivers))
sql_workflow_content = SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
sched = schedule(
'sql.utils.query_sql.query',
sql_workflow.id,
hook='sql.utils.query_sql.query_callback',
name=f'sqlcron-{sql_workflow.id}',
schedule_type=schedule_type,
minutes=minutes,
next_run=first_run_time,
repeats=0, # 在审批结束前创建但不启用
timeout=-1,
)
sql_workflow.schedule = sched
sql_workflow.save()
sql_workflow_content.save()
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], sql_workflow.id)
except Exception as e:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
result['status'] = 1
result['msg'] = f'提交工单报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
# 进行消息通知
audit_id = Audit.detail_by_workflow_id(
workflow_id=sql_workflow.id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
cc_users=receivers,
timeout=60,
task_name=f'sqlreview-submit-{sql_workflow.id}')
result['status'] = 0
result['data'] = {
'redirect': reverse('sql:sqlcrondetail', args=(sql_workflow.id, ))
}
return HttpResponse(json.dumps(result), content_type='application/json')
def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
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')
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
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 = sqlparse.format(sql_content.strip(), strip_comments=True)
sql_list = sqlparse.split(sql_content)
# 执行第一条有效sql
sql_content = sql_list[0].rstrip(';')
if re.match(r"^select|^show|^explain", sql_content, re.I) is None:
result['status'] = 1
result['msg'] = '仅支持^select|^show|^explain语法，请联系管理员！'
return HttpResponse(json.dumps(result), content_type='application/json')
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:
result['status'] = priv_check_info['status']
result['msg'] = priv_check_info['msg']
data = ResultSet(full_sql=sql_content)
data.error = priv_check_info['msg']
result['data'] = data.__dict__
return HttpResponse(json.dumps(result), content_type='application/json')
limit_num = 0 if re.match(r"^explain", sql_content.lower()) else limit_num
# 查询检查
query_engine = get_engine(instance=instance)
filter_result = query_engine.query_check(db_name=db_name, sql=sql_content, limit_num=limit_num)
if filter_result.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = filter_result.get('msg')
return HttpResponse(json.dumps(result), content_type='application/json')
if filter_result.get('has_star') and SysConfig().get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = filter_result.get('msg')
return HttpResponse(json.dumps(result), content_type='application/json')
else:
sql_content = filter_result['filtered_sql']
sql_content = sql_content + ';'
# 执行查询语句,统计执行时间
t_start = time.time()
query_result = query_engine.query(db_name=str(db_name), sql=sql_content, limit_num=limit_num)
t_end = time.time()
query_result.query_time = "%5s" % "{:.4f}".format(t_end - t_start)
# 数据脱敏，同样需要检查配置，是否开启脱敏，语法树解析是否允许出错继续执行
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().get('data_masking') and re.match(r"^select", sql_content.lower()) and query_result.error is None:
try:
query_result = query_engine.query_masking(db_name=db_name, sql=sql_content, resultset=query_result)
if query_result.is_critical is True and SysConfig().get('query_check'):
masking_result = {'status': query_result.status,
'msg': query_result.error,
'data': query_result.__dict__}
return HttpResponse(json.dumps(masking_result), content_type='application/json')
else:
# 重置脱敏结果，返回未脱敏数据
query_result.status = 0
query_result.error = None
# 实际未命中, 则显示为未做脱敏
if query_result.is_masked:
masking = 1
hit_rule = 1
except Exception:
logger.error(traceback.format_exc())
# 报错, 未脱敏, 未命中
hit_rule = 2
masking = 2
if SysConfig().get('query_check'):
result['status'] = 1
result['msg'] = '脱敏数据报错,请联系管理员'
return HttpResponse(json.dumps(result), content_type='application/json')
t_end = time.time()
query_result.mask_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result = query_result.__dict__
result['data'] = sql_result
# 成功的查询语句记录存入数据库
if sql_result.get('error'):
pass
else:
if int(limit_num) == 0:
limit_num = int(sql_result['affected_rows'])
else:
limit_num = min(int(limit_num), int(sql_result['affected_rows']))
query_log = QueryLog(
username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=hit_rule,
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')