3v4l.org

run code in 150+ php & hhvm versions
Bugs & Features
<?php /** * This is a simple sql tokenizer / parser. * * It does NOT support multiline comments at this time. * * See the included example.php for usage. * * THIS CODE IS A PROTOTYPE/BETA * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @version 0.0.4 */ class SqlParser { var $handle = null; // statements public static $querysections = array('alter','create','drop', 'select', 'delete', 'insert', 'update', 'from', 'where', 'limit', 'order'); // operators public static $operators = array('=', '<>', '<', '<=', '>', '>=', 'like', 'clike', 'slike', 'not', 'is', 'in', 'between'); // types public static $types = array('character', 'char', 'varchar', 'nchar', 'bit', 'numeric', 'decimal', 'dec', 'integer', 'int', 'smallint', 'float', 'real', 'double', 'date', 'datetime', 'time', 'timestamp', 'interval', 'bool', 'boolean', 'set', 'enum', 'text'); // conjuctions public static $conjuctions = array('by', 'as', 'on', 'into', 'from', 'where', 'with'); // basic functions public static $funcitons = array('avg', 'count', 'max', 'min', 'sum', 'nextval', 'currval', 'concat', ); // reserved keywords public static $reserved = array('absolute', 'action', 'add', 'all', 'allocate', 'and', 'any', 'are', 'asc', 'ascending', 'assertion', 'at', 'authorization', 'begin', 'bit_length', 'both', 'cascade', 'cascaded', 'case', 'cast', 'catalog', 'char_length', 'character_length', 'check', 'close', 'coalesce', 'collate', 'collation', 'column', 'commit', 'connect', 'connection', 'constraint', 'constraints', 'continue', 'convert', 'corresponding', 'cross', 'current', 'current_date', 'current_time', 'current_timestamp', 'current_user', 'cursor', 'day', 'deallocate', 'declare', 'default', 'deferrable', 'deferred', 'desc', 'descending', 'describe', 'descriptor', 'diagnostics', 'disconnect', 'distinct', 'domain', 'else', 'end', 'end-exec', 'escape', 'except', 'exception', 'exec', 'execute', 'exists', 'external', 'extract', 'false', 'fetch', 'first', 'for', 'foreign', 'found', 'full', 'get', 'global', 'go', 'goto', 'grant', 'group', 'having', 'hour', 'identity', 'immediate', 'indicator', 'initially', 'inner', 'input', 'insensitive', 'intersect', 'isolation', 'join', 'key', 'language', 'last', 'leading', 'left', 'level', 'limit', 'local', 'lower', 'match', 'minute', 'module', 'month', 'names', 'national', 'natural', 'next', 'no', 'null', 'nullif', 'octet_length', 'of', 'only', 'open', 'option', 'or', 'order', 'outer', 'output', 'overlaps', 'pad', 'partial', 'position', 'precision', 'prepare', 'preserve', 'primary', 'prior', 'privileges', 'procedure', 'public', 'read', 'references', 'relative', 'restrict', 'revoke', 'right', 'rollback', 'rows', 'schema', 'scroll', 'second', 'section', 'session', 'session_user', 'size', 'some', 'space', 'sql', 'sqlcode', 'sqlerror', 'sqlstate', 'substring', 'system_user', 'table', 'temporary', 'then', 'timezone_hour', 'timezone_minute', 'to', 'trailing', 'transaction', 'translate', 'translation', 'trim', 'true', 'union', 'unique', 'unknown', 'upper', 'usage', 'user', 'using', 'value', 'values', 'varying', 'view', 'when', 'whenever', 'work', 'write', 'year', 'zone', 'eoc'); // open parens, tokens, and brackets public static $startparens = array('{', '('); public static $endparens = array('}', ')'); public static $tokens = array(',', ' '); private $query = ''; // constructor (placeholder only) public function __construct() { } /** * Simple SQL Tokenizer * * @author Justin Carlson <justin.carlson@gmail.com> * @license GPL * @param string $sqlQuery * @return token array */ public static function Tokenize($sqlQuery, $cleanWhitespace = true) { /** * Strip extra whitespace from the query */ if ($cleanWhitespace === true) { $sqlQuery = ltrim(preg_replace('/[\\s]{2,}/', ' ', $sqlQuery)); } /** * Regular expression parsing. * Inspired/Based on the Perl SQL::Tokenizer by Igor Sutton Lopes */ // begin group $regex = '('; // inline comments $regex .= '(?:--|\\#)[\\ \\t\\S]*'; // logical operators $regex .= '|(?:<>|<=>|>=|<=|==|=|!=|!|<<|>>|<|>|\\|\\||\\||&&|&|-'; $regex .= '|\\+|\\*(?!\/)|\/(?!\\*)|\\%|~|\\^|\\?)'; // empty quotes $regex .= '|[\\[\\]\\(\\),;`]|\\\'\\\'(?!\\\')|\\"\\"(?!\\"")'; // string quotes $regex .= '|".*?(?:(?:""){1,}"'; $regex .= '|(?<!["\\\\])"(?!")|\\\\"{2})'; $regex .= '|\'.*?(?:(?:\'\'){1,}\''; $regex .= '|(?<![\'\\\\])\'(?!\')'; $regex .= '|\\\\\'{2})'; // c comments $regex .= '|\/\\*[\\ \\t\\n\\S]*?\\*\/'; // wordds, column strings, params $regex .= '|(?:[\\w:@]+(?:\\.(?:\\w+|\\*)?)*)'; $regex .= '|[\t\ ]+'; // period and whitespace $regex .= '|[\.]'; $regex .= '|[\s]'; $regex .= ')'; # end group // perform a global match preg_match_all('/' . $regex . '/smx', $sqlQuery, $result); // return tokens return $result[0]; } /** * Simple SQL Parser * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @param string $sqlQuery * @param bool optional $cleanup * @return SqlParser Object */ public static function ParseString($sqlQuery, $cleanWhitespace = true) { // instantiate if called statically if (!isset($this)) { $handle = new SqlParser(); } else { $handle = $this; } // copy and tokenize the query $tokens = self::Tokenize($sqlQuery, $cleanWhitespace); $tokenCount = count($tokens); $queryParts = array(); if (isset($tokens[0])===true) { $section = $tokens[0]; } // parse the tokens for ($t = 0; $t < $tokenCount; $t++) { // if is paren if (in_array($tokens[$t], self::$startparens)) { // read until closed $sub = $handle->readsub($tokens, $t); $handle->query[$section].= $sub; } else { if (in_array(strtolower($tokens[$t]), self::$querysections) && !isset($handle->query[$tokens[$t]])) { $section = strtolower($tokens[$t]); } // rebuild the query in sections if (!isset($handle->query[$section])) $handle->query[$section] = ''; $handle->query[$section] .= $tokens[$t]; } } return $handle; } /** * Parses a sub-section of a query * * @param array $tokens * @param int $position * @return string section */ private function readsub($tokens, &$position) { $sub = $tokens[$position]; $tokenCount = count($tokens); $position++; while (!in_array($tokens[$position], self::$endparens) && $position < $tokenCount) { if (in_array($tokens[$position], self::$startparens)) { $sub.= $this->readsub($tokens, $position); $subs++; } else { $sub.= $tokens[$position]; } $position++; } $sub.= $tokens[$position]; return $sub; } /** * Returns manipulated sql to get the number of rows in the query. * Can be used for simple pagination, for example. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getCountQuery($optName = 'count') { // create temp copy of query $temp = $this->query; // create count() version of select and unset any limit statement $temp['select'] = 'select count(*) as `'.$optName.'` '; if (isset($temp['limit'])) { unset($temp['limit']); } return implode(null, $temp); } /** * Returns manipulated sql to get the unlimited number of rows in the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getLimitedCountQuery() { $this->query['select'] = 'select count(*) as `count` '; return implode('', $this->query); } /** * Returns the select section of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getSelectStatement() { return $this->query['select']; } /** * Returns the from section of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getFromStatement() { return $this->query['from']; } /** * Returns the where section of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getWhereStatement() { return $this->query['where']; } /** * Returns the limit section of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getLimitStatement() { return $this->query['limit']; } /** * Returns the specified section of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function get($which) { if (!isset($this->query[$which])) return false; return $this->query[$which]; } /** * Returns the sections of the query. * * @author Justin Carlson <justin.carlson@gmail.com> * @license LGPL 3 * @return string sql */ public function getArray() { return $this->query; } } /** * Note: The closing tag of a PHP block at the end of a file is optional, * and in some cases omitting it is helpful when using include() or require(), * so unwanted whitespace will not occur at the end of files */ $totalCountSql = SqlParser::ParseString( 'SELECT name, rew from asdf' ); var_dump($totalCountSql);
Output for 7.1.0
Warning: Illegal string offset 'select' in /in/04Bkg on line 218 Warning: Cannot assign an empty string to a string offset in /in/04Bkg on line 218 Warning: Illegal string offset 'select' in /in/04Bkg on line 219 Fatal error: Uncaught Error: Cannot use assign-op operators with string offsets in /in/04Bkg:219 Stack trace: #0 /in/04Bkg(372): SqlParser::ParseString('SELECT name, re...') #1 {main} thrown in /in/04Bkg on line 219
Process exited with code 255.
Output for 5.3.0 - 5.6.28, hhvm-3.10.0 - 3.12.0, 7.0.0 - 7.0.14
object(SqlParser)#1 (2) { ["handle"]=> NULL ["query":"SqlParser":private]=> array(2) { ["select"]=> string(17) "SELECT name, rew " ["from"]=> string(9) "from asdf" } }
Output for 5.1.3 - 5.2.17
object(SqlParser)#1 (2) { ["handle"]=> NULL ["query:private"]=> array(2) { ["select"]=> string(17) "SELECT name, rew " ["from"]=> string(9) "from asdf" } }
Output for 5.0.0 - 5.1.2
Strict Standards: var: Deprecated. Please use the public/private/protected modifiers in /in/04Bkg on line 18 object(SqlParser)#1 (2) { ["handle"]=> NULL ["query:private"]=> array(2) { ["select"]=> string(17) "SELECT name, rew " ["from"]=> string(9) "from asdf" } }
Output for 4.4.2 - 4.4.9
Parse error: syntax error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /in/04Bkg on line 21
Process exited with code 255.
Output for 4.3.0 - 4.3.1, 4.3.5 - 4.4.1
Parse error: parse error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /in/04Bkg on line 21
Process exited with code 255.
Output for 4.3.2 - 4.3.4
Parse error: parse error, expecting `T_OLD_FUNCTION' or `T_FUNCTION' or `T_VAR' or `'}'' in /in/04Bkg on line 21
Process exited with code 255.