json_injection_test.php

<?php

use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Adapter\Profiler\Profiler;
use Zend\Db\Adapter\StatementContainer;

/**
 * @see http://blog.kazuhooku.com/2014/07/the-json-sql-injection-vulnerability.html
 * @see http://developers.mobage.jp/blog/2014/7/3/jsonsql-injection
 * @see http://blog.tokumaru.org/2014/07/json-sql-injectionphpjson.html
 * @see http://qiita.com/uchiko/items/490aee47362ca39d603a
 */

//error_reporting(E_ERROR);

require_once 'vendor/autoload.php';

$adapter = new Adapter([
    'driver' => 'Mysqli',
    'database' => 'vagrant',
    'username' => 'vagrant',
    'password' => 'vagrant',
]);

$profiler = new Profiler;
$adapter->setProfiler($profiler);

$sql = new Sql($adapter);

echo "************* test 1 - array to IN \n";

$where = ['id' => ['test' => 1, 'test2' => 2]];
$select = $sql->select('album')->where($where);

$profiler->profilerStart(new StatementContainer);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
$profile = $profiler->getLastProfile();
var_dump($results);
var_dump($profile);

echo "************* test 2 - nested array\n";

$where = ['id' => ['test' => ['nested' => 1], 'test2' => 2]];
$select = $sql->select('album')->where($where);

$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();

result.txt

************* test 1 - array to IN
class Zend\Db\Adapter\Driver\Mysqli\Result#23 (9) {
  protected $resource =>
  class mysqli_stmt#15 (10) {
    public $affected_rows =>
    int(-1)
    public $insert_id =>
    int(0)
    public $num_rows =>
    int(0)
    public $param_count =>
    int(0)
    public $field_count =>
    int(3)
    public $errno =>
    int(0)
    public $error =>
    string(0) ""
    public $error_list =>
    array(0) {
    }
    public $sqlstate =>
    string(5) "00000"
    public $id =>
    int(1)
  }
  protected $isBuffered =>
  bool(false)
  protected $position =>
  int(0)
  protected $numberOfRows =>
  int(-1)
  protected $currentComplete =>
  bool(false)
  protected $nextComplete =>
  bool(false)
  protected $currentData =>
  bool(false)
  protected $statementBindValues =>
  array(2) {
    'keys' =>
    NULL
    'values' =>
    array(0) {
    }
  }
  protected $generatedValue =>
  int(0)
}
array(5) {
  'sql' =>
  string(50) "SELECT `album`.* FROM `album` WHERE `id` IN (1, 2)"
  'parameters' =>
  class Zend\Db\Adapter\ParameterContainer#22 (3) {
    protected $data =>
    array(0) {
    }
    protected $positions =>
    array(0) {
    }
    protected $errata =>
    array(0) {
    }
  }
  'start' =>
  double(1404743754.2334)
  'end' =>
  double(1404743754.2336)
  'elapse' =>
  double(0.00021600723266602)
}
************* test 2 - nested array

Notice: Array to string conversion in /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/AbstractSql.php on line 108

Call Stack:
    0.0011     229248   1. {main}() /vagrant/json_injection_test.php:0
    0.2059    1454336   2. Zend\Db\Sql\Sql->prepareStatementForSqlObject() /vagrant/json_injection_test.php:42
    0.2060    1454512   3. Zend\Db\Sql\Platform\AbstractPlatform->prepareStatement() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/Sql.php:124
    0.2060    1452712   4. Zend\Db\Sql\Platform\Mysql\SelectDecorator->prepareStatement() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/Platform/AbstractPlatform.php:78
    0.2060    1452040   5. Zend\Db\Sql\Select->prepareStatement() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/Platform/Mysql/SelectDecorator.php:48
    0.2062    1455992   6. Zend\Db\Sql\Select->processWhere() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/Select.php:500
    0.2062    1456072   7. Zend\Db\Sql\AbstractSql->processExpression() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/Select.php:778
    0.2064    1459704   8. vsprintf() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Sql/AbstractSql.php:108


Fatal error: Uncaught exception 'Zend\Db\Adapter\Exception\ErrorException' with message 'Commands out of sync; you can't run this command now' in /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Mysqli/Statement.php on line 209

Zend\Db\Adapter\Exception\InvalidQueryException: Statement couldn't be produced with sql: SELECT `album`.* FROM `album` WHERE `id` IN (Array, 2) in /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Mysqli/Statement.php on line 209

Call Stack:
    0.0011     229248   1. {main}() /vagrant/json_injection_test.php:0
    0.2081    1452144   2. Zend\Db\Adapter\Driver\Mysqli\Statement->execute() /vagrant/json_injection_test.php:43
    0.2081    1452192   3. Zend\Db\Adapter\Driver\Mysqli\Statement->prepare() /vagrant/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Mysqli/Statement.php:230