Php Sql Parser Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require greenlion/php-sql-parser
   

   Add to composer.json if not using autoloader:

   "autoload": {
       "psr-4": {
           "App\\": "app/",
           "GreenLion\\SQLParser\\": "vendor/greenlion/php-sql-parser/src/"
       }
   }
   

   Run composer dump-autoload.

2. First Use Case Parse a simple query in a Laravel controller or service:

   use GreenLion\SQLParser\Parser;
   
   $parser = new Parser();
   $sql = 'SELECT id, name FROM users WHERE id > 10 LIMIT 10';
   $ast = $parser->parse($sql);
   
   // Output the Abstract Syntax Tree (AST)
   print_r($ast);
   

3. Key Entry Points

   • Parser::parse(): Core method to generate AST.
   • Parser::getAST(): Retrieve the parsed AST object.
   • Parser::getTokens(): Get raw token stream (for debugging).

Implementation Patterns

Common Workflows

1. Query Analysis Extract metadata from SQL (e.g., table names, columns, conditions):

   $ast = $parser->parse('SELECT * FROM users WHERE active = 1');
   $tables = collect($ast->getTables())->pluck('name'); // ['users']
   $columns = collect($ast->getColumns())->pluck('name'); // ['*']
   

2. Dynamic Query Building Modify parsed SQL programmatically (e.g., add conditions):

   $ast = $parser->parse('SELECT id FROM users');
   $ast->where->addCondition('active', '=', true);
   $modifiedSql = $ast->getSQL(); // "SELECT id FROM users WHERE active = 1"
   

3. Validation & Sanitization Check for unsafe patterns (e.g., UNION, DROP TABLE):

   $ast = $parser->parse($userInputSql);
   if ($ast->hasUnsafeOperations()) {
       throw new \Exception('SQL contains unsafe operations.');
   }
   

4. ORM Integration Translate parsed SQL to Eloquent queries:

   $ast = $parser->parse('SELECT name, email FROM users WHERE status = ?');
   $query = User::query();
   foreach ($ast->getColumns() as $column) {
       $query->addSelect($column->name);
   }
   $query->where('status', $ast->where->conditions[0]->value);
   

5. Token Stream Processing Inspect raw tokens for custom logic:

   $tokens = $parser->getTokens();
   foreach ($tokens as $token) {
       if ($token->type === 'KEYWORD' && $token->value === 'JOIN') {
           // Handle JOIN logic
       }
   }
   

Integration Tips

• Laravel Service Provider Bind the parser as a singleton for global access:

  $this->app->singleton(Parser::class, function () {
      return new Parser();
  });
  

• Middleware for SQL Inspection Parse incoming SQL in middleware (e.g., for API queries):

  public function handle($request, Closure $next) {
      $sql = $request->input('sql');
      $ast = app(Parser::class)->parse($sql);
      // Log or validate $ast
      return $next($request);
  }
  

• Event Listeners Trigger events when specific SQL patterns are detected:

  $parser->on('table-access', function ($table) {
      \Log::info("Table accessed: {$table->name}");
  });
  $parser->parse($sql);
  

• Caching Parsed Queries Cache AST objects for repeated queries:

  $cacheKey = md5($sql);
  $ast = Cache::remember($cacheKey, now()->addHours(1), function () use ($parser, $sql) {
      return $parser->parse($sql);
  });
  

Gotchas and Tips

Pitfalls

1. Non-Standard SQL Dialects

   • The parser focuses on MySQL. Queries with PostgreSQL-specific syntax (e.g., ILIKE, RETURNING) may fail or produce incomplete ASTs.
   • Workaround: Normalize SQL to MySQL dialect before parsing or handle exceptions gracefully.

2. Complex SQL Features

   • CTEs (Common Table Expressions): Partial support; nested CTEs may break.
   • Window Functions: Limited parsing (e.g., OVER(PARTITION BY ...) may not be fully analyzed).
   • Workaround: Use try-catch and fall back to raw SQL execution if parsing fails.

3. Whitespace Sensitivity

   • The parser is not whitespace-agnostic. Queries with inconsistent formatting (e.g., SELECT * FROM) may cause tokenization issues.
   • Tip: Pre-process SQL with trim() or regex to normalize whitespace:

     $sql = preg_replace('/\s+/', ' ', $sql);
     

4. Parameterized Queries

   • Named parameters (e.g., :param) are not supported. Only ? placeholders work.
   • Tip: Replace named params with ? before parsing:

     $sql = str_replace([':id', ':name'], ['?', '?'], $sql);
     

5. Case Sensitivity

   • Keywords (e.g., SELECT, FROM) are case-insensitive in parsing but may affect AST structure.
   • Tip: Standardize case for consistency:

     $sql = strtoupper($sql); // or strtolower()
     

Debugging Tips

1. Inspect Tokens Dump raw tokens to diagnose parsing issues:

   $tokens = $parser->getTokens();
   foreach ($tokens as $token) {
       var_dump($token->type, $token->value);
   }
   

2. AST Visualization Use print_r or json_encode to explore the AST structure:

   echo json_encode($ast, JSON_PRETTY_PRINT);
   

3. Partial Parsing Handle parsing errors gracefully:

   try {
       $ast = $parser->parse($sql);
   } catch (\GreenLion\SQLParser\Exception\ParseException $e) {
       \Log::error("SQL parse error: {$e->getMessage()}");
       // Fallback: execute raw SQL or return empty result
   }
   

4. Test Edge Cases Common failure points:

   • Queries with comments (e.g., SELECT /* comment */ * FROM users).
   • Subqueries in WHERE, FROM, or SELECT.
   • Aliases with special characters (e.g., FROM users AS "user_1").

Extension Points

1. Custom Token Handlers Extend tokenization for domain-specific SQL:

   $parser->addTokenHandler('CUSTOM_KEYWORD', function ($lexer, $value) {
       return new \GreenLion\SQLParser\Token('CUSTOM_KEYWORD', $value);
   });
   

2. AST Modifiers Override AST nodes post-parsing:

   $ast = $parser->parse($sql);
   $ast->visit(function ($node) {
       if ($node instanceof \GreenLion\SQLParser\AST\Where) {
           $node->addCondition('custom_field', '>', 'value');
       }
   });
   

3. Plugin System Create reusable parsers for specific use cases:

   class UserQueryParser {
       public function parse($sql) {
           $parser = new Parser();
           $ast = $parser->parse($sql);
           // Add user-specific logic
           return $ast;
       }
   }
   

4. Performance Optimization

   • Cache Parsed ASTs: Store AST objects in Redis or file cache.
   • Lazy Loading: Parse only the parts of the AST you need:

     $ast = $parser->parse($sql);
     $columns = $ast->getColumns(); // Only parse columns if needed
     

5. Integration with Query Builders Sync parsed SQL with Laravel’s query builder:

   $ast = $parser->parse($sql);
   $query = DB::query();
   foreach ($ast->getTables() as $table) {
       $query->from($table->name);
   }
   // Map conditions, joins, etc.