I'm updating a project from PHP 5.6 to PHP 7.0 and with PHP 7.0 started getting database errors from postgresql (version 9.5 if that makes any difference) telling that the inserted data violates a constraint check. Run the same code again with PHP 5.6 and there was no violation. Removed all the checks to see what the parameters actually were and it seemed there were empty strings where NULL values were expected. I found a few old and fixed PHP bugs where pg_query() and pg_query_params() converted NULL values to empty strings, but this is different. NULL only gets converted to an empty string if the params array was looped over with a reference.
code demonstrating the problem:
$sql = "INSERT INTO params_test (value, details) VALUES ($1, $2)";
$params = array(null, "insert before looping with a reference");
var_dump($params); echo "<br>";
var_dump(is_null($params[0])); echo "<br>";
$result = pg_query_params($this->connection, $sql, $params);
$params2 = array(null, "insert after looping with a reference");
foreach ($params2 as &$p) {
// doing nothing
}
unset($p);
var_dump($params2); echo "<br>";
var_dump(is_null($params2[0])); echo "<br>";
$result = pg_query_params($this->connection, $sql, $params2);
echo "NULL equals NULL: "; var_dump($params[0] === $params2[0]); echo "<br>";
output:
array(2) { [0]=> NULL [1]=> string(36) "insert before looping with reference" }
bool(true)
array(2) { [0]=> NULL [1]=> string(67) "insert after looping with a reference" }
bool(true)
NULL equals NULL: bool(true)
postgresql log:
2016-04-15 09:28:04 EEST LOG: execute <unnamed>: INSERT INTO params_test (value, details) VALUES ($1, $2)
2016-04-15 09:28:04 EEST DETAIL: parameters: $1 = NULL, $2 = 'insert before looping with a reference'
2016-04-15 09:28:04 EEST LOG: execute <unnamed>: INSERT INTO params_test (value, details) VALUES ($1, $2)
2016-04-15 09:28:04 EEST DETAIL: parameters: $1 = '', $2 = 'insert after looping with a reference'
I would very much appreciate if anyone could explain what is happening here...
Reported this as a bug and it is fixed already. See https://bugs.php.net/bug.php?id=72028