/**
* Parse SQL query WHERE and ORDER BY clauses and validate that nothing bad is happening there
* @param string $where
* @param string $order_by
* @return bool
*/
public function validateQueryClauses($where, $order_by = '')
{
if (empty($where) && empty($order_by)) {
return true;
}
if (empty($where) && !empty($order_by)) {
$where = "deleted=0";
}
$parser = new PHPSQLParser\PHPSQLParser();
$testquery = "SELECT dummy FROM dummytable WHERE {$where}";
$clauses = 3;
if (!empty($order_by)) {
$testquery .= " ORDER BY {$order_by}";
$clauses++;
}
$parsed = $parser->parse($testquery);
if (count($parsed) != $clauses) {
// we assume: SELECT, FROM, WHERE, maybe ORDER
return false;
}
$parts = array_keys($parsed);
if ($parts[0] != "SELECT" || $parts[1] != "FROM" || $parts[2] != "WHERE") {
// check the keys to be SELECT, FROM, WHERE
return false;
}
if (!empty($order_by) && $parts[3] != "ORDER") {
// extra key is ORDER
return false;
}
// verify SELECT didn't change
if (count($parsed["SELECT"]) != 1 || $parsed["SELECT"][0] !== array('expr_type' => 'colref', 'alias' => '`dummy`', 'base_expr' => 'dummy', 'sub_tree' => false)) {
Log::debug("validation failed SELECT");
return false;
}
// verify FROM didn't change
if (count($parsed["FROM"]) != 1 || $parsed["FROM"][0] !== array('table' => 'dummytable', 'alias' => 'dummytable', 'join_type' => 'JOIN', 'ref_type' => '', 'ref_clause' => '', 'base_expr' => false, 'sub_tree' => false)) {
Log::debug("validation failed FROM");
return false;
}
// check WHERE
if (!$this->validateExpression($parsed["WHERE"], true)) {
Log::debug("validation failed WHERE");
return false;
}
// check ORDER
if (!empty($order_by) && !$this->validateExpression($parsed["ORDER"])) {
Log::debug("validation failed ORDER");
return false;
}
return true;
}
/**
* @REST\Get("/query/analyze")
*/
public function analyzeQueryTest(Request $request)
{
$query = $request->query->get('query');
$parser = new \PHPSQLParser\PHPSQLParser();
$result = $parser->parse($query);
if (false) {
return new Response(print_r($result, true), 200);
ob_start();
var_dump($result);
$dump = ob_get_clean();
return new Response($dump, 200);
} else {
$parser = new \PHPSQLParser\PHPSQLParser();
$creator = new \PHPSQLParser\PHPSQLCreator();
$tree = $parser->parse($query);
$column = 'foo';
$direction = 'asc';
$tree['ORDER'] = array(array('expr_type' => 'colref', 'base_expr' => $column, 'no_quotes' => array('delim' => false, 'parts' => array($column)), 'sub_tree' => false, 'direction' => strtoupper($direction)));
return array('query' => $creator->create($tree));
}
}
#!/usr/bin/php
<?php
$config = ['git_urls' => ['https://github.com/yfix/php-sql-parser.git' => 'php_sql_parser/'], 'autoload_config' => ['php_sql_parser/src/PHPSQLParser/' => 'PHPSQLParser'], 'example' => function () {
$parser = new \PHPSQLParser\PHPSQLParser();
$sql = '
`id` int(6) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT \'\',
`active` enum(\'0\',\'1\') NOT NULL DEFAULT \'0\',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
';
$parsed = $parser->parse($sql);
var_export($parsed);
}];
if ($return_config) {
return $config;
}
require_once __DIR__ . '/_yf_autoloader.php';
new yf_autoloader($config);
