1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
| import csv
import re
from datetime import datetime
class Validator:
def __init__(self):
self.id_weights = [7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2]
self.id_chars = ['1','0','X','9','8','7','6','5','4','3','2']
self.violation_rules = {
'客服': ['product_management','system_logs'],
'财务': ['user_management','product_management','system_logs'],
'商品经理': ['user_management','order_management','system_logs'],
'系统审计员': ['user_management','product_management','order_management']
}
def validate_name(self, name):
if not name or not isinstance(name, str):
return False
return 2 <= len(name) <= 4 and bool(re.match(r'^[\u4e00-\u9fa5]+$', name))
def validate_phone(self, phone):
if not phone or not isinstance(phone, str):
return False
return len(phone) == 11 and phone.isdigit() and phone.startswith('1') and phone[1] in '3456789'
def validate_id_card(self, id_card):
if not id_card or len(id_card) != 18 or not id_card[:17].isdigit():
return False
last_char = id_card[17].upper()
total = sum(int(id_card[i]) * self.id_weights[i] for i in range(17))
return last_char == self.id_chars[total % 11]
def validate_bank_card(self, card):
if not card or not card.isdigit() or len(card) < 16 or len(card) > 19:
return False
digits = [int(d) for d in card][::-1]
total = 0
for i, digit in enumerate(digits):
if i % 2 == 1:
doubled = digit * 2
total += doubled - 9 if doubled > 9 else doubled
else:
total += digit
return total % 10 == 0
def validate_reg_date(self, reg_date, id_card):
try:
reg_dt = datetime.strptime(reg_date, '%Y/%m/%d')
if reg_dt < datetime(2015,1,1) or reg_dt > datetime(2025,10,31):
return False
if id_card and self.validate_id_card(id_card):
birth_date_str = id_card[6:14]
birth_dt = datetime.strptime(birth_date_str, '%Y%m%d')
if reg_dt < birth_dt:
return False
return True
except:
return False
def check_operation(self, role, module):
return role not in self.violation_rules or module not in self.violation_rules[role]
def parse_sql_file(filename):
"""解析SQL文件，提取表数据"""
tables = {'users': [], 'operations': [], 'roles': []}
with open(filename, 'r', encoding='utf-8') as f:
content = f.read()
for table_name in tables.keys():
pattern = rf"INSERT INTO `{table_name}` VALUES (.*?);"
matches = re.findall(pattern, content, re.DOTALL)
for match in matches:
rows = re.findall(r'\((.*?)\)', match)
for row in rows:
values = []
current = ""
in_quote = False
escape_next = False
for char in row + ',':
if escape_next:
current += char
escape_next = False
elif char == '\\':
current += char
escape_next = True
elif char == "'":
in_quote = not in_quote
current += char
elif char == ',' and not in_quote:
values.append(current.strip())
current = ""
else:
current += char
cleaned_values = []
for val in values:
val = val.strip()
if val.startswith("'") and val.endswith("'"):
val = val[1:-1]
val = val.replace("\\'", "'").replace('\\"', '"').replace('\\\\', '\\')
cleaned_values.append(val)
if cleaned_values:
tables[table_name].append(cleaned_values)
return tables
def debug_print_tables(tables):
"""调试函数：打印解析的数据"""
print("解析到的数据:")
for table_name, rows in tables.items():
print(f"\n{table_name} 表 ({len(rows)} 行):")
for i, row in enumerate(rows[:3]):
print(f" 第{i+1}行: {row}")
def main():
tables = parse_sql_file('data.sql')
debug_print_tables(tables)
validator = Validator()
violations = {}
print("\n信息违规检查:")
for user_data in tables['users']:
if len(user_data) >= 7:
user_id, name, phone, id_card, bank_card, reg_date, role = user_data[:7]
print(f"检查用户 {name}:")
print(f" 姓名验证: {validator.validate_name(name)}")
print(f" 手机号验证: {validator.validate_phone(phone)}")
print(f" 身份证验证: {validator.validate_id_card(id_card)}")
print(f" 银行卡验证: {validator.validate_bank_card(bank_card)}")
print(f" 注册日期验证: {validator.validate_reg_date(reg_date, id_card)}")
if not all([
validator.validate_name(name),
validator.validate_phone(phone),
validator.validate_id_card(id_card),
validator.validate_bank_card(bank_card),
validator.validate_reg_date(reg_date, id_card)
]):
violations.setdefault(name, set()).add('信息违规')
print(f" → 发现信息违规")
user_id_to_name = {}
user_id_to_role = {}
for user_data in tables['users']:
if len(user_data) >= 7:
user_id, name, phone, id_card, bank_card, reg_date, role = user_data[:7]
user_id_to_name[user_id] = name
user_id_to_role[user_id] = role
print("\n操作违规检查:")
for op_data in tables['operations']:
if len(op_data) >= 5:
op_id, user_id, op_type, op_module, timestamp = op_data[:5]
user_name = user_id_to_name.get(user_id)
user_role = user_id_to_role.get(user_id)
if user_name and user_role:
has_permission = validator.check_operation(user_role, op_module)
print(f"用户 {user_name}({user_role}) 操作 {op_module}: {'合规' if has_permission else '违规'}")
if not has_permission:
violations.setdefault(user_name, set()).add('操作违规')
print(f"\n总共发现 {len(violations)} 个用户有违规")
with open('violations.csv', 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['姓名', '违规类型'])
for name, types in violations.items():
for violation_type in types:
writer.writerow([name, violation_type])
print(f"记录违规: {name}, {violation_type}")
if __name__ == '__main__':
main()
|
