Bug->Doc #81173 [Nab]: Fatal error. PHP7-8 PG Prepared statement name collision
| From: | kachalin dot alexey at gmail dot com | Date: | Mon, 21 Jun 2021 18:09:05 +0000 |
| Subject: | Bug->Doc #81173 [Nab]: Fatal error. PHP7-8 PG Prepared statement name collision | ||
| References: | 1 | Groups: | php.doc.bugs |
| Request: | Send a blank email to doc-bugs+get-18897@lists.php.net to get a copy of this message | ||
Edit report at https://bugs.php.net/bug.php?id=81173&edit=1
ID: 81173
User updated by: kachalin dot alexey at gmail dot com
Reported by: kachalin dot alexey at gmail dot com
Summary: Fatal error. PHP7-8 PG Prepared statement name
collision
Status: Not a bug
-Type: Bug
+Type: Documentation Problem
Package: PostgreSQL related
-Operating System: Ubuntu
+Operating System: any
PHP Version: 8.0.7
Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
Okay, I understood.
Maybe be it's good idea to add a notice in PHP documentation, something like "The prepared
statement name can be truncated. Default limit set to 63 character."
At least people will be aware about the limit. How do you think?
In some cases prepared statment warning don't showed, but can returns a malformed data.
The prepared statement 'SELECT 222 as result_1' returns 111. No warrning, no error.
<?php
$host = '';
$db = '';
$port = '';// 5432
$user = '';
$pass = '';
$connectString = "host=$host port=$port dbname=$db user=$user password=$pass";
$pg_pconnect = pg_pconnect($connectString);
$string63 = '5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87';
//$string63 = 'smallLenthSQL';// Uncoment for expected result.
$sqlPreparedNameA = $string63 . '_A';
$sqlPreparedNameB = $string63 . '_B';
$sqlPreparedBodyA = 'SELECT 111 as result_1';
$sqlPreparedBodyB = 'SELECT 222 as result_1';
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameA, $sqlPreparedBodyA);
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameB, $sqlPreparedBodyB);
$pg_executeA = pg_execute($pg_pconnect, $sqlPreparedNameA, []);
$pg_executeB = pg_execute($pg_pconnect, $sqlPreparedNameB, []);
$resultA = pg_fetch_all($pg_executeA);
$resultB = pg_fetch_all($pg_executeB);
var_dump($resultA);
var_dump($resultB);
Previous Comments:
------------------------------------------------------------------------
[2021-06-21 09:49:56] cmb@php.net
Yes, that is a general limitation of PostgreSQL[1], and there is
nothing we can do about it. If you need to have longer
identifiers, increase the value of NAMEDATALEN.
[1] <https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>
------------------------------------------------------------------------
[2021-06-19 20:12:07] requinix@php.net
I don't see anything in ext/pgsql or libpq, source or documentation, that says prepared
statement names are limited to 63/64 characters. This may be a server-side limitation.
------------------------------------------------------------------------
[2021-06-19 11:57:13] kachalin dot alexey at gmail dot com
Description:
------------
Brifely
Prepared SQL name collision, because the name implicitly truncated to 63 first characters of given
name.
Current behavior:
Fatal errors and warnings.
Desirable behavior:
No any error or warning.
Uncomment a "//$string63 = 'smallLengthSQL'; line to check execution with any error.
Affected version.
All 7 and 8.
Latest check on 8.1.
Notice
Don't forget to put your PG server credentials for connection.
Test script:
---------------
<?php
$host = '';
$port = '';// 5432
$db = '';
$user = '';
$pass = '';
$connectString = "host=$host port=$port dbname=$db user=$user password=$pass";
$pg_pconnect = pg_pconnect($connectString);
$string63 = '5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87';// length -
63
//$string63 = 'smallLengthSQL';// Uncomment for expected result.
$sqlPreparedNameA = $string63 . '_A';// length - 65 for error case.
$sqlPreparedNameB = $string63 . '_B';// length - 65 for error case.
$sqlPreparedBodyA = 'SELECT $1 as result_1' ;
$sqlPreparedBodyB = 'SELECT $1 as result_1, $2 as result_2';
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameA, $sqlPreparedBodyA);
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameB, $sqlPreparedBodyB);
$pg_executeA = pg_execute($pg_pconnect, $sqlPreparedNameA, array("Result A1"
));
$pg_executeB = pg_execute($pg_pconnect, $sqlPreparedNameB, array("Result B1", "Result
B2"));
$resultA = pg_fetch_all($pg_executeA);
$resultB = pg_fetch_all($pg_executeB);
var_dump($resultA);
var_dump($resultB);
Expected result:
----------------
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(9) "Result A1"
}
}
array(1) {
[0]=>
array(2) {
["result_1"]=>
string(9) "Result B1"
["result_2"]=>
string(9) "Result B2"
}
}
Actual result:
--------------
Warning: pg_prepare(): Query failed: ERROR: prepared statement
"5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists in
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared
statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
in
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result,
bool given in
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=81173&edit=1