Download the PHP package pierresh/phpstan-pdo-mysql without Composer

PHPStan PDO MySQL Rules

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 seven 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
4. Self-Reference Detection - Catches self-reference conditions in JOIN and WHERE clauses
5. Invalid Table Reference Detection - Catches typos in table/alias names (e.g., user.name when table is users)
6. Tautological Condition Detection - Catches always-true/false conditions like WHERE 1 = 1
7. MySQL-Specific Syntax Detection - Flags MySQL-specific functions that have portable ANSI alternatives

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

Developer Tools:

• ddt() Helper Function - Generates PHPStan type definitions from runtime values for easy copy-paste into your code
• ddc() Helper Function - Generates PHP class definitions from objects for use with PDO::fetchObject()

Installation

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

Manual registration in phpstan.neon:

Examples

1. SQL Syntax Validation

Catches syntax errors in SQL queries:

[!CAUTION] SQL syntax error in query(): Expected token NAME ~RESERVED, but end of query found instead.

Works with both direct strings and variables:

[!CAUTION] SQL syntax error in query(): Expected token NAME ~RESERVED, but end of query found instead.

2. Parameter Binding Validation

Ensures all SQL placeholders have corresponding bindings:

[!CAUTION] Missing parameter :name in execute()

[!CAUTION] Parameter :extra in execute() is not used

[!CAUTION] Missing parameter :user_id in execute()

Parameter :id in execute() is not used

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

[!CAUTION] Missing parameter :id in execute()

Parameter :name in execute() is not used

3. SELECT Column Validation

Validates that SELECT columns match the PHPDoc type annotation.

[!NOTE] This rule supports fetch(), fetchObject(), and fetchAll() methods, assuming the fetch mode of the database connection is PDO::FETCH_OBJ (returning objects). Other fetch modes like PDO::FETCH_ASSOC (arrays) or PDO::FETCH_CLASS are not currently validated.

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

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

Supports @phpstan-type aliases:

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

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

Fetch Method Type Validation

The extension also validates that your PHPDoc type structure matches the fetch method being used:

[!CAUTION] Type mismatch: fetchAll() returns array<object{...}> but PHPDoc specifies object{...} (line X)

[!CAUTION] Type mismatch: fetch() returns object{...} but PHPDoc specifies array<object{...}> (line X)

[!NOTE] Both PHPStan array syntaxes are supported:

• Generic syntax: array<object{...}>
• Suffix syntax: object{...}[]

False Return Type Validation

The extension validates that fetch() and fetchObject() calls properly handle the false return value that occurs when no rows are found.

[!CAUTION] Missing |false in @var type: fetch() can return false when no results found. Either add |false to the type or check for false/rowCount() before using the result (line X)

[!CAUTION] Missing |false in @var type: fetch() can return false when no results found. Either add |false to the type or check for false/rowCount() before using the result (line X)

[!NOTE] This validation applies only to fetch() and fetchObject(). The fetchAll() method returns an empty array instead of false, so it doesn't require |false in the type annotation.

4. Self-Reference Detection

Detects self-reference conditions where the same column is compared to itself. This is likely a bug where the developer meant to reference a different table or column.

[!CAUTION] Self-referencing JOIN condition: 'users.id = users.id'

[!CAUTION] Self-referencing WHERE condition: 'products.category_id = products.category_id'

[!CAUTION] Self-referencing JOIN condition: 'products.id = products.id'

Self-referencing WHERE condition: 'products.active = products.active'

[!NOTE] This rule works with:

• INNER JOIN, LEFT JOIN, RIGHT JOIN conditions
• WHERE clause conditions (including AND/OR combinations)
• Both SELECT and INSERT...SELECT queries
• Queries with PDO placeholders (:parameter)

The rule reports errors on the exact line where the self-reference occurs, making it easy to locate and fix the issue.

5. Invalid Table Reference Detection

Detects typos in table and alias names used in qualified column references. Catches errors like using user.name when the table is users, or referencing a table that doesn't appear in FROM/JOIN clauses.

[!CAUTION] Invalid table reference 'user' - available tables/aliases: users

[!CAUTION] Invalid table reference 'usr' - available tables/aliases: u, users

[!CAUTION] Invalid table reference 'orders' - available tables/aliases: users

The rule validates:

• Column references in SELECT clause
• Column references in WHERE conditions
• Column references in JOIN conditions
• Column references in ORDER BY and GROUP BY clauses
• Column references in HAVING clause

This catches common typos that would only be discovered at runtime, like:

• Singular/plural mistakes (user vs users)
• Typos in alias names (usr vs usrs)
• Wrong table references in complex JOINs

6. Tautological Condition Detection

Detects tautological conditions that are always true or always false. These are often left over from development (e.g., WHERE 1 = 1 used to easily toggle conditions) and should be removed before committing.

[!CAUTION] Tautological condition in WHERE clause: '1 = 1' (always true)

[!CAUTION] Tautological condition in WHERE clause: '1 = 0' (always false)

[!CAUTION] Tautological condition in WHERE clause: ''yes' = 'yes'' (always true)

[!CAUTION] Tautological condition in WHERE clause: 'TRUE = FALSE' (always false)

[!CAUTION] Tautological condition in JOIN clause: '1 = 1' (always true)

[!NOTE] This rule detects:

• Numeric comparisons: 1 = 1, 0 = 0, 42 = 42, 1 = 0
• String comparisons: 'yes' = 'yes', 'a' = 'b'
• Boolean comparisons: TRUE = TRUE, FALSE = FALSE, TRUE = FALSE
• In WHERE, JOIN ON, and HAVING clauses

7. MySQL-Specific Syntax Detection

Detects MySQL-specific SQL syntax that has portable ANSI alternatives. This helps maintain database-agnostic code for future migrations to PostgreSQL, SQL Server, or other databases.

[!CAUTION] Use COALESCE() instead of IFNULL() for database portability

[!CAUTION] Use CASE WHEN instead of IF() for database portability

[!CAUTION] Bind current datetime to a PHP variable instead of NOW() for database portability

[!CAUTION] Bind current date to a PHP variable instead of CURDATE() for database portability

[!CAUTION] Use LIMIT count OFFSET offset instead of LIMIT offset, count for database portability

Currently detects:

• IFNULL() → Use COALESCE()
• IF() → Use CASE WHEN
• NOW() → Bind PHP datetime variable
• CURDATE() → Bind PHP date variable
• LIMIT offset, count → Use LIMIT count OFFSET offset

Requirements

• PHP 8.1+
• PHPStan 1.10+
• SQLFTW 0.1+ (SQL syntax validation)

How It Works

All four 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:

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

Known Limitations

• SQL queries with variable interpolation (e.g., "SELECT $column FROM table") cannot be validated
• SELECT * and SELECT table.* queries cannot be validated for column matching (no way to know columns statically)
• Very long queries (>10,000 characters) are skipped for performance
• Cross-file SQL tracking is limited to class properties

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

Available Error Identifiers

Ignoring Specific Errors

All errors from this extension have custom identifiers that allow you to selectively ignore them in your phpstan.neon:

You can also ignore errors by path or message pattern:

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.

Developer Tools

ddt() - Dump Debug Type

The ddt() helper function inspects PHP values at runtime and generates PHPStan type definitions. This is useful for quickly creating @phpstan-type annotations from real data in tests.

Usage in PHPUnit tests:

Terminal output:

Simply copy the output and paste it into your code as a type annotation!

Supported types:

• Objects (stdClass and class instances): Shows public properties as object{...} shape
• Associative arrays: Formatted as array{key: type, ...}
• Sequential arrays: Formatted as array<int, type>
• Nested structures: Handles nesting up to 5 levels deep
• All scalar types: int, float, string, bool, null

Type mapping:

Examples:

Note: The function calls exit(0) after dumping (like dd()), so execution stops. This is intentional for use in debugging/testing workflows.

ddc() - Dump Debug Class

The ddc() helper function inspects PHP objects at runtime and generates PHP class definitions. This is useful for creating view model classes compatible with PDO::fetchObject().

Usage in PHPUnit tests:

Terminal output:

Simply copy the output, rename the class, and use it as your view model!

Example workflow:

Supported types:

Note: Like ddt(), this function calls exit(0) after dumping.

Development

To contribute to this project:

1. Clone the repository:

2. Install dependencies:

3. Run tests:

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

To run tests once without watching:

4. Analyze source code with PHPStan:

This analyzes only the ./src directory (excludes playground and test fixtures) at maximum level.

5. Refactor code with Rector:

Rector is configured to modernize code to PHP 8.1+ standards with code quality improvements.

6. Format code with Mago:

Mago provides consistent, opinionated code formatting for PHP 8.1+.

7. Lint code with Mago:

8. Analyze code with Mago:

Mago's analyzer provides fast, type-level analysis to find logical errors and type mismatches.

License

MIT

Contributing

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