根据基站位置区识别码(LCA)和小区识别(CI)获取经纬度

需求

在网络攻击溯源时，需要对攻击者的位置进行定位。

已知参数

已知攻击者发送攻击报文的接入基站的位置区识别码(LCA)和小区识别(CI)码

目标

获取攻击者位置

技术路线

• request 调用API查询经纬度位置
• openpyxl 读取 excel 表格
• sqlite3 读写数据库
• json 数据解析

Python 代码

import requests
from openpyxl import load_workbook
import re
import os
import json
import sqlite3

# 传入一个列表，里面包含字典数据。
def writeDB(conn, cursor, lac, ci, mnc, real_data):
    errcode  = real_data['errcode']
    lat = real_data['lat']
    lon = real_data['lon']
    radius = real_data['radius']
    address = real_data['address']
    
    cursor.execute("INSERT INTO lac_ci_cache(errcode, mnc, lac, ci, lat, lon, radius, address) VALUES (?,?,?,?,?,?,?,?)",(errcode, mnc, lac, ci, lat, lon, radius, address))
    conn.commit()
    

def queryCache(cursor, lac, ci):
    cursor.execute("SELECT * FROM lac_ci_cache WHERE lac = ? AND ci = ?",(lac, ci))
    results = cursor.fetchall()
    
    # 获取查询结果的列名
    column_names = [desc[0] for desc in cursor.description]
    
    # 将查询结果转换为字典列表
    result_dict_list = []
    
    for row in results:
        row_dict = dict(zip(column_names, row))
        result_dict_list.append(row_dict)
        
    print(result_dict_list)
        
    return result_dict_list

def writeData(sheet, row, operator_code, content_str, status):
    
    print('content_str:' + str(content_str))
    print(type(content_str))
    dict_data = ["lat","lon","radius","address"]
    # 正常写入逻辑
    if(status == 0):
        real_data = json.loads(content_str)
        for number in range(4, 4 + len(dict_data)):
            sheet.cell(row = row, column = number, value = real_data[dict_data[number-4]])
        
        # 8 运营商
        operator_str = ""
        if(operator_code == 0):
            operator_str = "移动"
        elif(operator_code == 1):
            operator_str = "联通"
        else:
            operator_str = "未知"
        
        sheet.cell(row = row, column = 4 + len(dict_data), value = '移动')
    # 异常写入逻辑
    else:
        for number in range(4, 4 + len(dict_data) + 1):
            sheet.cell(row = row, column = number, value = 'N/A')
        

    
def saveData(wb, file_name):
    wb.save(file_name)
    wb.close()
        
# return sheet
def getSheet(file_name, sheet_name):
    # 获取当前目录
    current_directory = os.getcwd()
    # subdirectory = "xxx"
    # current_directory = os.path.join(current_directory, subdirectory)
    print(current_directory)
    # 读取Excel文件
    excel_file_path = file_name  # 替换为你的Excel文件路径
    wb = load_workbook(filename=excel_file_path)

    # 选择工作表
    sheet = wb[sheet_name]  # 替换为你的工作表名称
    return sheet,wb



# return json text of lac ci
def getLocation(mnc, lac, ci, output):

    # 访问地址
    url = "http://api.cellocation.com:84/cell/?mcc=460&mnc={}&lac={}&ci={}&output={}".format(mnc, lac, ci, output) 
    
    # 打印URL
    print(url)
    
    # 定义要设置的Cookie
    
    # 发送HTTP GET请求获取页面内容
    response = requests.get(url)
    
    if response.status_code == 200:
        # 使用response.text获取页面的文本内容
        page_content = response.text
        
        return page_content;
    else:
        return 'none'
    

if __name__ == "__main__":
    
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('data.db')
    
    # 创建一个游标对象，用于执行 SQL 语句
    cursor = conn.cursor()
    
    # 创建表格
    cursor.execute('''CREATE TABLE IF NOT EXISTS lac_ci_cache (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        errcode INTEGER,
                        mnc INTEGER,
                        lac INTEGER,
                        ci INTEGER,
                        lat REAL,
                        lon REAL,
                        radius REAL,
                        address TEXT
                    )''')                   
                    
    # 1 获取表单信息openpyxl
    sheet, wb= getSheet('lacci_test.xlsx','lacci_test')
    
    # 添加标题
    dict_data = ["纬度","精度","半径","地址","运营商"]
    for number in range(4, 4+len(dict_data)): 
        sheet.cell(row = 1, column = number, value = dict_data[number-4])
        
    # 2 遍历查询
    row_index = 1;
    for row in sheet.iter_rows(min_row=2, max_row=201, min_col=1, max_col=2):
        
        row_index = row_index + 1;
        print('row_index' + str(row_index))
        # 获取单元格的值
        lac_value = row[0].value
        ci_value = row[1].value
        
        # 查询数据库是否已经存在
        cache_value = queryCache(cursor, lac_value, ci_value)
        
        # 数据库有直接填充
        if(len(cache_value)!=0):
            content_str = cache_value[0]
            operator_code = content_str['mnc']
            status = content_str['errcode']
            content_str = str(json.dumps(content_str))
            writeData(sheet, row_index, operator_code, content_str, status)
            continue;
            
        # 目前缓存的数据库不存在的情况 请求API 返回json字符串
        content_str = getLocation(0, lac_value, ci_value, 'json')
        real_data = json.loads(content_str)
        
        # 如果是移动的就写入
        if(real_data['errcode'] == 0):
            operator_code = 0;
            status = 0;
            writeData(sheet, row_index, operator_code, content_str, status)
            writeDB(conn, cursor, lac_value, ci_value, operator_code, real_data)
            continue;
        
        # 移动没有查到，换成联通的再调用API
        # 如果是联通的就写入
        content_str = getLocation(1, lac_value, ci_value, 'json')
        real_data = json.loads(content_str)
        if(real_data['errcode'] == 0):
            operator_code = 1;
            status = 0;
            writeData(sheet, row_index, operator_code, content_str, status)
            writeDB(conn, cursor, lac_value, ci_value, operator_code, real_data)
            continue;
        
        # 既不是移动也不是联通，触发异常逻辑
        operator_code = 0;
        status = 10001;
        writeData(sheet, row_index, operator_code, content_str, status)
        writeDB(conn, cursor, lac_value, ci_value, operator_code, real_data)
        
            
    saveData(wb,'lacci_test.xlsx')
    conn.close()


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
193
194