Would you like to see the EXPLAIN output for all MySQL queries of any PHP application without changing the application much? Easy-peasy: compile PHP to use the mysqlnd library, install PECL/mysqlnd_uh and paste 22 lines of evil code into your auto_prepend_file .
class conn_proxy extends MysqlndUhConnection {
public function query($conn, $query, $self = false) {
if (!$self) {
$this->query($conn, "EXPLAIN " . $query, true);
if ($this->getFieldCount($conn)) {
printf("\tAuto EXPLAIN for '%s'\n", $query);
$res = $this->storeResult($conn);
$r = new MysqlndUhresult();
do {
$row = NULL;
$r->fetchInto($res, $row, 2, 1);
if (is_array($row))
printf("\t\t%s\n", implode(" ", $row));
} while (!empty($row));
$r->freeResult($res, false);
}
}
return parent::query($conn, $query);
}
}
mysqlnd_uh_set_connection_proxy(new conn_proxy());
Not being a PHP hero, I don’t have a PHP application in the cloud to demo how it works. Thus, I wrote a very basic application who serves no other purpose creating a table, inserting some rows and fetching them to demo that the auto EXPLAIN SQL insertion works with any PHP MySQL API: PDO_MySQL, mysqli, mysql. All of them.
$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$res = $mysqli->query("SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2");
printf("MySQLi has found %d results\n", $res->num_rows);
$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");
$stmt = $pdo->query("SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2");
printf("PDO has found %d results\n", $stmt->rowCount());
Auto EXPLAIN for 'SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2'
1 SIMPLE t1 ALL 3
1 SIMPLE t2 ALL 3 Using join buffer (BNL, incremental buffers)
MySQLi has found 9 results
Auto EXPLAIN for 'SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2'
1 SIMPLE t1 ALL 3
1 SIMPLE t2 ALL 3 Using join buffer (BNL, incremental buffers)
PDO has found 9 results
If you want to learn more about PECL/mysqlnd_uh, please browse the blog archive and check out the manual. Also, don’t miss the MySQL “Succeed with Plugins” webinar next week. Credits go to Mayflower OpenSource Labs for the original development of this mysqlnd plugin!
Unfortunately I am cheating a bit when writing “easy-peasy”. The basics are really easy. But… please, note that the above is bloody, fresh meat. To toy around with SQL injection that requires fetching result sets, you have to build the development version of PECL/mysqlnd_uh. Please, keep also in mind that PECL/mysqlnd_uh exposes the internal mysqlnd C library interface to the PHP user. The mysqlnd library has been written to be used by C developers. Giving PHP hackers, which are used to garbage collection and other safety belts, access to it was never planned. Inappropriate use may cause memory leaks and crashes.
Use PECL/mysqlnd_uh for debugging and prototyping mysqlnd plugins. Test well before using in mission critical environments. Generally speaking: if your proxy script works once, it should always do. Good enough for your development machine. In production environments, you will usually want to write your mysqlnd plugins in C for performance reasons anyway. Using C has the additional advantage of gaining access to all feature not just those made available by an Internet Super Hero, to have nice examples for a web seminar.
Having said all this, who is the first to identify the line that needs to be changed to make the example leak memory? Give me one line, dear reader. Just one line.
Happy hacking!