Automating SQL injection analysis with PHP, sqlmap, Gearman

If you’re in web development area, you certainly know about SQL injection attack. There is also a well-known joke about it on xkcd:

There is a tool for automating SQL injection discovery, called sqlmap, you can find it on github

All info you can find in this article is ONLY for educational purposes. Neither LAMPDev nor the author of this article are responsible for any damage caused by 3rd party to any site using techniques explained in this article!

Now assume you have a list of URLs to check against various SQL injection attacks. Something like:

https://example1.com/param.php?id=1
https://example2.com/news.php?id=23
.....
https://example100.com/param.php?id=100

If you run sqlmap against every URL manually, it till take ages. So can we automate it? In order to do so, we will need a machine (ideally Linux based, but I did it all on Win7+Virtualbox ubuntu image) with the following software installed:

[list style=“upper-alpha tick“]
[list_item]PHP5 + Gearman client + PDO_MySQL driver[/list_item]
[list_item]MySQL[/list_item]
[list_item]Gearman server[/list_item]
[list_item]python (sqlmap is written on it)[/list_item]
[list_item]sqlmap[/list_item]
[/list]

Optionally,

[list style=“upper-alpha tick“]
[list_item]supervisord if you need to launch certain number of Gearman workers and respawn them automatically[/list_item]
[list_item]git, if you want to clone from github sqlmap project instead of downloading and unpacking .zip/.tar.gz[/list_item]
[/list]

Let’s get started with PHP, MySQL and importing our initial URL list. I won’t explain PHP and MySQL installation, let’s assume they are both installed.

Below is database tables structure for URL storage we’re going to audit.

CREATE TABLE `sites` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(1024) NOT NULL,
  `domain` VARCHAR(128) NOT NULL,
  `country_id` INT(11) NOT NULL,
  `output` LONGTEXT,
  `is_vulnerable` TINYINT(1) DEFAULT '0',
  `payload` VARCHAR(8000) DEFAULT NULL,
  `is_processing` TINYINT(1) DEFAULT '0',
  `processed` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE `countries` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(16) NOT NULL,
  `fullname` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

You can download this structure .sql file + sample data here.

Now we need to install sqlmap itself. You can do it by cloning sqlmap repo onto your local machine:

Install python. Under *nix just use your package manager like apt in Debian/Ubuntu:

apt-get install python

Or if you’re running Windows, there is win32 version of it. I think we need 2.7 branch, I remember having issues with 3rd python and sqlmap. Download it here. Once you’re done you may launch sqlmap against an URL to test whether it works.

sqlmap.py -u https://example1.com/page.php?id=1

If it starts checking it, you’re done with the steps above.

Knowing that sqlmap has ‐‐batch option, we’re now theoretically ready to launch it against the whole table of the URLs we created above. So in PHP we will loop through `processed` = 0 rows and launch sqlmap with every row’s URL and update `output` and `payload` columns with sqlmap’s output. This is possible to do with popen()/pclose() or proc_open()/proc_terminate()/proc_close(). BUT what if we want to launch this task asynchronously for every URL? So we don’t want to wait until sqlmap finishes working with one row to go to the next one, but we want multithreading of this task , so that it works on 10-20 rows at a moment and so we want to create a queue. This is where Gearman goes into action.

Gearman is a daemon that listens to a port and accepts tasks from clients. On the other hand there are Gearman worker scripts launched and waiting for tasks to arrive. So in our case:

[list style=“upper-alpha tick“]
[list_item]Gearman client – a PHP script that gets ALL processed = 0 rows and in a loop through all of them sends a task to Gearman server[/list_item]
[list_item]Task is a row from `sites` DB table[/list_item]
[list_item]Worker script is a script that waits for task (a row from `sites`) and actually launches sqlmap, gets its output and saves to the `sites` table[/list_item]
[/list]

One of the killer features of Gearman is that workers and clients can be written in different programming languages. So it is a software + protocol + client libraries for many programming languages. The protocol is unencrypted telnet-like one, so you can debug it by connecting to gearmand server and sending commands to it from your shell. But in this example we will code both client and workers in PHP.

So, install gearman server. If you’re under *nix and using apt package manager, just launch:

apt-get install gearman gearman-job-server libevent1-dev

If you’re under windows you have two options: either try to compile it in cygwin or install a Java-coded server from here. Note that in case of cygwin you will need libevent1-devel installed (there is a cygwin package). Also note that I tried to compile it this way, but I got troubles with -L and -I options of c++ compiler. For example, it could not find libtest/yatlcon.h header, however, it was there. I read in Gearman mailing list that its developers haven’t yet created a configure for cygwin.

After we’re done with Gearman server, we need to install PHP extension. It can be done with PECL installer, basically

pecl install gearman

You will need make, autoconf, gcc, libgearman and, I think, php headers of your PHP version installed to compile this PECL extension.

Let’s see how a worker script may look like. I will put comments before certain lines:

// put PDO DB connection in this file. I won't publish it here, but basically something like $db = new PDO('mysql:host=host;port=port','user','password')
require_once('include.php');
 
// path to your sqlmap.py
define('SQLMAP_PATH','/home/ubuntu/sqlmap/sqlmap.py');
// sqlmap options. --batch is a good idea
define('SQLMAP_OPTIONS','--random-agent --threads=5 --timeout=5 --batch 2>&1');
 
$worker= new GearmanWorker();
 
// add the default server (localhost)
$worker->addServer();
 
//add the "sqlmap" -> "run" function. So a client triggers "sqlmap", worker runs local "run"
$worker->addFunction("sqlmap", "run");
 
// start the worker, infinite loop
while ($worker->work());
 
// the "run" function itself
function run($job)
{
    // this is just for debugging purposes
    echo "Got task ID: " . $job->workload() . PHP_EOL;
    // get $db connection from outer scope
    global $db;
 
    // descriptors of STDIN, STDOUT, STDERR for proc_open()
    $descriptorspec = array(
        0 => array("pipe", "r"),  // stdin is a pipe that the child will read from
        1 => array("pipe", "w"),  // stdout is a pipe that the child will write to
        2 => array("file", "/home/ubuntu/cse/worker_log.txt", "a") // stderr is a file to write to
    );
 
    $stmt = $db->prepare('SELECT * FROM `sites` WHERE `id` = ?');
    // $job->workload() returns the string parameter that was given to this worker from the daemon, which in turn was triggered by the client
    $res = $stmt->execute(array($job->workload()));
    if(($row = $stmt->fetch(PDO::FETCH_OBJ)) !== false) {
        // set `is_processing` flag, just for debug. 
        $stmt = $db->prepare('UPDATE `sites` SET `is_processing` = 1 WHERE `id` = ?');
        $stmt->execute(array($row->id));
        $id = $row->id;
        // launch the sqlmap with the options. $pipes - is the streams to read STDOUT , STDIN, STDERR from
        $proc = proc_open(SQLMAP_PATH . ' -u ' . escapeshellarg($row->url) . ' ' . SQLMAP_OPTIONS,$descriptorspec, $pipes);
        // while STDOUT 
        while(!feof($pipes[1])) {
            // read 1 Kb
            $contents = fread($pipes[1], 1024);
            // update `output` in the database with it
            $stmt = $db->prepare('UPDATE `sites` SET `output` = CONCAT(IF(`output` IS NULL,"",`output`),?) WHERE `id` = ?');
            $stmt->execute(array($contents,$row->id));
            // this is a dirty hack to stop sqlmap if it hits a dynamic page and tries to infinitely check it with UNION technique with different number of columns. Or if tere were 5 connection failures, we should give up
            $stmt = $db->prepare('SELECT (LENGTH( output) - LENGTH(REPLACE(output, ?, "")))/LENGTH(?) AS connect_failures,
                                    (LENGTH( output) - LENGTH(REPLACE(output, ?, "")))/LENGTH(?) AS union_attempts,
                                    (LENGTH( output) - LENGTH(REPLACE(output, ?, "")))/LENGTH(?) AS dropped_attempts
                                    FROM `sites`
                                    WHERE `id` = ?');
            $stmt->execute(array('to the target URL or proxy','to the target URL or proxy','MySQL UNION query (random number) -','MySQL UNION query (random number) -','connection dropped or unknown HTTP status code received','connection dropped or unknown HTTP status code received',$row->id));
            $failures = $stmt->fetch(PDO::FETCH_OBJ);
            if($failures->connect_failures > 5 || $failures->union_attempts > 5 || $failures->dropped_attempts > 5) {
                // give up , kill the process of sqlmap if there were many failures.
                proc_terminate ($proc);
                break;
            }
            // if we find "sqlmap identified the following injection" substring, the site is vulerable
            if(strpos($contents, 'sqlmap identified the following injection') !== false) {
                $matches = array();
                preg_match_all('/\-\-\-([\s\S]*?)\-\-\-/im',$contents,$matches);
                // so save the payload and mark it as vulnerable
                $stmt = $db->prepare('UPDATE `sites` SET `is_vulnerable` = 1, `payload` = ? WHERE `id` = ?');
                $stmt->execute(array($matches[1][0],$row->id));
            }
        }
        // mark it at processed 
        $stmt = $db->prepare('UPDATE `sites` SET `is_processing` = 0, `processed` = 1 WHERE `id` = ?');
        $res = $stmt->execute(array($row->id));
 
        // and if we haven't terminated the process till now because of failures, terminate it now.
        if(is_resource($proc)) {
            proc_terminate($proc);
        }
 
        return true;
    } else {
         return false;
    }
}

The client script is much easier:

// same DB connection stuff as with worker script
require_once('include.php');
 
// select all unprocessed and not processing at the moment records
$stmt = $db->prepare('SELECT * FROM `sites` WHERE `is_processing` = 0 AND `processed` = 0 ORDER BY `id` DESC');
$res = $stmt->execute();
 
// instantiate Gearman client
$gmc= new GearmanClient();
 
// add localhost server. If there is no address, it assumes localhost
$gmc->addServer();
 
while(($row = $stmt->fetch(PDO::FETCH_OBJ)) !== false) {
    //send the task for every row to the server
    $task = $gmc->addTask("sqlmap", $row->id);
}
 
 
// debug info
if (! $gmc->runTasks())
{
    echo "ERROR " . $gmc->error() . "\n";
    exit;
}

Now we need to launch a few workers of Gearman. We obviously want to run and support running a few of them, like 10 for example and we want to respawn them automatically if one or few are terminated for some reason. This can be done with supervisor daemon.

Peraonally I configured it like this guy replied. So:

apt-get install python-setuptools
easy_install supervisor
echo_supervisord_conf > /etc/supervisord.conf

init.d script is here.

Once copied, you may want to add it to autostart scripts:

chmod +x /etc/init.d/supervisord
update-rc.d -f supervisord defaults

I just had to add this option, as I have > 1 workers.

process_name=%(program_name)s_%(process_num)02d

So the config entry of supervisor would look like:

[program:gearman]
command=/usr/bin/php /home/ubuntu/cse/worker.php
numprocs=10
process_name=%(program_name)s_%(process_num)02d
directory=/home/ubuntu/cse
stdout_logfile=/home/ubuntu/cse/supervisord.log
environment=GEARMAN_USER=gearman
autostart=true
autorestart=true
user=gearman
stopsignal=KILL

Now we are done, start supervisor:

 
/etc/init.d/supervisord start

You may validate that there are PHP workers launched by:

ps aux|grep php

Now just run the client script:

php client.php

and check if it has started by looking at database `sites` table.