Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN - Mailing list pgsql-bugs
| From | Alexey Kachalin |
|---|---|
| Subject | Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN |
| Date | |
| Msg-id | CAF9fLqtmtPqjLD06gnxFB_oLtx7SKm-gAe+7JNzXQG5iRb=utA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
|
| List | pgsql-bugs |
Hello,
I would like to report a bug.
# Title.
Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
# Description.
Prepared sql collision can occur when 2 conditions are met:
1. Prepared SQL name is longer than 63 characters.
2. Both sql names have the same first 63 characters.
This issue happens because Postgres silently truncates prepared SQL names to 63 characters.
According to documentation the length of 63 characters is defined in NAMEDATALEN constant.
In the second example two different SQLs return the same result. It's almost impossible to detect a problem.
A prepared SQL can return unpredictable results based on the time which SQL was prepared first.
Or return an error that is not related to the prepared SQL name.
Also it's possible to miss the warning message of double prepared SQL. Most of the time errors are related to a difference between collided SQL.
The roots of this behaviour are hard detectable on application level.
Is it possible to know which value was used at compilation time from application code?
# Desirable behaviour.
If an identifier exceeds the length defined in NAMEDATALEN an error is thrown, neither silently truncating an identifier.
# Version info
Postgres version 15.3
Php version: 8.2.6
#Code examples on PHP scripts.
--
I would like to report a bug.
# Title.
Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
# Description.
Prepared sql collision can occur when 2 conditions are met:
1. Prepared SQL name is longer than 63 characters.
2. Both sql names have the same first 63 characters.
This issue happens because Postgres silently truncates prepared SQL names to 63 characters.
According to documentation the length of 63 characters is defined in NAMEDATALEN constant.
In the second example two different SQLs return the same result. It's almost impossible to detect a problem.
A prepared SQL can return unpredictable results based on the time which SQL was prepared first.
Or return an error that is not related to the prepared SQL name.
Also it's possible to miss the warning message of double prepared SQL. Most of the time errors are related to a difference between collided SQL.
The roots of this behaviour are hard detectable on application level.
Is it possible to know which value was used at compilation time from application code?
# Desirable behaviour.
If an identifier exceeds the length defined in NAMEDATALEN an error is thrown, neither silently truncating an identifier.
# Version info
Postgres version 15.3
Php version: 8.2.6
#Code examples on PHP scripts.
<?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 = 'smallLengthSQL';// Uncomment for normal behaviour and expected result.
# Uncomment to check an example #1
//$sqlPreparedNameA = $string63 . '_A';
//$sqlPreparedNameB = $string63 . '_B';
//$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_prepareB = 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, $resultB);
//exit();
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
# Uncomment to check an example #2
$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_prepareB = 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, $resultB);
exit();
/*
*
* Expected output
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "222"
}
}
*
*
*
* Gotten output
<br />
<b>Warning</b>: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
*/
$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 = 'smallLengthSQL';// Uncomment for normal behaviour and expected result.
# Uncomment to check an example #1
//$sqlPreparedNameA = $string63 . '_A';
//$sqlPreparedNameB = $string63 . '_B';
//$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_prepareB = 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, $resultB);
//exit();
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
# Uncomment to check an example #2
$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_prepareB = 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, $resultB);
exit();
/*
*
* Expected output
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "222"
}
}
*
*
*
* Gotten output
<br />
<b>Warning</b>: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
*/
Best regards,
Alexey Kachalin.
Alexey Kachalin.
pgsql-bugs by date: