Fails to recognize misspelled SELECT

[bchecketts]

Description

phpstan-dba fails to recognize misspellings of SELECT in common SQL statements. It correctly identifies the misspelling of FROM, so it seems reasonsable to expect it would catch SELCT or SLECT misspellings

Environment:

• OS: Ubuntu 22.04
• PHP: 8.1.2-1ubuntu2.22
• phpstan/phpstan: 1.11.2
• staabm/phpstan-dba: 0.2.85
• Database: MySQL 8.0.40

Test Script (phpstan-dba-bug-report.php)

<?php
// PDO connection
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "user", "pass");

$sqlStatements = [
    "SELECT * FROM users",  // Valid
    "SELCT * FROM users",   // Misspelled SELECT
    "SLECT * FROM users",   // Misspelled SELECT
    "SELECT * FORM users",  // Misspelled FROM
];

foreach ($sqlStatements as $sql) {
    echo "Atttempting SQL: {$sql}\n";
    try {
        $pdo->query($sql);
        echo "  Success. At least no exception\n";
    } catch (\Exception $e) {
        echo "  EXCEPTION: {$e->getMessage()}\n";
    }
}

Bootstrap File (phpstan-dba-bug-bootstrap.php)

<?php
// Minimal bootstrap file for bug report

use staabm\PHPStanDba\DbSchema\SchemaHasherMysql;
use staabm\PHPStanDba\QueryReflection\RuntimeConfiguration;
use staabm\PHPStanDba\QueryReflection\PdoMysqlQueryReflector;
use staabm\PHPStanDba\QueryReflection\QueryReflection;
use staabm\PHPStanDba\QueryReflection\ReplayAndRecordingQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReflectionCache;

require_once __DIR__ . '/vendor/autoload.php';

$config = new RuntimeConfiguration();

// Database connection

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'testuser', 'testpass');

QueryReflection::setupReflector(
    new ReplayAndRecordingQueryReflector(
        ReflectionCache::create(__DIR__ . '/.phpstan-dba.cache'),
        new PdoMysqlQueryReflector($pdo),
        new SchemaHasherMysql($pdo)
    ),
    $config
);

phpstan config (phpstan-bug.neon)

includes:
    - vendor/staabm/phpstan-dba/config/dba.neon

parameters:
    level: 5
    paths:
        - phpstan-dba-bug-report.php
    bootstrapFiles:
        - phpstan-dba-bug-bootstrap.php

Script Output:

04:12 $ php phpstan-dba-bug-report.php
Atttempting SQL: SELECT * FROM users
  Success. At least no exception
Atttempting SQL: SELCT * FROM users
  EXCEPTION: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELCT * FROM users' at line 1
Atttempting SQL: SLECT * FROM users
  EXCEPTION: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLECT * FROM users' at line 1
Atttempting SQL: SELECT * FORM users
  EXCEPTION: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORM users' at line 1

phpstan output:

04:12 $ ./vendor/bin/phpstan analyze -c phpstan-bug.neon
 1/1 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓] 100%

 ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Line   phpstan-dba-bug-report.php
 ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  28     Query error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL/MariaDB server version for the right syntax to use near 'FORM
         users LIMIT 0' at line 1 (42000).
 ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 [ERROR] Found 1 error

Expected output: I expected that it would have found three errors instead of just one. It correctly recognized the misspelling of FROM (FORM). But I expected it to generate errors for the misspelling of SELECT as SELCT and SLECT

[staabm]

problem is, we are trying to be defensive and do inspection only for readonly queries (identified by a SELECT query type). this can be widened with RuntimeConfiguration->analyzeWriteQueries() for more query types. but since mysql does not support transactions for some queries we play it safe and stop analyzing in case we are not able to detect the query type.

we don't do this in other database driver extensions, as we added transactions to prevent accidentaly changes. I think it might be some oversight that we still have checks like 'SELECT' !== QueryReflection::getQueryType($queryString) in the Pdo* classes.

maybe we can drop this checks in case we know the underlying database version supports transactions for all queries.

---

alternatively we might be able to AST parse the query and report a syntax error when queries are not AST parseable (we already use sqlftw/sqlftw for mysql ast).