From d1cfd849c6a61800dcd86ffb45f80882ae20b2e7 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期一, 14 九月 2026 22:32:36 +0800
Subject: [PATCH] security: 接口鉴权 + 跨域收紧 + AI 数据脱敏 + 日志脱敏
---
traffic-audit-server/src/main/java/com/trafficaudit/llmintegration/service/DbQueryExecutor.java | 96 ++++++++++++++++++++++++++++++++++++++++++++---
1 files changed, 89 insertions(+), 7 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/llmintegration/service/DbQueryExecutor.java b/traffic-audit-server/src/main/java/com/trafficaudit/llmintegration/service/DbQueryExecutor.java
index 9608cab..7bac2ab 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/llmintegration/service/DbQueryExecutor.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/llmintegration/service/DbQueryExecutor.java
@@ -28,6 +28,17 @@
@Resource
private JdbcTemplate jdbcTemplate;
+ @Resource
+ private DesensitizeService desensitizeService;
+
+ /** 绂佹澶栧彂鍒板ぇ妯″瀷鐨勫瓧娈碉細涓汉涓庝富浣撹韩浠界被淇℃伅锛堝湪鍙煡鍒椼�佽繃婊ゅ垪銆佹帓搴忓垪涓竴骞跺睆钄斤級 */
+ private static final java.util.Set<String> BLOCKED_COLUMNS = new java.util.TreeSet<>(Arrays.asList(
+ "unified_credit_code", "contact_person", "contact_phone",
+ "unit_leader", "stats_leader", "report_unit", "enterprise_code"));
+
+ /** 鑷敱鏂囨湰閲屾畫鐣欑殑鎵嬫満鍙� */
+ private static final Pattern PHONE = Pattern.compile("(?<!\\d)1[3-9]\\d{9}(?!\\d)");
+
public static final Set<String> ALLOWED_TABLES = new TreeSet<>();
private static final Map<String, TableMeta> TABLES = new LinkedHashMap<>();
@@ -95,6 +106,28 @@
new String[]{"report_period", "region_name", "freight_m01", "freight_m02", "freight_m03", "freight_m04",
"freight_m05", "freight_m06", "freight_m07", "freight_m08", "freight_m09", "freight_m10", "freight_m11",
"freight_m12", "turnover_m01", "turnover_m12"});
+ register("h2031_enterprise_monthly", "鍏矾鏃呭鏈堟姤H203-1琛�(閬撹矾鏃呭杩愯緭鏈堝害鐢熶骇鎯呭喌)",
+ new String[]{"id", "report_period", "region_code", "enterprise_code", "enterprise_name", "vehicle_total",
+ "seat_total", "passenger_total", "turnover_total", "avg_distance_total", "verify_explanation"},
+ new String[]{"涓婚敭", "鎶ヨ〃鏈�", "鎵�灞炲湴鍖轰唬鐮�", "浼佷笟浠ｇ爜", "浼佷笟鍚嶇О", "杞﹁締鏁�", "杞藉浣嶆暟",
+ "瀹㈣繍閲�(涓囦汉)", "鏃呭鍛ㄨ浆閲�(涓囦汉鍏噷)", "骞冲潎杩愯窛(鍏噷)", "浼佷笟鏍稿疄瑙ｉ噴"},
+ new String[]{"id", "report_period", "enterprise_name", "vehicle_total", "seat_total",
+ "passenger_total", "turnover_total", "avg_distance_total", "verify_explanation"});
+ register("city_bus_monthly", "鍩庡競鍏氦鏈堝害杩愯惀鎯呭喌(浼佷笟绾э紝鍚建閬�/杞浮瀛楁)",
+ new String[]{"id", "report_period", "region_code", "city", "enterprise_name", "op_vehicles",
+ "passenger_volume", "turnover", "avg_distance", "passenger_chengxiang", "turnover_chengxiang",
+ "verify_explanation"},
+ new String[]{"涓婚敭", "鎶ヨ〃鏈�", "鎵�灞炲湴鍖轰唬鐮�", "甯傚窞", "浼佷笟鍚嶇О", "杩愯惀杞︽暟", "瀹㈣繍閲�(涓囦汉娆�)",
+ "鏃呭鍛ㄨ浆閲�(涓囦汉鍏噷)", "骞冲潎杩愯窛(鍏噷)", "鍩庨檯鍩庝埂瀹㈣繍閲�", "鍩庨檯鍩庝埂鍛ㄨ浆閲�", "浼佷笟鏍稿疄瑙ｉ噴"},
+ new String[]{"id", "report_period", "enterprise_name", "op_vehicles", "passenger_volume",
+ "turnover", "avg_distance", "verify_explanation"});
+ register("city_taxi_monthly", "宸℃父鍑虹姹借溅杩愯惀鏈嶅姟鎯呭喌鏈堟姤(甯傚窞绾�)",
+ new String[]{"id", "report_period", "region_code", "city", "trip_total", "passenger_volume",
+ "turnover", "op_vehicles", "avg_distance", "verify_explanation"},
+ new String[]{"涓婚敭", "鎶ヨ〃鏈�", "鎵�灞炲湴鍖轰唬鐮�", "甯傚窞", "杞藉杞︽鎬绘暟", "瀹㈣繍閲�(涓囦汉娆�)",
+ "鏃呭鍛ㄨ浆閲�(涓囦汉鍏噷)", "杩愯惀杞﹁締鏁�", "骞冲潎杩愯窛(鍏噷)", "浼佷笟鏍稿疄瑙ｉ噴"},
+ new String[]{"id", "report_period", "city", "trip_total", "passenger_volume",
+ "turnover", "op_vehicles", "avg_distance", "verify_explanation"});
register("audit_result", "瀹℃牳缁撴灉",
new String[]{"id", "rule_id", "report_id", "enterprise_code", "report_period", "actual_value",
"threshold_value", "deviation", "status", "review_comment", "created_at"},
@@ -124,7 +157,7 @@
for (TableMeta meta : TABLES.values()) {
sb.append(meta.table).append("銆�").append(meta.desc).append("銆�: ");
List<String> parts = new ArrayList<>();
- for (String c : meta.cols) {
+ for (String c : selectable(meta)) {
parts.add(snakeToCamel(c) + "(" + meta.labels.getOrDefault(c, snakeToCamel(c)) + ")");
}
sb.append(String.join(", ", parts)).append("\n");
@@ -157,6 +190,7 @@
sql.append(" LIMIT ").append(lim);
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), w.args.toArray());
+ maskRows(rows);
out.set("success", true);
out.set("rows", rows);
out.set("count", rows.size());
@@ -174,21 +208,68 @@
if (columnsParam == null) {
return aliased(meta.defaults);
}
+ // 浼犲叆 "*" 鏃跺彧杩斿洖榛樿鍒楋紝涓嶅啀鏀惧紑鏁磋〃锛堟晱鎰熷瓧娈典笉鍙備笌鏌ヨ锛�
if (columnsParam instanceof String && "*".equals(columnsParam)) {
- return aliased(meta.cols);
+ return aliased(meta.defaults);
}
if (columnsParam instanceof List) {
List<String> out = new ArrayList<>();
+ List<String> usable = selectable(meta);
for (Object o : (List<?>) columnsParam) {
String c = normalizeCol(String.valueOf(o));
- if (!meta.cols.contains(c)) {
- throw new IllegalArgumentException("鍒楀悕涓嶅悎娉�: " + o + "锛岃琛ㄥ彲鐢ㄥ垪: " + camelList(meta.cols));
+ if (BLOCKED_COLUMNS.contains(c)) {
+ throw new IllegalArgumentException("璇ュ瓧娈典笉鍏佽鏌ヨ: " + o);
+ }
+ if (!usable.contains(c)) {
+ throw new IllegalArgumentException("鍒楀悕涓嶅悎娉�: " + o + "锛岃琛ㄥ彲鐢ㄥ垪: " + camelList(usable));
}
out.add(c);
}
return aliased(out.isEmpty() ? meta.defaults : out);
}
return aliased(meta.defaults);
+ }
+
+ /** 澶栧彂鍓嶈劚鏁忥細浼佷笟鍚嶇О鏇挎崲涓哄埆鍚嶏紝鑷敱鏂囨湰涓殑浼佷笟鍚嶇О涓庢墜鏈哄彿涓�骞跺鐞� */
+ private void maskRows(List<Map<String, Object>> rows) {
+ if (rows == null) {
+ return;
+ }
+ for (Map<String, Object> row : rows) {
+ Object nameObj = row.get("enterpriseName");
+ String realName = nameObj == null ? null : String.valueOf(nameObj);
+ String alias = null;
+ if (realName != null && !realName.isEmpty()) {
+ alias = desensitizeService.maskEnterprise(realName);
+ row.put("enterpriseName", alias);
+ }
+ for (Map.Entry<String, Object> e : row.entrySet()) {
+ Object v = e.getValue();
+ if (!(v instanceof String)) {
+ continue;
+ }
+ String s = (String) v;
+ String outText = s;
+ if (realName != null && !realName.isEmpty() && outText.contains(realName)) {
+ outText = outText.replace(realName, alias);
+ }
+ outText = PHONE.matcher(outText).replaceAll("鎵嬫満鍙峰凡鑴辨晱");
+ if (!outText.equals(s)) {
+ e.setValue(outText);
+ }
+ }
+ }
+ }
+
+ /** 璇ヨ〃鍙煡瀛楁 = 鍏ㄩ儴瀛楁 - 鏁忔劅瀛楁 */
+ private static List<String> selectable(TableMeta meta) {
+ List<String> out = new ArrayList<>();
+ for (String c : meta.cols) {
+ if (!BLOCKED_COLUMNS.contains(c)) {
+ out.add(c);
+ }
+ }
+ return out;
}
private List<String> aliased(List<String> cols) {
@@ -217,8 +298,9 @@
}
for (Map.Entry<String, Object> e : filters.entrySet()) {
String col = normalizeCol(String.valueOf(e.getKey()));
- if (!meta.cols.contains(col)) {
- throw new IllegalArgumentException("鍒楀悕涓嶅悎娉�: " + e.getKey() + "锛岃琛ㄥ彲鐢ㄥ垪: " + camelList(meta.cols));
+ List<String> usable = selectable(meta);
+ if (BLOCKED_COLUMNS.contains(col) || !usable.contains(col)) {
+ throw new IllegalArgumentException("鍒楀悕涓嶅悎娉�: " + e.getKey() + "锛岃琛ㄥ彲鐢ㄥ垪: " + camelList(usable));
}
Object v = e.getValue();
if (v instanceof List) {
@@ -259,7 +341,7 @@
s = s.substring(0, s.length() - 4).trim();
}
String col = normalizeCol(s);
- if (!meta.cols.contains(col)) {
+ if (BLOCKED_COLUMNS.contains(col) || !selectable(meta).contains(col)) {
throw new IllegalArgumentException("鎺掑簭鍒椾笉鍚堟硶: " + orderBy);
}
return col + (desc ? " DESC" : " ASC");
--
Gitblit v1.9.1