pgsql library, null-by-reference handling in pg_execute, pg_send_query_params, pg_send_execute

[georgebarbarosie]

Description

There is a bug in pg_execute, pg_send_query_params and pg_send_execute in the way it handles null values that makes it less reliable and error prone in a very counter-intuitive way and at the same time diverge from the behaviour of pg_query_params. The code below should offer a complete explanation.

<?php

$DSN = getenv('DSN');
$conn = pg_connect($DSN);

pg_prepare($conn, 'test', 'SELECT $1::text IS NULL;');

// method 1, pg_execute
$val = null;
$res = pg_execute($conn, 'test', [$val]);
echo "pg_execute, null value: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

$res = pg_execute($conn, 'test', [&$val]);
echo "pg_execute, null value by reference: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

// method 2, pg_query_params
$res = pg_query_params($conn, 'SELECT $1::text IS NULL;', [$val]);
echo "pg_query_params, null value: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

$res = pg_query_params($conn, 'SELECT $1::text IS NULL;', [&$val]);
echo "pg_query_params, null value by reference: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);


// method 3, pg_send_query_params
$res = pg_send_query_params($conn, 'SELECT $1::text IS NULL;', [$val]);
pg_consume_input($conn);
$res = pg_get_result($conn);
echo "pg_send_query_params, null value: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

$res = pg_send_query_params($conn, 'SELECT $1::text IS NULL;', [&$val]);
pg_consume_input($conn);
$res = pg_get_result($conn);
echo "pg_send_query_params, null value by reference: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

// method 4, pg_send_prepare, pg_send_execute
pg_send_execute($conn, 'test', [$val]);
pg_consume_input($conn);
$res = pg_get_result($conn);
echo "pg_send_execute, null value: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

pg_send_execute($conn, 'test', [&$val]);
pg_consume_input($conn);
$res = pg_get_result($conn);
echo "pg_send_execute, null value by reference: " . pg_fetch_result($res, 0, 0) . "\n";
pg_free_result($res);

pg_close($conn);

Output, tested both on PHP 8.2.13 and PHP 8.3.2:

pg_execute, null value: t
pg_execute, null value by reference: f
pg_query_params, null value: t
pg_query_params, null value by reference: t
pg_send_query_params, null value: t
pg_send_query_params, null value by reference: f
pg_send_execute, null value: t
pg_send_execute, null value by reference: f

I strongly suspect that ZVAL_DEREF(tmp) here:

php-src/ext/pgsql/pgsql.c

Line 1107 in cd66fcc

ZVAL_DEREF(tmp);

is what makes pg_query_params behave correctly, and is missing in the params preparation of the other functions.

PHP Version

PHP 8.2.13, PHP 8.3.2

Operating System

Debian GNU/Linux 11 (bullseye), NixOS 24.05 (Uakari)

[devnexen]

Nice catch ! Would you mind create a PR ? If not you ll get due credit either way.

[georgebarbarosie]

Will do