SQL Parser

Overview

The Hazaar SQL Parser is a utility for programmatically parsing, analyzing, and manipulating SQL queries in PHP. It is designed to help you safely inspect and modify user-provided queries, enforce security rules, or rewrite queries before execution. The parser converts SQL into a structured, traversable object model, making it easy to inspect tables, columns, conditions, and more.

Warning

Experimental Feature: This SQL parser is currently experimental and may change in future releases. It is recommended for advanced users and internal tooling. Please report any issues or unexpected behavior.

Info

Supported Queries: Currently, only SQL SELECT statements are supported. INSERT, UPDATE, DELETE, and other query types may be supported in the future.

Usage

Instantiating the Parser

You can use the parser directly via the Hazaar\DBI\SQLParser class:

use Hazaar\DBI\SQLParser;

$sql = 'SELECT id, name, t.name as type_name FROM my_records r INNER JOIN my_record_type t ON r.type_id=t.id WHERE r.id=1';
$parser = new SQLParser($sql);

After parsing, the SQLParser instance exposes properties describing the query structure:

• $parser->type — Query type (currently always 'SELECT')
• $parser->columns — Array of columns (with aliases if present)
• $parser->table — Main table name
• $parser->alias — Table alias (if any)
• $parser->joins — Array of JOIN clauses (type, reference table, ON condition, alias)
• $parser->where — Parsed WHERE conditions as a nested associative array
• $parser->group — GROUP BY columns
• $parser->having — HAVING conditions (same structure as WHERE)
• $parser->order — ORDER BY clauses
• $parser->limit — LIMIT value
• $parser->offset — OFFSET value
• $parser->fetch — FETCH clause (if present)
• $parser->combine — UNION, INTERSECT, EXCEPT (if present)

Example: Parsing and Inspecting a Query

$sql = 'SELECT name, email FROM users WHERE id = 1 AND status = "active"';
$parser = new SQLParser($sql);

// Access parsed components
print_r($parser->columns); // ['name', 'email']
echo $parser->table; // 'users'
print_r($parser->where); // ['$and' => [['id' => 1], ['status' => 'active']]]

Example: Parsing Nested Conditions

$sql = 'SELECT name FROM users WHERE (id = 1 AND status = "active") OR email = "[email protected]"';
$parser = new SQLParser($sql);
print_r($parser->where);
// Output:
// [
//   '$or' => [
//     [ '$and' => [ ['id' => 1], ['status' => 'active'] ] ],
//     [ 'email' => '[email protected]' ]
//   ]
// ]

Integration with Hazaar DBI

If you are using the Hazaar DBI Adapter, you can still use the parseSQL() method for convenience. This will return a Hazaar\DBI\Table object, but you can access the parser via the table's internal properties if needed.

$db = \Hazaar\DBI\Adapter::create();
$query = $db->parseSQL($sql);
// $query is a Table object, but you can inspect the parsed SQL via $query->_parser

Supported Features

• SELECT queries (other types may be supported in the future)
• Table and column extraction (including aliases)
• JOIN parsing (INNER, LEFT, RIGHT, etc.)
• WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, FETCH
• Nested and complex WHERE/HAVING conditions (AND/OR, parentheses)
• SQL functions in SELECT columns (e.g., COUNT(*))
• UNION, INTERSECT, EXCEPT (as sub-parsers)

Limitations

• Only SELECT queries are supported at this time.
• SQL syntax must be reasonably standard; highly non-standard or vendor-specific SQL may not parse correctly.
• The parser does not execute SQL or validate against a schema.
• Some advanced SQL features (window functions, subqueries in SELECT, etc.) may not be fully supported.

Example: Restricting Access to Tables

You can use the parser to enforce table-level security:

$allowed_tables = ['my_records', 'my_record_type'];
$parser = new SQLParser($sql);
$tables = [$parser->table];
foreach ($parser->joins as $join) {
    $tables[] = $join['ref'];
}
foreach ($tables as $table) {
    if (!in_array($table, $allowed_tables)) {
        throw new \Exception("You are not allowed to access table '$table'!");
    }
}

Example: Overriding Query Parameters

$parser = new SQLParser($sql);
$parser->limit = 100;
$parser->offset = 200;
// You can now use $parser to generate a modified query or pass it to your DBI layer

Testing

The parser is covered by PHPUnit tests in tests/dbi/SQLParserTest.php. You can review these tests for more usage examples and expected output.

---

For more details, see the source code in src/DBI/SQLParser.php or the test suite for real-world examples.