/** * 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);