dbal: SQLLogger does not log actual params used in query

Bug Report

Q	A
BC Break	no
Version	2.12.1

Summary

There are scenarios when params logged by SQLLogger are not the same as params actually used in query. It’s because query, params and types are logged before preparing statement, in which params are bound (and while binding they can be typecasted which ends with different value than the one which was logged).

Initially reported in Symfony, after discussion in DoctrineBundle, then here after @ostrolucky’s request.

Current behaviour

When using query builder with something like:

$qb->andWhere("foo.value IN (:foo_values)");
$qb->setParameter("foo_values", ['bar'], Connection::PARAM_INT_ARRAY);

Doctrine data collector in Symfony profiler shows “runnable query” with foo.values IN ('bar') but actually database query contains foo.values IN (0) because of Connection::PARAM_INT_ARRAY param type. It is very misleading, because looking at the profiler you may think the query is correct (even though the result is not). Profiler displays data from collector, which works on top of SQLLogger, so DBAL is the source of query, params and types available later in the profiler.

More context can be found in doctrine/DoctrineBundle#1312

How to reproduce

Here’s reproducer created by @Nyholm → https://github.com/Nyholm/sf-issue-40566

Expected behaviour

In the end user should know what params were actually used in query because it would allow to find bugs faster. Personally I’ve lost lot of time because I’ve seen in profiler wrong “runnable query”, which was returning different set of values than actual query in the app. I had to enable and watch general log in MySQL to see what’s actually executed and then was able to find source of the problem.