staabm / phpstan-dba Goto Github PK

View Code? Open in Web Editor NEW

244.0 6.0 17.0 1.29 MB

PHPStan based SQL static analysis and type inference for the database access layer

Home Page: https://staabm.github.io/archive.html#phpstan-dba

License: MIT License

PHP 100.00%

phpstan phpstan-extension static-analysis php query-validation type-inference hacktoberfest

phpstan-dba's Introduction

`phpstan-dba`: PHPStan based SQL static analysis and type inference for the database access layer

phpstan-dba makes your phpstan static code analysis jobs aware of datatypes within your database. With this information at hand we are able to detect type inconsistencies between your domain model and database-schema. Additionally errors in code handling the results of sql queries can be detected.

This extension provides the following features, as long as you stick to the rules:

result set type-inference
detect errors in sql queries
detect placeholder/bound value mismatches
query plan analysis to detect performance issues
builtin support for doctrine/dbal, mysqli, and PDO
API to configure the same features for your custom sql based database access layer
Opt-In analysis of write queries (since version 0.2.55+)

In case you are using Doctrine ORM, you might use phpstan-dba in tandem with phpstan-doctrine.

Note

At the moment only MySQL/MariaDB and PGSQL databases are supported. Technically it's not a big problem to support other databases though.

Talks

phpstan-dba - check your sql queries like a boss May 2023, at PHP Usergroup in Frankfurt Main (Germany).

DEMO

see the 'Files Changed' tab of the DEMO-PR for a quick glance.

💌 Support phpstan-dba

Consider supporting the project, so we can make this tool even better even faster for everyone.

Installation

First, use composer to install:

composer require --dev staabm/phpstan-dba

Second, create a phpstan-dba-bootstrap.php file, which allows to you to configure phpstan-dba (this optionally includes database connection details, to introspect the database; if you would rather not do this see Record and Replay:

<?php // phpstan-dba-bootstrap.php

use staabm\PHPStanDba\DbSchema\SchemaHasherMysql;
use staabm\PHPStanDba\QueryReflection\RuntimeConfiguration;
use staabm\PHPStanDba\QueryReflection\MysqliQueryReflector;
use staabm\PHPStanDba\QueryReflection\QueryReflection;
use staabm\PHPStanDba\QueryReflection\ReplayAndRecordingQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReplayQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReflectionCache;

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

$cacheFile = __DIR__.'/.phpstan-dba.cache';

$config = new RuntimeConfiguration();
// $config->debugMode(true);
// $config->stringifyTypes(true);
// $config->analyzeQueryPlans(true);
// $config->utilizeSqlAst(true);

// TODO: Put your database credentials here
$mysqli = new mysqli('hostname', 'username', 'password', 'database');

QueryReflection::setupReflector(
    new ReplayAndRecordingQueryReflector(
        ReflectionCache::create(
            $cacheFile
        ),
        // XXX alternatively you can use PdoMysqlQueryReflector instead
        new MysqliQueryReflector($mysqli),
        new SchemaHasherMysql($mysqli)

    ),
    $config
);

Note

Configuration for PGSQL is pretty similar

Third, create or update your phpstan.neon file so bootstrapFiles includes phpstan-dba-bootstrap.php.

If you are not using phpstan/extension-installer, you will also need to include dba.neon.

Your phpstan.neon might look something like:

parameters:
  level: 8
  paths:
    - src/
  bootstrapFiles:
    - phpstan-dba-bootstrap.php

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

Finally, run phpstan, e.g.

./vendor/bin/phpstan analyse -c phpstan.neon

phpstan-dba's People

Contributors

Stargazers

Watchers

Forkers

craigfrancis seldaek seriquynh mitelg voku p4vei sumonst21 marmichalski keulinho tomasvotruba hemberger deprecated-packages bloep szepeviktor davidxkurka jakubvojacek patrickkusebauch

phpstan-dba's Issues

Using stringable object as query parameter fails

$dbalConnection->fetchAssociative('
    SELECT *
    FROM bar
    WHERE foo = :enum
', [
    'enum' => new Enum('foo'),
]);

Causes error:

Uncaught staabm\PHPStanDba\DbaException: Unexpected expression type PHPStan\Type\ObjectType in /app/vendor/staabm/phpstan-dba/src/QueryReflection/QuerySimulation.php:91

Even though the Enum has __toString method which works fine in dbal query.

Support for COUNT

Another one:

        $sql = 'SELECT COUNT(*) count FROM package p WHERE p.suspect IS NOT NULL AND (p.replacementPackage IS NULL OR p.replacementPackage != "spam/spam")';

        return (int) $this->getEntityManager()->getConnection()->fetchOne($sql);

triggers Cannot cast mixed to int

From https://github.com/composer/packagist/blob/bb486fc455c7c59544dd23d7508fcdf0fcc02023/src/Entity/PackageRepository.php#L467-L469

Seems to me like count is a clear case of returning int, but maybe I missed something :)

Seems like support should work as per

phpstan-dba/tests/default/data/pdo.php

Lines 155 to 160 in 5227c73

    
           public function aggregateFunctions(PDO $pdo) 
        
           { 
        
               $query = 'SELECT MAX(adaid), MIN(adaid), COUNT(adaid), AVG(adaid) FROM ada WHERE adaid = 1'; 
        
               $stmt = $pdo->query($query, PDO::FETCH_ASSOC); 
        
               assertType('PDOStatement<array{MAX(adaid): int<-2147483648, 2147483647>|null, MIN(adaid): int<-2147483648, 2147483647>|null, COUNT(adaid): int, AVG(adaid): float|null}>', $stmt); 
        
           }

but somehow it doesn't here. Not sure why.

Add support for SUM() & similar functions

SUM() can return numeric-string|null in mysql for example. Right now it is seen as mixed.

Pls add Doctrine connection example to Readme

Hello!

// TODO: Put your database credentials here
new MysqliQueryReflector(new mysqli('hostname', 'username', 'password', 'database'))

I have no idea how to connect Doctrine/Dblal.
Please advise.

unexpected syntax error

refs composer/packagist#1232

    public function getDependents(string $name, int $offset = 0, int $limit = 15, string $orderBy = 'name', ?int $type = null)
    {
        $orderByField = 'p.name ASC';
        $join = '';
        if ($orderBy === 'downloads') {
            $orderByField = 'd.total DESC';
            $join = 'LEFT JOIN download d ON d.id = p.id AND d.type = '.Download::TYPE_PACKAGE;
        } else {
            $orderBy = 'name';
        }

        $args = ['name' => $name];
        $typeFilter = '';
        if (null !== $type) {
            $typeFilter = ' AND type = :type';
            $args['type'] = $type;
        }

        $sql = 'SELECT p.id, p.name, p.description, p.language, p.abandoned, p.replacementPackage
            FROM package p INNER JOIN (
                SELECT DISTINCT package_id FROM dependent WHERE packageName = :name'.$typeFilter.'
            ) x ON x.package_id = p.id '.$join.' ORDER BY '.$orderByField.' LIMIT '.((int)$limit).' OFFSET '.((int)$offset);

        return $this->getEntityManager()->getConnection()->fetchAllAssociative($sql, $args);
    }

leads to

Query error: 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 'LIMIT 0' at line 4                                 
                                                                                
         Simulated query: SELECT p.id, p.name, p.description, p.language,       
         p.abandoned, p.replacementPackage                                      
                     FROM package p INNER JOIN (                                
                         SELECT DISTINCT package_id FROM dependent WHERE        
         packageName = '1' AND type = '1'                                       
                     ) x ON x.package_id = p.id LEFT JOIN download d ON d.id =  
         p.id AND d.type = 1 ORDER BY p.name ASC LIMIT 1 OFFSET 1 LIMIT 0       
         (1064).

refactor node-finder related logic out into symplify/astral

see

Use proper sql parser

Left joined columns should be nullable

support phpstan 1.4.x

see #113

Strange DBAL discrepancy between local env and CI

With this code here https://github.com/composer/packagist/blob/8a3d28e42f9790dea401e5f9afc7c2c9780b58d8/src/Entity/PackageRepository.php#L255-L270

Running locally I get:

Method App\Entity\PackageRepository::getStalePackagesForDumping() should return array<int, string> but returns array<int<0, max>, int<0, 4294967295>>.

Which makes sense given #222

But on CI e.g. https://github.com/composer/packagist/runs/5037284175?check_suite_focus=true I get this:

Method App\Entity\PackageRepository::getStalePackagesForDumping() should return array<int, string> but returns array<int, mixed>.

Any clue what could cause this? All dependency versions are the same as it installs from lock file.

Support for postgresql

Looking at the code, it seems there is only a MysqliQueryReflector for now. Is Postgresql supported ?

add `RecordingQueryReflector` test-coverage

otherweise we might again run into issues like #240

Query error with parameterised queries

Following up on Issue 69, as this is a different issue (I don't want to mix up the two things).

The SQL error, when checking $pdo->prepare():

------ ------------------------------------------------------------------ 
 Line   index.php                                                         
------ ------------------------------------------------------------------ 
 5      Query error: 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 ':id AND 2=2 LIMIT 0' at line 1      
                                                                          
        Simulated query: SELECT * FROM user WHERE id = :id AND 2=2 LIMIT  
        0 (1064).                                                         
 8      Query error: 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 '? AND 2=2 LIMIT 0' at line 1        
                                                                          
        Simulated query: SELECT * FROM user WHERE id = ? AND 2=2 LIMIT 0  
        (1064).                                                           
------ ------------------------------------------------------------------

This output used the new debug mode in RuntimeConfiguration (thanks for adding).

It's happening on both named and question mark parameter queries.

The MysqliQueryReflector::simulateQuery() method is used, and it looks like the query is being sent to the database without parameters being provided (or replaced).

It's interesting that later, when $stmt->execute() is being tested, the values are being replaced in QueryReflection::replaceParameters().

I wonder if this check can be done during $stmt->execute(), when you have some parameters to use... or it might be possible to generate some values, even if it's the number 0, or a value that's appropriate for the field type (considering it's not actually returning any records with a LIMIT 0).

Doctrine DBAL issue with associative keys returned

For a query like SELECT normalizedVersion, data FROM foo which is executed with fetchAllAssociative phpstan-dba assume a type Doctrine\DBAL\Result<array{normalizedVersion: string, 0: string, data: string, 1: string}> but the 0/1 keys do not exist on the real returned data from doctrine. It only populates the associative names (which makes for much cleaner output than raw PDO 👍🏻)

So the expected type would be Doctrine\DBAL\Result<array{normalizedVersion: string, data: string}> here.

It probably applies to iterateAssociative, fetchAssociative and fetchAllAssociative. I am not sure if there are other calls impacted.

Skip generic check

phpstan/phpstan-doctrine@8061e5f

Unsigned big int cannot be smaller than 0

Mark cache files as generated

See https://docs.github.com/en/repositories/working-with-files/managing-files/customizing-how-changed-files-appear-on-github

goal: make the cache files disappear from PR diffs to ease review of the actual change

PDO: Support setting default fetch mode

It is possible to configure the default fetch mode in PDO:

new PDO(..., [
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

It would be similar to setting errorMode on RuntimeConfiguration.

Cover aggregate functions

add throw-type-extension

depending on QueryReflection::getRuntimeConfiguration()->throws*Exceptions in symmetrie to methods for which we remove the false return when exception reporting is enabled

Internal errors with array parameters

This code here makes PHPStan crash with this extension enabled:

    Internal error: Internal error: Unexpected expression type PHPStan\Type\ArrayType in file /var/www/packagist/src/Command/IndexPackagesCommand.php
     Run PHPStan with --debug option and post the stack trace to:
     https://github.com/phpstan/phpstan/issues/new?template=Bug_report.md
     Child process error (exit code 1):

https://github.com/composer/packagist/blob/e3f76f42980e6477e453f13526e33ed174318ff2/src/Command/IndexPackagesCommand.php#L292-L311

It seems to be due to the Connection::PARAM_INT_ARRAY usage, and the fact that one of the parameters is an array, which I assume this lib isn't expecting yet.

Doctrina/dbal 2.x compat

See https://github.com/doctrine/dbal/blob/3.3.x/UPGRADE.md#deprecated-fetchmode-and-the-corresponding-methods

for newer 2.x releases some api is the same to 3.x.

its only about the low hanging fruit. I guess we mostly need to check places like

phpstan-dba/src/Extensions/DoctrineConnectionExecuteQueryDynamicReturnTypeExtension.php

Line 53 in 4d1c84b

    
           if (!InstalledVersions::satisfies(new VersionParser(), 'doctrine/dbal', '3.*')) {

And see whether the checked version also satisfies doctrina dbal 2.x

Worried about performance

Hi, I really like this idea, it's definitely needed, but the fact this extension asks the actual database engine about each analysed query isn't optimal. Sometimes the database doesn't even live on the same machine where PHPStan is running.

I think it'd be much more interesting to download the database structure before the analysis (or let the user provide an actual static dump because sometimes it's even versioned in the repository) and then parse the queries and figure out the types by custom logic in the extension itself.

WDYT?

PDO: support `bindValue` based api

example

https://github.com/shopware/shopware/blob/f5146152531467ddd39c7f91b5d7f74f027ca6fc/engine/Shopware/Components/Session/PdoSessionHandler.php?rgh-link-date=2022-01-24T08%3A52%3A08Z#L319-L335

Problem with conditional join

Refs https://github.com/composer/packagist/pull/1232/files

public function getDependents(string $name, int $offset = 0, int $limit = 15, string $orderBy = 'name', ?int $type = null)
    {
        $orderByField = 'p.name ASC';
        $join = '';
        if ($orderBy === 'downloads') {
            $orderByField = 'd.total DESC';
            $join = 'LEFT JOIN download d ON d.id = p.id AND d.type = '.Download::TYPE_PACKAGE;
        } else {
            $orderBy = 'name';
        }
        $args = ['name' => $name];
        $typeFilter = '';
        if (null !== $type) {
            $typeFilter = ' AND type = :type';
            $args['type'] = $type;
        }
        $sql = 'SELECT p.id, p.name, p.description, p.language, p.abandoned, p.replacementPackage
            FROM package p INNER JOIN (
                SELECT DISTINCT package_id FROM dependent WHERE packageName = :name'.$typeFilter.'
            ) x ON x.package_id = p.id '.$join.' ORDER BY '.$orderByField.' LIMIT '.((int)$limit).' OFFSET '.((int)$offset);

         return $this->getEntityManager()->getConnection()->fetchAllAssociative($sql, $args);
 }

leads to

Query error: Unknown column 'd.total' in 'order clause' (1054).

doctrine dbal type inference

Todo

https://github.com/doctrine/dbal/blob/3.3.x/src/Connection.php

Statement->executequery
Statement->execute

https://github.com/doctrine/dbal/blob/3.3.x/src/Statement.php

https://github.com/doctrine/dbal/blob/3.3.x/src/Result.php

Remaining select-based api seems to be supported already

see https://github.com/staabm/phpstan-dba/blob/main/tests/data/doctrine-dbal.php for everything we understand right now

mass test

gather sql statements out of a application
Run all said statements against phpstan-dba
Identify which of these real world statements make phpstan-dba error (not analyzable query), on which a wrong type is inferred, or no type is inferred at all
Repro the underlying issue with a unit tests
Create a fix

`count()` Should be inferred as a `0|positive-int`

Queries ending with semicolon failing simulation

The query simulation causes analysis of queries with ; at the end to fail on

Query error: 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 'LIMIT 0' at line 6 (1064).

SELECT foo
FROM bar;

running `RecordingQueryReflector` should work without `--debug`

atm we have race conditions when recording, which sometime lead to a different cache result state.

when running with --debug we always get the correct cache state.

merge upstream changes

check whether we need todo similar things as phpstan/phpstan-src@a9881e3

Conditional placeholder bug

refs https://github.com/composer/packagist/pull/1232/files#

    /**
      * @param string[] $packageNames
      */
     public function searchSecurityAdvisories(array $packageNames, int $updatedSince): array
     {
         $sql = 'SELECT s.packagistAdvisoryId as advisoryId, s.packageName, s.remoteId, s.title, s.link, s.cve, s.affectedVersions, s.source, s.reportedAt, s.composerRepository
            FROM security_advisory s
            WHERE s.updatedAt >= :updatedSince ' .
            (count($packageNames) > 0 ? ' AND s.packageName IN (:packageNames)' : '')
            .' ORDER BY s.id DESC';

        return $this->getEntityManager()->getConnection()
            ->fetchAllAssociative(
                $sql,
                [
                    'packageNames' => $packageNames,
                    'updatedSince' => date('Y-m-d H:i:s', $updatedSince),
                ],
                ['packageNames' => Connection::PARAM_STR_ARRAY]
            );
    }

leads to

Query expects 1 placeholder, but 2 values are given.

Detecting possible SQL Injection Vulernerabilties

On your Todos, you have "security rule: detect possible sql injections".

May I suggest using the literal-string type, as it's very simple, and is the only way of being sure an Injection Vulnerability cannot exist.

It basically says the SQL must be written by the developer (i.e. it cannot use any dangerous user input).

All user input should use parameters, on the basis that it's easy for escaping to be forgotten, or go wrong:

$sql .= ' WHERE id = ' . $mysqli->real_escape_string($_GET['id']);

Notice how the developer forgot to quote the value, so the attacker could easily do example.com/?id=id (or something a bit more complex, like using a UNION)... and that's before we get into character set issues, and the sql_mode option NO_BACKSLASH_ESCAPES.

If a developer wants to use the IN() operator, that's fine, as they should still use parameters, maybe with something safe like:

$sql .= 'id IN (' . join(',', array_fill(0, count($ids), '?')) . ')';

// id IN (?, ?, ?)

or maybe use a function like this, to make their code easier to read:

/** @return literal-string */
function in_sql(int $count) { // Maybe check for 0?
  $sql = '?';
  for ($k = 1; $k < $count; $k++) {
    $sql .= ',?';
  }
  return $sql;
}

$sql .= 'id IN (' . in_sql(count($ids)) . ')';

And if a developer is doing something like allowing the user to choose how the results are sorted, they should limit the user to only those fields they are allowed to sort by (and nothing more), e.g.

$order_fields = ['name', 'created', 'type'];

$order_id = array_search(($_GET['sort'] ?? NULL), $order_fields);

$sql .= ' ORDER BY ' . $order_fields[$order_id];

There may be cases where the field names cannot be defined in the source code (some complex CMS'es do this, for "reasons")... in which case, they can do something like @phpstan-ignore-line... this should be very rare, and will be flagged as something to check during an audit (a good thing).

I must admit it's been a while since I've used PDO and MySQLi directly, but I think it's the $sql argument in these functions that will need checking:

pdo::exec($sql);
pdo::query($sql);
pdo::prepare($sql);

mysqli::prepare($sql);
mysqli::query($sql);
mysqli::real_query($sql);
mysqli::multi_query($sql);

fetchOne returns always numeric-string instead of string|false

            $defaultVersionId = $conn->fetchOne('SELECT id FROM package_version WHERE package_id = :id ORDER BY defaultBranch DESC, releasedAt DESC LIMIT 1', ['id' => $id]);
            \PHPStan\dumpType($defaultVersionId);

Dumped type: numeric-string

From https://github.com/composer/packagist/blob/main/src/Command/PopulateDependentsSuggestersCommand.php#L71

fetchOne returns false if there is no result though.

Fatal when cache file does not exist yet

I get this:

Fatal error: Uncaught ErrorException: Warning: require(/var/www/packagist/.phpstan-dba.cache): Failed to open stream: No such file or directory in /var/www/packagist/vendor/staabm/phpstan-dba/src/QueryReflection/ReflectionCache.php:62

It's fixed if I touch .phpstan-dba.cache to ensure the file exists first, but it sounds to me like that shouldn't be needed :)

Cannot simulate parameter value for type: `non-empty-array<int, int>.`

composer/packagist#1232

https://github.com/composer/packagist/blob/c7d192a3bb5a48a2c5d54a5ede4115fa9d188d6a/src/Package/SymlinkDumper.php#L426-L434

                    $this->getEM()->getConnection()->executeQuery(
                        'UPDATE package SET dumpedAt=:dumped WHERE id IN (:ids)',
                        [
                            'ids' => $ids,
                            'dumped' => $dt,
                        ],
                        ['ids' => Connection::PARAM_INT_ARRAY]
                    );

leads to

  426    Unresolvable Query: Cannot simulate parameter value for type:  
         non-empty-array<int, int>.                                     
         💡 Make sure all variables involved have a non-mixed type and   
         array-types are specified.

Support more mysql types

Might be inspired from https://blog.devart.com/mysql-data-types.html

Readme tweaks

This is just my initial thoughts, feel free to ignore/edit as you see fit...

The README.md file currently has two cases of calling QueryReflection::setupReflector()

https://github.com/staabm/phpstan-dba/blob/main/README.md?plain=1#L46
https://github.com/staabm/phpstan-dba/blob/main/README.md?plain=1#L77

But this method requires a RuntimeConfiguration; for now I've simply used new RuntimeConfiguration(); and this requires use staabm\PHPStanDba\QueryReflection\RuntimeConfiguration.

I'd move the Installation section above Usage, as you would need to do this first.
Probably remove/rename the Usage heading, as this is part of Installation or could be called Setup.
I'd create the phpstan-bootstrap.php (named like this to match the example given in the phpstan documentation, and not conflict with any existing bootstrap.php files); and I'd do this as the first step after composer require, so the file exists before trying to explain that a file (of unknown content) needs to be added to the phpstan config file... you can also add a note that other bootstrap options are below, e.g. if you "don't want to depend on a database at PHPStan analysis time".
Then I'd do the phpstan.neon config update in a second (single) step, and use a basic example (because people are lazy)... saying something like "either create or update your phpstan.neon file so that it uses bootstrapFiles and it includes dba.neon, e.g."

parameters:
  level: 9
  paths:
    - public
  bootstrapFiles:
    - phpstan-bootstrap.php

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

Then you could add a "using" step, e.g.

./vendor/bin/phpstan analyse -c phpstan.neon

demo: custom sql based database access layer

There was somewhere already a demo implementation for zf1, or? 🤔

PS: thanks for pushing this code checks and for take care of code quality for us all 👌

unexpected error in query analysis

refs composer/packagist#1232

    /**
     * @param string   $name Package name to find the dependents of
     * @param int|null $type One of Dependent::TYPE_*
     * @return int<0, max>
     */
    public function getDependentCount(string $name, ?int $type = null): int
    {
        $sql = 'SELECT COUNT(*) count FROM dependent WHERE packageName = :name';
        $args = ['name' => $name];
        if (null !== $type) {
            $sql .= ' AND type = :type';
            $args['type'] = $type;
        }

        return (int) $this->getEntityManager()->getConnection()->fetchOne($sql, $args);
    }

leads to

Query expects 1 placeholder, but 2 values are given.

no error expected

refactor out PdoExecuteTypeSpecifyingExtension prepared statement logic

make it available as API
allow re-use of the prepared statement handling/replacement logic for 3rd party phpstan-extensions

see #44

Question about Doctrine/DBAL

Hey there 👋

I found your project on twitter the other day and after looking into it, it looks very interesting. 👍 good work so far

we are using the Doctrine/DBAL connection in our projects and not directly the PHP classes and functions. Is this somehow also supported? Or are your interfered types again replaced by the annotations and types of DBAL itself?

keep up the good work 💪

Prepared statements with camelCased named placeholders

Not sure if related to another of the open issues tbh, but I tried this as it's IMO very promising and had to go around Doctrine DBAL for now to test it out but I got a rather unexpected result:

        $sql = '
            SELECT v.normalizedVersion, d.data
            FROM package_version v
            INNER JOIN download d ON d.id=v.id AND d.type = :versionType
            WHERE v.package_id = :package AND v.development = 0 AND v.normalizedVersion LIKE :majorVersion
        ';

        /** @var \Doctrine\DBAL\Driver\PDO\Connection $pdo */
        $pdo = $this->getEntityManager()->getConnection()->getWrappedConnection();
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['package' => $package->getId(), 'versionType' => Download::TYPE_VERSION, 'majorVersion' => $majorVersion . '.%']);

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Line 37 is the execute call.

  37     Query expects placeholder :major, but it is missing from values given to execute().
  37     Query expects placeholder :version, but it is missing from values given to execute().
  37     Value :majorVersion is given to execute(), but the query does not contain this placeholder.
  37     Value :versionType is given to execute(), but the query does not contain this placeholder.

I have no idea why the placeholders :majorVersion/:versionType became :major/:version. It looks like it stopped parsing as soon as it encountered a capital letter, so it smells to me of a regex that's not case insensitive or something.. But I couldn't find anything in the code.

add strict-mode

error on non literal query strings args

See

———

report any non-simulatable input as error (as we already do in debug-mode)

Return type for integers should be numeric-string

This code here:

        $result = $this->getEntityManager()->getConnection()->fetchOne('SELECT verified FROM vendor WHERE name = :vendor', ['vendor' => $vendor]);

        return $result === '1';

Triggered:

Strict comparison using === between int<-128, 127> and '1' will always evaluate to false.

At least with doctrine connection like this it appears to really return '1'. I am not sure if there are cases where PDO will return the proper integers, or perhaps with Doctrine when using query builder it may have smarter return values. Would need to all be verified.

PDO: `fetch()` may return false if there no is no data even in exception mode

For code like this:

    $stmt = Container::Database()->query('SELECT AppID FROM Apps LIMIT 5');
    while ($row = $stmt->fetch()) {
        var_dump($row);
    }

It will correctly iterate over the rows and it will return false once there is no more data.

However phpstan-dba assumes the loop never exits:

  7      While loop condition is always true.
  11     Unreachable statement - code above always terminates.

Create a REPL

Comparable to phpstan.org online sandbox

Prepared statement: Question mark parameters

Currently phpstan-dba supports named parameters (thank you), but it does not support question mark parameters:

$statement = $pdo->prepare('SELECT * FROM user WHERE id = :id');
$statement->execute(['id' => 1]);

$statement = $pdo->prepare('SELECT * FROM user WHERE id = ?');
$statement->execute([1]);

First one is fine, second one "Value 0 is given to execute(), but the query does not containt this placeholder."

I can see you have a $parameters array in PdoStatementExecuteErrorMethodRule::checkParameterValues(), is it worth using something like the following to determine if it's using keyed arrays:

(count(array_filter(array_keys($array), 'is_string')) > 0)

https://stackoverflow.com/a/4254008/6632

Also, typo with "containt" (says he with a keyboard who happens to have a nearly broken "t" key, so can you send them my way).

remove rector dependency and use symplify/astral instead

depends on deprecated-packages/symplify#3876

Wrong type inferrence

https://github.com/composer/packagist/blob/2f8c458bcf24d1d47c7aa6a6eecac8995518d4c6/src/Entity/PhpStatRepository.php#L231

we conclude a return type of array{int}|false which seems wrong.

we should either return the correct type - or conclude the query is too complex to analyze

	private static function stripTraillingLimit(string $queryString): ?string
	{
	// XXX someday we will use a proper SQL parser,
	// which would also allow us to support even more complex expressions like SELECT .. LIMIT X, Y FOR UPDATE
	return preg_replace('/\sLIMIT\s+["\']?\d+["\']?\s(,\s["\']?\d["\']?)?\s*$/i', '', $queryString);
	}

	public function aggregateFunctions(PDO $pdo)
	{
	$query = 'SELECT MAX(adaid), MIN(adaid), COUNT(adaid), AVG(adaid) FROM ada WHERE adaid = 1';
	$stmt = $pdo->query($query, PDO::FETCH_ASSOC);
	assertType('PDOStatement<array{MAX(adaid): int<-2147483648, 2147483647>\|null, MIN(adaid): int<-2147483648, 2147483647>\|null, COUNT(adaid): int, AVG(adaid): float\|null}>', $stmt);
	}

staabm / phpstan-dba Goto Github PK

phpstan-dba's Introduction

phpstan-dba: PHPStan based SQL static analysis and type inference for the database access layer

Talks

DEMO

💌 Support phpstan-dba

Installation

Read more

phpstan-dba's People

Contributors

Stargazers

Watchers

Forkers

phpstan-dba's Issues

Recommend Projects

Recommend Topics

Recommend Org

`phpstan-dba`: PHPStan based SQL static analysis and type inference for the database access layer