Php Sql Parser Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require aitradeinc/php-sql-parser
   

   Add the namespace to your project:

   use Aitradeinc\SqlParser\Parser;
   

2. First Use Case Parse a simple SQL query to extract components:

   $parser = new Parser();
   $sql = "SELECT id, name FROM users WHERE id = 1";
   $parsed = $parser->parse($sql);
   
   // Output parsed structure
   print_r($parsed);
   

3. Where to Look First

   • Documentation: Check the GitHub repository (if available) for basic examples.
   • Source Code: The package is lightweight; inspect src/Parser.php for supported SQL features and parsing logic.
   • Tests: If tests exist, they’ll show real-world usage patterns (e.g., handling SELECT, INSERT, JOIN).

Implementation Patterns

Usage Patterns

1. Parsing Queries for Dynamic Analysis Extract table/column names dynamically:

   $parsed = $parser->parse("SELECT * FROM orders WHERE status = 'shipped'");
   $tables = $parsed['tables']; // ['orders']
   $columns = $parsed['columns']; // ['*']
   

2. Validation and Sanitization Reject unsafe queries (e.g., containing DROP TABLE):

   $sql = "SELECT * FROM users; DROP TABLE users;";
   $parsed = $parser->parse($sql);
   if (isset($parsed['statements'][1]['type']) && $parsed['statements'][1]['type'] === 'DROP') {
       throw new \RuntimeException("Unsafe SQL detected!");
   }
   

3. Generating SQL from Parsed Structures Rebuild queries programmatically:

   $select = ['id', 'name'];
   $from = ['users'];
   $where = ['id = ?'];
   $rebuiltSql = "SELECT " . implode(', ', $select) .
                 " FROM " . implode(', ', $from) .
                 " WHERE " . implode(' AND ', $where);
   

4. Integration with Query Builders Use parsed data to populate Laravel’s query builder:

   $parsed = $parser->parse("SELECT id FROM users WHERE active = true");
   $query = DB::table($parsed['tables'][0]);
   foreach ($parsed['where'] as $condition) {
       [$column, $operator, $value] = $condition;
       $query->where($column, $operator, $value);
   }
   

5. Handling Complex Queries Parse subqueries or joins:

   $sql = "SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id";
   $parsed = $parser->parse($sql);
   $joins = $parsed['joins']; // [['table' => 'orders', 'on' => 'u.id = o.user_id']]
   

Workflows

• ORM Migration Helper: Parse raw SQL in migrations to extract table/column definitions.
• SQL Logging: Log parsed queries (without sensitive data) for debugging:

  $parsed = $parser->parse($request->input('sql'));
  logger()->info('Executed SQL', ['parsed' => $parsed]);
  

• Dynamic Report Builder: Let users define reports via SQL snippets, then parse and validate them.

Integration Tips

• Combine with doctrine/dbal: Use the parser alongside DBAL for advanced SQL analysis.
• Extend for Custom Dialects: Override parsing logic for non-standard SQL (e.g., vendor-specific extensions).
• Cache Parsed Results: Store parsed structures in Redis if the same queries repeat often.

Gotchas and Tips

Pitfalls

1. Limited SQL Support

   • The package may not handle:
     • Complex CTEs (Common Table Expressions) like WITH RECURSIVE.
     • Window functions (e.g., OVER() clauses).
     • Database-specific syntax (e.g., PostgreSQL’s ILIKE, MySQL’s ENGINE).
   • Workaround: Pre-process SQL or use a fallback parser for unsupported features.

2. False Positives in Parsing

   • Strings in SQL (e.g., WHERE name = 'O\'Reilly') may break parsing.
   • Fix: Use Parser::parseWithStrings() if available, or pre-sanitize inputs.

3. Performance Overhead

   • Parsing large queries (e.g., multi-line INSERT with 1000 rows) may be slow.
   • Tip: Cache parsed results or parse only when necessary (e.g., during validation).

4. No Standardized Output

   • The parsed structure may vary between versions or SQL types.
   • Tip: Normalize output in your code:

     $parsed = $parser->parse($sql);
     $tables = $parsed['from'] ?? $parsed['tables'] ?? [];
     

5. No Active Maintenance

   • Last release was 2021. Test thoroughly and consider forking if critical bugs arise.

Debugging

• Verify Parsed Structure:

  $parsed = $parser->parse("SELECT 1");
  var_dump($parsed); // Check if keys like 'select', 'where' exist.
  

• Handle Exceptions:

  try {
      $parser->parse("INVALID SQL");
  } catch (\Exception $e) {
      logger()->error("Parse error: " . $e->getMessage());
  }
  

• Test Edge Cases:
  • Empty queries ("").
  • Queries with comments (-- comment or /* multi-line */).
  • Case sensitivity (e.g., select vs SELECT).

Config Quirks

• No Configuration File: All behavior is hardcoded. Extend Parser class to add options:

  class CustomParser extends Parser {
      public function parseWithOptions($sql, array $options) {
          // Add logic for $options['ignore_case'], etc.
      }
  }
  

Extension Points

1. Add Custom SQL Types Extend the parser to recognize vendor-specific syntax:

   // In a custom parser class:
   protected function parseCustom($sql) {
       if (strpos($sql, 'CUSTOM_KEYWORD') !== false) {
           return ['type' => 'custom', 'sql' => $sql];
       }
   }
   

2. Plugin System Use composer autoloading to add parsers for specific SQL features:

   // Register a plugin:
   $parser->addPlugin(new CustomSqlPlugin());
   

3. Output Transformers Convert parsed data to other formats (e.g., JSON for APIs):

   $json = json_encode([
       'tables' => $parsed['tables'],
       'columns' => $parsed['columns'],
   ]);
   

4. Integration with Laravel Events Listen to query events and parse SQL dynamically:

   DB::listen(function ($query) {
       $parser = new Parser();
       $parsed = $parser->parse($query->sql);
       // Log or analyze $parsed
   });