Sql Parser Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require phpmyadmin/sql-parser
   

   Add to composer.json if not auto-loaded:

   "autoload": {
       "psr-4": {
           "App\\": "app/",
           "PMA\\SQLParser\\": "vendor/phpmyadmin/sql-parser/src/"
       }
   }
   

   Run composer dump-autoload.

2. First Use Case: Parsing a Query

   use PMA\SQLParser\Parser;
   
   $parser = new Parser();
   $sql = "SELECT * FROM users WHERE id = 1";
   $result = $parser->parse($sql);
   
   // Outputs parsed structure (AST)
   print_r($result);
   

3. Where to Look First

   • Documentation: GitHub README (limited but practical).
   • Source Code: src/Parser.php (core logic), src/Parser/Parser.php (grammar rules).
   • Tests: tests/ for edge cases and validation examples.

Implementation Patterns

1. Query Validation

Use Case: Sanitize user input or enforce SQL standards.

$parser = new Parser();
$sql = "SELECT * FROM users WHERE id = '1' OR 1=1"; // Malicious input

try {
    $parser->parse($sql);
    // Proceed if valid
} catch (\PMA\SQLParser\Exception $e) {
    abort(400, "Invalid SQL: " . $e->getMessage());
}

2. Query Analysis

Use Case: Extract metadata (tables, columns, joins) for caching or optimization.

$parser = new Parser();
$sql = "SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id";
$result = $parser->parse($sql);

// Extract tables/columns
$tables = $result->getTables(); // ['users', 'orders']
$columns = $result->getColumns(); // ['u.name', 'o.total']

3. Query Transformation

Use Case: Rewrite queries for compatibility or performance.

$parser = new Parser();
$sql = "SELECT * FROM users LIMIT 10 OFFSET 20";
$result = $parser->parse($sql);

// Modify the AST (e.g., replace LIMIT/OFFSET with pagination logic)
$rewritten = $result->rewrite(function ($node) {
    if ($node instanceof \PMA\SQLParser\Node\Select\Limit) {
        return new \PMA\SQLParser\Node\Select\Pagination(
            $node->getOffset(),
            $node->getCount()
        );
    }
    return $node;
});

echo $rewritten->getSQL(); // Outputs transformed SQL

4. Integration with Laravel

Use Case: Middleware for SQL validation or query logging.

// app/Http/Middleware/ValidateSQL.php
public function handle($request, Closure $next) {
    $parser = new \PMA\SQLParser\Parser();
    $parser->parse($request->input('sql')); // Validate raw SQL input
    return $next($request);
}

Use Case: Eloquent Query Builder Extension

// Extend Query Builder to parse raw SQL
use PMA\SQLParser\Parser;

class CustomQueryBuilder extends \Illuminate\Database\Query\Builder {
    public function parseRawSql($sql) {
        $parser = new Parser();
        return $parser->parse($sql);
    }
}

Gotchas and Tips

Pitfalls

1. MySQL Dialect Only

   • The parser is MySQL-specific. Queries with TOP (SQL Server) or FETCH (PostgreSQL) will fail.
   • Workaround: Pre-process queries or use a dialect-aware wrapper.

2. AST Modification Complexity

   • The Abstract Syntax Tree (AST) is not fully documented. Refer to tests (tests/ParserTest.php) for node types.
   • Tip: Use var_dump($result) to inspect nodes before rewriting.

3. Performance with Large Queries

   • Parsing complex queries (e.g., deeply nested subqueries) can be slow.
   • Tip: Cache parsed results if reusing the same query.

4. Error Messages

   • Exceptions lack context. Wrap parsing in a try-catch and log the full SQL for debugging:

     try {
         $parser->parse($sql);
     } catch (\Exception $e) {
         \Log::error("Failed to parse SQL: {$sql}", ['error' => $e->getMessage()]);
     }
     

Debugging Tips

• Enable Verbose Errors: Set error_reporting(E_ALL) to catch parsing issues early.
• Test Edge Cases: Use the test suite (php vendor/bin/phpunit) to validate your queries.
• Check Grammar Rules: Modify src/Parser/Parser.php if you need custom syntax support (advanced).

Extension Points

1. Custom Node Handlers Add logic to handle unsupported syntax by extending the parser:

   $parser = new Parser();
   $parser->addListener(new class implements \PMA\SQLParser\Listener {
       public function enterNode(\PMA\SQLParser\Node $node) {
           if ($node instanceof \PMA\SQLParser\Node\Select) {
               // Custom logic for SELECT nodes
           }
       }
   });
   

2. Plugin System For reusable transformations, create a plugin:

   class PaginationPlugin {
       public static function apply(\PMA\SQLParser\Node $node) {
           // Rewrite LIMIT/OFFSET to pagination
       }
   }
   

3. Integration with Laravel Scout Use the parser to validate full-text search queries before indexing:

   $parser->parse($request->search_query);
   // Proceed with Scout indexing
   

Config Quirks

• No Configuration File: The parser is stateless. All settings are passed via constructor or methods.
• Default Behavior: Assumes MySQL 8.0+ syntax. For older versions, manually adjust grammar rules.