php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #81173 Fatal error. PHP7-8 PG Prepared statement name collision
Submitted: 2021-06-19 11:57 UTC Modified: 2021-06-21 18:18 UTC
Votes:3
Avg. Score:3.7 ± 0.9
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: kachalin dot alexey at gmail dot com Assigned:
Status: Open Package: PostgreSQL related
PHP Version: 8.0.7 OS: any
Private report: No CVE-ID: None
 [2021-06-19 11:57 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-06-19 20:12 UTC] 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-21 09:49 UTC] cmb@php.net
-Status: Open +Status: Not a bug -Assigned To: +Assigned To: cmb
 [2021-06-21 09:49 UTC] 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-21 18:09 UTC] kachalin dot alexey at gmail dot com
-Type: Bug +Type: Documentation Problem -Operating System: Ubuntu +Operating System: any
 [2021-06-21 18:09 UTC] kachalin dot alexey at gmail dot com
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);
 [2021-06-21 18:18 UTC] cmb@php.net
-Status: Not a bug +Status: Open -Assigned To: cmb +Assigned To:
 [2021-06-21 18:18 UTC] cmb@php.net
Adding this info to the PHP documentation should not hurt,
although it is not particulary about prepared statements, but
rather about identifiers in general.
 [2022-12-28 06:01 UTC] marlynrasavong at gmail dot com
the missing index notification is a relatively low-severity warning.
(https://www.c4yourself.ltd/)github.com
 [2023-05-24 17:33 UTC] kachalin dot alexey at gmail dot com
Well, it not a bug. Please close this issue.
An application programmer should truncate and secure a prepared SQL statements names.
To know the which value was used at compilation time for NAMEDATALEN use SQL
show max_identifier_length;
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Oct 07 21:01:27 2024 UTC