Is there a lightweight sql parser class written in PHP to do this?

Is there a lightweight sql parser class written in PHP to do this?

Before jumping in and rolling my own, I thought I'd ask in here first.

I am looking to do some elementary parsing of random SQL commands in order to:

  1. inspect the field names that are being used in 'SELECT's (including any subqueries)
  2. inspect the field names that are being used in 'JOIN's (including any subqueries)
  3. inspect the names of tables being used in the query (including any subqueries)

I have seen some SQL parser classes out there, but they are far too 'heavyweight' for the use cases described above.

Is anyone aware of a lightweight class that has atleast some of the required functionality?

Worst case scenario, if I have to write a parser, what would be the best way of writing such a parser (normally, to write a parser, I would normally resort to tools that are not available in PHP), any tips on how to write a 'rough and ready' class to do this parsing?

//rough sketch
<?php
class SqlParser()
{
    protected $sqlstr;
    protected $m_tablenames = array();
    protected $m_fieldnames = array();

    public function __construct($sql){
       $this->sqlstr = $sqlcmd;
       $this->parseString($sqlstr);
    }

    public function __destroy(){}
    public function getTableNames(){ return m_tablenames; }
    public function getFieldNames(){ return m_fieldnames; }

    private function parseString($sql)
    {
        //TODO
    }
}

?>

I would prefer the parsing to be SQL dialect agnostic (i.e. not tied to any particular SQL dialect or db specific SQL) as much as possible.

If that is not possible, then the SQL dialect I will be using is PostgreSQL.


PHP SQL Parser might be what you're looking for. It'll handle fairly complex queries, as you can see from the link. Download the code from the projects front page. The only drawback is it targets MySQL only. Adding support for PostgreSQL should be no big problem.

There's also a more basic solution for SQL parsing: PHP SQL Tokenizer, but it does not offer you anything but select/from/where/order separation: no field names, subquery extraction, or such.

marcocesarato/PHP-Light-SQL-Parser: This class can , This class can parse SQL to get query type, tables, field values, etc.. It takes an string with a SQL statements and parses it to extract its different components. sql-parser. This project contains a lightweight SQL parser written in PHP. This parser enables you to analyze SQL requests and build your own syntax helpers - very usefull for an ORM project. Why. Doctrine HQL engine : 3100 LOC (+lexer) php-sql-parser (from google code) : 1600 LOC; and this project : 500 LOC; Sample


You might give cbMySQL a try, i do not know it very well, but it might be the thing you are looking for.

ichiriac/sql-parser: a light SQL parser, Contribute to ichiriac/sql-parser development by creating an account on GitHub. This project contains a lightweight SQL parser written in PHP. And the result : <?php /** * Defines a parser that handles named parameters */ class of the Software, and to permit persons to whom the Software is furnished to do so,  This class is primarily an SQL parser, written with influences from a variety of sources (mSQL, CPAN's SQL-Statement, mySQL). It also includes a tokenizer (lexer) class and a reimplementation of the ctype extension in PHP.


I tried do this recently PHP-Light-SQL-Parser-Class this is more light that the other classes

<?php
/**
 * Light SQL Parser Class
 * @author Marco Cesarato <cesarato.developer@gmail.com>
 * @copyright Copyright (c) 2018
 * @license http://opensource.org/licenses/gpl-3.0.html GNU Public License
 * @link https://github.com/marcocesarato/PHP-Light-SQL-Parser-Class
 * @version 0.1.86
 */
class LightSQLParser {
    // Public
    public $query = '';
    // Private
    protected static $connectors = array('OR', 'AND', 'ON', 'LIMIT', 'WHERE', 'JOIN', 'GROUP', 'ORDER', 'OPTION', 'LEFT', 'INNER', 'RIGHT', 'OUTER', 'SET', 'HAVING', 'VALUES', 'SELECT', '\(', '\)');
    protected static $connectors_imploded = '';
    /**
     * Constructor
     */
    public function __construct($query = '') {
        $this->query = $query;
        if(empty(self::$connectors_imploded))
            self::$connectors_imploded = implode('|', self::$connectors);
        return $this;
    }
    /**
     * Set SQL Query string
     */
    public function setQuery($query) {
        $this->query = $query;
        return $this;
    }
    /**
     * Get SQL Query method
     * @param $query
     * @return string
     */
    public function method($query = null){
        $methods = array('SELECT','INSERT','UPDATE','DELETE','RENAME','SHOW','SET','DROP','CREATE INDEX','CREATE TABLE','EXPLAIN','DESCRIBE','TRUNCATE','ALTER');
        $queries = empty($query) ? $this->_queries() : array($query);
        foreach($queries as $query){
            foreach($methods as $method) {
                $_method = str_replace(' ', '[\s]+', $method);
                if(preg_match('#^[\s]*'.$_method.'[\s]+#i', $query)){
                    return $method;
                }
            }
        }
        return '';
    }
    /**
     * Get Query fields (at the moment only SELECT/INSERT/UPDATE)
     * @param $query
     * @return array
     */
    public function fields(){
        $fields = array();
        $queries = $this->_queries();
        foreach($queries as $query) {
            $method = $this->method($query);
            switch ($method){
                case 'SELECT':
                    preg_match('#SELECT[\s]+([\S\s]*)[\s]+FROM#i', $query, $matches);
                    if (!empty($matches[1])) {
                        $match = trim($matches[1]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]+(AS[\s]+)?[\w\.]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    }
                    break;
                case 'INSERT':
                    preg_match('#INSERT[\s]+INTO[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)\(([\S\s]*)\)[\s]+VALUES#i', $query, $matches);
                    if (!empty($matches[4])) {
                        $match = trim($matches[4]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]+(AS[\s]+)?[\w\.]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    } else {
                        preg_match('#INSERT[\s]+INTO[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)SET([\S\s]*)([\;])?#i', $query, $matches);
                        if (!empty($matches[4])) {
                            $match = trim($matches[4]);
                            $match = explode(',', $match);
                            foreach ($match as $field) {
                                $field = preg_replace('#([\s]*\=[\s]*[\S\s]+)#i', '', trim($field));
                                $fields[] = $field;
                            }
                        }
                    }
                    break;
                case 'UPDATE':
                    preg_match('#UPDATE[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)SET([\S\s]*)([\s]+WHERE|[\;])?#i', $query, $matches);
                    if (!empty($matches[4])) {
                        $match = trim($matches[4]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]*\=[\s]*[\S\s]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    }
                    break;
                case 'CREATE TABLE':
                    preg_match('#CREATE[\s]+TABLE[\s]+\w+[\s]+\(([\S\s]*)\)#i', $query, $matches);
                    if (!empty($matches[1])) {
                        $match = trim($matches[1]);
                        $match = explode(',', $match);
                        foreach ($match as $_field) {
                            preg_match('#^w+#', trim($_field), $field);
                            if (!empty($field[0])) {
                                $fields[] = $field[0];
                            }
                        }
                    }
                    break;
            }
        }
        return array_unique($fields);
    }
    /**
     * Get SQL Query First Table
     * @param $query
     * @return string
     */
    public function table(){
        $tables = $this->tables();
        return $tables[0];
    }
    /**
     * Get SQL Query Tables
     * @return array
     */
    function tables(){
        $results = array();
        $queries = $this->_queries();
        foreach($queries as $query) {
            $patterns = array(
                '#[\s]+FROM[\s]+(([\s]*(?!'.self::$connectors_imploded.')[\w]+([\s]+(AS[\s]+)?(?!'.self::$connectors_imploded.')[\w]+)?[\s]*[,]?)+)#i',
                '#[\s]*INSERT[\s]+INTO[\s]+([\w]+)#i',
                '#[\s]*UPDATE[\s]+([\w]+)#i',
                '#[\s]+[\s]+JOIN[\s]+([\w]+)#i',
                '#[\s]+TABLE[\s]+([\w]+)#i'
            );
            foreach($patterns as $pattern){
                preg_match_all($pattern,$query, $matches, PREG_SET_ORDER);
                foreach ($matches as $val) {
                    $tables = explode(',', $val[1]);
                    foreach ($tables as $table) {
                        $table = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $table));
                        $results[] = $table;
                    }
                }
            }
        }
        return array_unique($results);
    }
    /**
     * Get all queries
     * @return array
     */
    protected function _queries(){
        $queries = preg_replace('#\/\*[\s\S]*?\*\/#','', $this->query);
        $queries = preg_replace('#;(?:(?<=["\'];)|(?=["\']))#', '', $queries);
        $queries = preg_replace('#[\s]*UNION([\s]+ALL)?[\s]*#', ';', $queries);
        $queries = explode(';', $queries);
        return $queries;
    }
}

EXAMPLE USAGE

header("Content-Type: text/plain"); 

echo '========= Light SQL Parser DEMO =========' . PHP_EOL; 

echo PHP_EOL . '### UPDATE ###' . PHP_EOL; 

$lsp = new LightSQLParser("UPDATE Customers as ae 
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' 
WHERE CustomerID = 1;"); 

// OR 

/* 
$lsp = new LightSQLParser(); 
$lsp->setQuery("UPDATE Customers as ae 
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' 
WHERE CustomerID = 1;"); 
*/ 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields()); 

echo PHP_EOL . '### SELECT ###' . PHP_EOL; 

$lsp->setQuery("SELECT surname, given_names, title FROM Person 
  JOIN Author on person.ID = Author.personID 
  JOIN Book on Book.ID = Author.publicationID 
UNION ALL 
SELECT surname, given_names, title FROM Person 
  JOIN Author on person.ID = Author.personID 
  JOIN Article on Article.ID = Author.publicationID"); 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields()); 

echo PHP_EOL . '### INSERT ###' . PHP_EOL; 

$lsp->setQuery("INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) 
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');"); 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields()); 

Code critique: minimalist SQL query class - PHP, For simpler queries, it generally takes less code to write and for more complex ones I think parsing placeholders is enough for the sql class to do. But now I only use my lightweight active-record system which didn't take much effort but  This class can parse SQL to get query type, tables, field values, etc.. It takes an string with a SQL statements and parses it to extract its different components. Currently the class can extract the SQL query method, the names of the tables involved in the query and the field values that are passed as parameters.


Java mysql sql parser, I need parser on java that parse SQL and return it as some structure. 9. It translates SQLs in a traversable hierarchy of Java classes. convert Strings to Dates and MySQL and Java DB and their respective JDBC drivers do not fully support the Open SQL Parser is a database independant SQL parser written in PHP. $ parser = new PHPSQLParser ($ sql); print_r ( $ parser -> parsed ); $ sql = "(select 1, 1, 1, 1 from dual dual1) union all (select 2, 2, 2, 2 from dual dual2) union all (select c1,c2,c3,sum(c4) from (select c1,c2,c3,c4 from a_table where c2 = 1) subquery group by 1,2,3) limit 10" ;


Supporting Analysis of SQL Queries in PHP AiR, step in the process is to parse the PHP scripts that make up the system being analyzed. an open-source PHP parser7, written in PHP. 8https://github.com/​ecu-pase-lab/sql-parser instance, a SELECT query is represented by a class named [9] M. Hills, P. Klint, and J. J. Vinju, “Static, Lightweight Includes Resolution. Other SQL dialects. Since the MySQL SQL dialect is very close to SQL-92, this should work for most database applications that need a SQL parser. If using another database dialect, then you may want to change the reserved words - see the ParserManual. It supports UNION, subqueries and compound statements. External dependencies. The parser is a self contained class.


Parse json sql, Apr 15, 2020 · moz-sql-parser is a peculiar SQL parser in Python written by Mozilla. For the jobs that they do, the syntax of these functions is simpler to use than is the syntax of json_table. Net, Javascript, Java and PHP classes from JSON. It is it's lightweight, and every API worth playing with is usually JSON-​friendly. It takes an string with a SQL statements and parses it to extract its different components. Currently the class can extract the SQL query method, the names of the tables involved in the query and the field values that are passed as parameters. This parser is pretty light respect phpsqlparser or others php sql parser.