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

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 4 years ago.

Improve this question

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;
开发者_如何学C    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.

---

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

---

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());