身份证

# 身份证号格式，可能会进⾏空格分隔或横线分隔
794688197610150966
794688 19761015 0966
794688-19761015-0966

idcard_pattern = r'\d{17}[\dXx]|\d{6}-\d{8}-[\dXx]{4}|\d{6} \d{8} [\dXx]{4}'

手机号

# ⼿机号格式，可能会进⾏空格分隔或横线分隔，可能会带有“+86 ”或“(+86)”前缀
19930800372
199 3080 0372
199-3080-0372
+86 19930800372
+86 199 3080 0372
+86 199-3080-0372
(+86)19930800372
(+86)199 3080 0372
(+86)199-3080-0372

phone_pattern = r'(?:\(\+86\)|\+86)?\s*(\d{3}(?:[\s\-]\d{4}[\s\-]\d{4}|\d{8}))'

银行卡号

银⾏卡号为 16-19 位数字字符串，使⽤ Luhn 算法进⾏校验：

# 银⾏卡格式，⽆变形
6228480402564890018

bankcard_pattern = r'(?<!\d)\d{16,19}(?!\d)'

校验算法：

def luhn_check(card_number):
        """Luhn算法校验"""
        total = 0
        # 从右到左遍历
        for i, digit in enumerate(reversed(card_number)):
            num = int(digit)
            if i % 2 == 1:  # 偶数位（从右往左数，索引从0开始）
                num *= 2
                if num > 9:
                    num = num // 10 + num % 10  # 各位数字相加
            total += num
        return total % 10 == 0

ip地址

ip地址开头之前和结尾之后不再出现数字：

# IP 地址格式，⽆变形
192.168.10.1

ip_pattern = r'(?<!\d)(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)(?!\d)'

mac地址

mac地址格式比较固定，大小写mac地址：

# MAC 地址格式，⽆变形
00:a0:c9:14:c8:29

mac_pattern = r'[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}'

常用脚本

合并xlsx：

import os
import pandas as pd

# 定义文件夹路径和输出文件名
folder_path = './'  # 存放 .xlsx 文件的文件夹路径
output_file = 'merged.xlsx'    # 合并后的输出文件名

# 创建一个空的 DataFrame 用于存储合并数据
merged_data = pd.DataFrame()

# 遍历文件夹中的所有 .xlsx 文件
for file_name in os.listdir(folder_path):
    if file_name.endswith('.xlsx'):
        file_path = os.path.join(folder_path, file_name)
        # 读取每个 Excel 文件
        data = pd.read_excel(file_path)
        # 将数据追加到合并的 DataFrame 中
        merged_data = pd.concat([merged_data, data], ignore_index=True)

# 将合并后的数据保存为新的 Excel 文件
merged_data.to_excel(output_file, index=False)

print(f"所有文件已成功合并并保存为 {output_file}")

题目

2025 羊城杯 dataIdSort

import re
import csv

def extract_and_save(filename):
    # 身份证模式
    id_pattern = r'\d{17}[\dXx]|\d{6}-\d{8}-[\dXx]{4}|\d{6} \d{8} [\dXx]{4}'
    id_coefficients = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2]
    id_check_table = {0: '1', 1: '0', 2: 'X', 3: '9', 4: '8', 5: '7', 6: '6', 7: '5', 8: '4', 9: '3', 10: '2'}

    common_bankcard_prefixes = {
        '622848', '622700', '621700', '622262', '622188', 
        '622200', '622568', '622609', '622908', '622518'
    }

    def is_valid_id(id_str):
        clean = id_str.replace('-', '').replace(' ', '')
        if len(clean) != 18:
            return False
        try:
            if clean[:6] in common_bankcard_prefixes:
                return False
            total = sum(int(clean[i]) * id_coefficients[i] for i in range(17))
            remainder = total % 11
            expected = id_check_table[remainder]
            return expected == clean[17].upper()
        except:
            return False

    phone_pattern = r'(?:\(\+86\)|\+86)?\s*(\d{3}(?:[\s\-]\d{4}[\s\-]\d{4}|\d{8}))'
    valid_prefixes = {
        '134', '135', '136', '137', '138', '139', '147', '148', '150', '151', '152', '157', '158', '159', '172', '178',
        '182', '183', '184', '187', '188', '195', '198', '130', '131', '132', '140', '145', '146', '155', '156', '166',
        '167', '171', '175', '176', '185', '186', '196', '133', '149', '153', '173', '174', '177', '180', '181', '189',
        '190', '191', '193', '199'
    }

    def is_valid_phone(phone_str):
        clean = re.sub(r'[^\d]', '', phone_str)
        if len(clean) != 11 and len(clean) != 13:
            return False
        if len(clean) == 11:
            return clean[:3] in valid_prefixes
        if len(clean) == 13:
            return clean[2:5] in valid_prefixes
    
    # 银行卡号模式
    bankcard_pattern = r'(?<!\d)\d{16,19}(?!\d)'
    
    common_bankcard_prefixes = {
        '622848', '622700', '621700', '622262', '622188', 
        '622200', '622568', '622609', '622908', '622518'
    }

    def luhn_check(card_number):
        """Luhn算法校验"""
        total = 0
        # 从右到左遍历
        for i, digit in enumerate(reversed(card_number)):
            num = int(digit)
            if i % 2 == 1:  # 偶数位（从右往左数，索引从0开始）
                num *= 2
                if num > 9:
                    num = num // 10 + num % 10  # 各位数字相加
            total += num
        return total % 10 == 0

    def is_valid_bankcard(card_str):
        """验证银行卡号"""
        # 检查长度
        if len(card_str) < 16 or len(card_str) > 19:
            return False
        
        # 检查是否全为数字
        if not card_str.isdigit():
            return False
        
        # 检查常见前缀
        has_common_prefix = any(card_str.startswith(prefix) for prefix in common_bankcard_prefixes)
        if not has_common_prefix:
            return False
        
        # Luhn算法校验
        return luhn_check(card_str)

    # IP地址模式
    ip_pattern = r'(?<!\d)(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)(?!\d)'
    
    def is_valid_ip(ip_str):
        parts = ip_str.split('.')
        if len(parts) != 4:
            return False
        for part in parts:
            if not 0 <= int(part) <= 255:
                return False
        return True

    # MAC地址模式
    mac_pattern = r'[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}:[0-9A-Fa-f]{2}'
    
    def is_valid_mac(mac_str):
        return True

    results = []
    
    with open(filename, 'r', encoding='utf-8') as f:
        content = f.read()
        check = []  # 用于排除手机号取身份证前11位的情况

        # 身份证匹配
        for match in re.findall(id_pattern, content):
            if is_valid_id(match):
                check.append(match[:11])
                results.append(['idcard', match])
            

        # 手机号匹配
        for match in re.finditer(phone_pattern, content):
            full_match = match.group()
            if is_valid_phone(full_match):
                
                if full_match[0] == " ":
                    full_match = full_match[1:len(full_match)]
                if full_match not in check:
                    results.append(['phone', full_match.strip() ])

        # 银行卡号匹配
        bankcard_matches = re.findall(bankcard_pattern, content)
        
        for match in bankcard_matches:
            if is_valid_bankcard(match):
                results.append(['bankcard', match])

        # IP地址匹配
        ip_matches = re.findall(ip_pattern, content)
        for match in ip_matches:
            if is_valid_ip(match):
                results.append(['ip', match])

        # MAC地址匹配
        mac_matches = re.findall(mac_pattern, content)
        for match in mac_matches:
            if is_valid_mac(match):
                results.append(['mac', match])
        
    
    
    with open('output.csv', 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow(['category', 'value'])
        writer.writerows(results)

extract_and_save('data.txt')

2025 能源网络安全大赛 数据脱敏

为了抵抗黑客攻击导致数据拖库等问题，需要将敏感数据识别并脱敏存储成⼀个表。给定脱敏算法逻辑，要求选⼿⽣成脱敏后的数据表数据（所有数据均为随机⽣成，与现实世界⽆任何联系）。为了防⽌⼀些隐私数据泄漏，现需要对该数据表进⾏脱敏操作，请按照指定要求对各字段进⾏脱敏处理，并按照先行后列拼接字符串（不包含标题行），对此字符串进行md5计算，得到答案。

脱敏要求：

编号：⽆需脱敏。
姓名：⼆字姓名对最后⼀位字使⽤ * 进⾏替换，三字姓名对中间⼀位字使⽤ * 进⾏替换，四字姓名对中间两位字使⽤ * 进⾏替换。
手机号：请对中间五位信息使⽤ * 进⾏替换。
身份证号码：请对除了前6位信息使⽤ * 进⾏替换。
银⾏卡：请对前四位和后十位信息使⽤ * 进⾏替换。
Email：请对字符 @ 前除 . 外的字符使⽤ * 进⾏替换。
性别：替换成未知。
微信号：请对为字符的信息使用 * 进行替换。

import pandas as pd
import string
import hashlib

df_dict = pd.read_excel("data.xlsx", sheet_name=["Sheet1"], header=0)
df_sheet1 = df_dict['Sheet1']

# 数据提取
id = df_sheet1['编号']
name = df_sheet1['姓名']
phone = df_sheet1['手机号']
idcard = df_sheet1['身份证号']
bankcard = df_sheet1['银行卡号']
email = df_sheet1['Email']
gender = df_sheet1['性别']
wechat = df_sheet1['微信号']

# 编号无需脱敏

# 姓名：⼆字姓名对最后⼀位字使⽤ * 进⾏替换，三字姓名对中间⼀位字使⽤ * 进⾏替换，四字姓名对中间两位字使⽤ * 进⾏替换。
def name_desensitization(current_name):
    n = len(current_name)

    if n == 2:
        desensitized_name = current_name[0] + '*'
    elif n == 3:
        desensitized_name = current_name[0] + '*' + current_name[2]
    elif n == 4:
        desensitized_name = current_name[0] + '**' + current_name[3]
    else:
        desensitized_name = current_name

    return desensitized_name

# 手机号：请对中间五位信息使⽤ * 进⾏替换。
def phone_desensitization(current_phone):
    current_phone = str(current_phone)
    desensitized_phone = current_phone[0:3] + '*'*5 + current_phone[8:]
    return desensitized_phone

# 身份证号码：请对除了前6位信息使⽤ * 进⾏替换。
def idcard_desensitization(current_idcard):
    current_idcard = str(current_idcard)
    desensitized_idcard = current_idcard[0:6] + '*'*12
    return desensitized_idcard

# 银⾏卡：请对前四位和后十位信息使⽤ * 进⾏替换。
def bankcard_desensitization(current_bankcard):
    current_bankcard = str(current_bankcard)
    desensitized_bankcard = current_bankcard[:4] + '*'*(len(current_bankcard)-14) + current_bankcard[-10:]
    return desensitized_bankcard

# Email：请对字符 @ 前除 . 外的字符使⽤ * 进⾏替换。
def email_desensitization(current_email):
    desensitized_email = ''
    for i in range(len(current_email)):
        if current_email[i] == '@':
            desensitized_email += current_email[i:]
            return desensitized_email
        else:
            if current_email[i] != '.':
                desensitized_email += '*'
            else:
                desensitized_email += current_email[i]



# 性别：替换成未知。
def gender_desensitization(current_gender):
    desensitized_gender = '未知'
    return desensitized_gender

# 微信号：请对为字符的信息使用 * 进行替换。
chars = string.ascii_lowercase + string.ascii_uppercase + string.digits + '_'
def wechat_desensitization(current_wechat):
    desensitized_wechat = ''
    for i in range(len(current_wechat)):
        if current_wechat[i] in chars:
            desensitized_wechat += '*'
        else:
            desensitized_wechat += current_wechat[i]

    return desensitized_wechat
        

# 数据脱敏
name_desensitized = name.apply(name_desensitization)
phone_desensitized = phone.apply(phone_desensitization)
idcard_desensitized = idcard.apply(idcard_desensitization)
bankcard_desensitized = bankcard.apply(bankcard_desensitization)
email_desensitized = email.apply(email_desensitization)
gender_desensitized = gender.apply(gender_desensitization)
wechat_desensitized = wechat.apply(wechat_desensitization)

with open('output.txt', 'w') as f:
    for data_index in range(len(id)):
        f.write(name_desensitized[data_index]+phone_desensitized[data_index]+idcard_desensitized[data_index]+bankcard_desensitized[data_index]+email_desensitized[data_index]+gender_desensitized[data_index]+wechat_desensitized[data_index])

with open('output.txt', 'r') as ff:
    # 计算文件md5    
    md5 = hashlib.md5(ff.read().encode('UTF-8')).hexdigest()
    print(md5)

2025 红明谷杯

题目内容：某企业网络安全部门人员正在对企业网络资产受到的攻击行为进行溯源分析，该工作人员发现攻击者删除了一段时间内的访问日志数据，但是攻击者曾传输过已被删除的访问日志数据并且被流量监控设备捕获，工作人员对流量数据进行了初步过滤并提取出了相应数据包。已知该攻击者在开始时曾尝试低密度的攻击，发现未被相关安全人员及时发现后进行了连续多日的攻击，请协助企业排查并定位攻击者IP，flag格式为：flag{md5(IP)}

先用tshark导出data.data字段的base64数据，默认输出的是hex的数据：

tshark -r network_traffic.pcap -T fields -e data.data tcp > 1.txt

先解hex，再解base64：

import base64
import json
 
with open('1.txt', 'r', encoding='utf-8') as f, open('2.txt', 'w', encoding='utf-8') as ff:
    lines = f.readlines()
    for line in lines:
        line = line.strip()
        try:
            base64_data = bytes.fromhex(line).decode()
            data = base64.b64decode(base64_data).decode()
            json_data = json.loads(data)
            if 'msg' in json_data:
                base64_msg = json_data['msg']
                log = base64.b64decode(base64_msg).decode()
                ff.write(log + '\n')
        except:
            print("decode error")

日志形式：

90.97.242.28- - [08/Jan/2025:22:57:37 +0000] "POST /main/search.php HTTP/1.1" 200 2380 "-" "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.1)"

import re
from collections import Counter

pattern = r'\d+\.\d+\.\d+\.\d+(?=-| )'

with open('2.txt', 'r', encoding='latin') as f:
    ip = re.findall(pattern, f.read())
    
    # 统计ip出现频率
    ip_counts = Counter(ip)

    # 出现最多次数的ip
    badip, counts = ip_counts.most_common(1)[0]
    print(badip, counts)

输出结果：

35.127.46.111 67