pierresh/phpstan-pdo-mysql

PHPStan rules for validating PDO/MySQL code: SQL syntax, parameter bindings, and SELECT columns matching PHPDoc types

1.0.5 2025-11-07 05:12 UTC

Requires

• php: ^8.1
• phpmyadmin/sql-parser: ^5.0
• phpstan/phpstan: ^1.10

Requires (Dev)

• phpstan/phpstan-phpunit: ^1.3
• phpunit/phpunit: ^10.0
• spatie/phpunit-watcher: ^1.24

MIT

• Pierre Clavequin

• mysql
• pdo
• static-analysis
• PHPStan
• sql-validation

README

Static analysis rules for PHPStan that validate PDO/MySQL code for common errors that would otherwise only be caught at runtime.

Features

This extension provides three powerful rules that work without requiring a database connection:

1. SQL Syntax Validation - Detects MySQL syntax errors in prepare() and query() calls
2. Parameter Binding Validation - Ensures PDO parameters match SQL placeholders
3. SELECT Column Validation - Verifies SELECT columns match PHPDoc type annotations

All validation is performed statically by analyzing your code, so no database setup is needed.

Installation

composer require --dev pierresh/phpstan-pdo-mysql

The extension will be automatically registered if you use phpstan/extension-installer.

Manual registration in phpstan.neon:

includes:
    - vendor/pierresh/phpstan-pdo-mysql/extension.neon

Examples

1. SQL Syntax Validation

Catches syntax errors in SQL queries:

// ❌ Incomplete query
$stmt = $db->query("SELECT * FROM");

Caution

Error: SQL syntax error in query(): An expression was expected.

Works with both direct strings and variables:

$sql = "SELECT * FROM";
$stmt = $db->query($sql);

Caution

Error: SQL syntax error in query(): An expression was expected.

// ✅ Valid SQL
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");

2. Parameter Binding Validation

Ensures all SQL placeholders have corresponding bindings:

// ❌ Missing parameter
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id AND name = :name");
$stmt->execute(['id' => 1]); // Missing :name

Caution

Error: Missing parameter :name in execute() array - SQL query (line X) expects this parameter

// ❌ Extra parameter
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1, 'extra' => 'unused']);

Caution

Error: Parameter :extra in execute() array is not used in SQL query (line X)

// ❌ Wrong parameter name
$stmt = $db->prepare("SELECT * FROM users WHERE id = :user_id");
$stmt->execute(['id' => 1]); // Should be :user_id

Caution

Error: Missing parameter :user_id in execute() array - SQL query (line X) expects this parameter Error: Parameter :id in execute() array is not used in SQL query (line X)

// ✅ Valid bindings
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id AND name = :name");
$stmt->execute(['id' => 1, 'name' => 'John']);

Important: When execute() receives an array, it ignores previous bindValue() calls:

$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindValue(':id', 1); // This is ignored!
$stmt->execute(['name' => 'John']); // Wrong parameter

Caution

Error: Missing parameter :id in execute() array - SQL query (line X) expects this parameter Error: Parameter :name in execute() array is not used in SQL query (line X)

3. SELECT Column Validation

Validates that SELECT columns match the PHPDoc type annotation:

// ❌ Column typo: "nam" instead of "name"
$stmt = $db->prepare("SELECT id, nam, email FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();

Caution

Error: SELECT column mismatch: PHPDoc expects property "name" but SELECT (line X) has "nam" - possible typo?

// ❌ Missing column
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();

Caution

Error: SELECT column missing: PHPDoc expects property "email" but it is not in the SELECT query (line X)

// ✅ Valid columns (extra columns in SELECT are allowed)
$stmt = $db->prepare("SELECT id, name, email FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();

// ✅ Also valid - selecting extra columns is fine
$stmt = $db->prepare("SELECT id, name, email, created_at FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch(); // No error - extra columns are ignored

Supports @phpstan-type aliases:

/**
 * @phpstan-type User object{id: int, name: string, email: string}
 */
class UserRepository
{
    public function findUser(int $id): void
    {
        // Typo: "nam" instead of "name", also missing "email"
        $stmt = $this->db->prepare("SELECT id, nam FROM users WHERE id = :id");
        $stmt->execute(['id' => $id]);

        /** @var User */
        $user = $stmt->fetch();

Caution

Error: SELECT column mismatch: PHPDoc expects property "name" but SELECT (line X) has "nam" - possible typo? Error: SELECT column missing: PHPDoc expects property "email" but it is not in the SELECT query (line X)

    }
}

Requirements

• PHP 8.1+
• PHPStan 1.10+
• phpmyadmin/sql-parser 5.0+

How It Works

All three rules use a two-pass analysis approach:

1. First pass: Scan the method for SQL query strings (both direct literals and variables)
2. Second pass: Find all prepare()/query() calls and validate them

This allows the rules to work with both patterns:

// Direct string literals
$stmt = $db->prepare("SELECT ...");

// Variables
$sql = "SELECT ...";
$stmt = $db->prepare($sql);

The rules also handle SQL queries prepared in constructors and used in other methods.

Performance

These rules are designed to be fast:

• Early bailouts for non-SQL code
• Efficient SQL detection heuristics
• Skips very long queries (>10,000 characters)
• Gracefully handles missing dependencies

Playground

Want to try the extension quickly? Open playground/example.php in your IDE with a PHPStan plugin installed. You'll see errors highlighted in real-time as you edit the code.

Development

To contribute to this project:

1. Clone the repository:

git clone https://github.com/pierresh/phpstan-pdo-mysql.git
cd phpstan-pdo-mysql

1. Install dependencies:

composer install

1. Run tests:

composer test

This will start PHPUnit watcher that automatically runs tests when files change.

To run tests once without watching:

./vendor/bin/phpunit

License

MIT

Contributing

Contributions welcome! Please open an issue or submit a pull request.