Incorrect NUMERIC value returned from Firebird PDO query in PHP 8.1 #9971

clarumedia · 2022-11-18T16:07:36Z

Description

The following code:

//$pdo already connected with no dialect specified (Defaults to 3 ?)

$sql="SELECT SOME_INDEX,
SOME_NUMERIC_FIELD, 
CAST (SOME_NUMERIC_FIELD AS VARCHAR(255)) AS SOME_NUMERIC_FIELD_STRING
FROM MYTABLE WHERE SOME_INDEX='1234' ";

$objPDOStatement=$pdo->query($sql);

foreach($objPDOStatement as $arrRow){
	print_r($arrRow);
	exit;
}

Resulted in this output in PHP 8.1.12 :

Array
(
    [SOME_INDEX] => 1234
    [0] => 1234
    [SOME_NUMERIC_FIELD] => 0.00000000
    [1] => 0.00000000
    [SOME_NUMERIC_FIELD_STRING] => 1.00000000            
    [2] => 1.00000000            
)

But I expected this output instead as per PHP 7.4:

Array
(
    [SOME_INDEX] => 1234
    [0] => 1234
    [SOME_NUMERIC_FIELD] => 1.00000000
    [1] => 1.00000000
    [SOME_NUMERIC_FIELD_STRING] => 1.00000000            
    [2] => 1.00000000            
)

Both report LI-V6.3.5.33220 Firebird 3.0 from a call to
$pdo->getAttribute(PDO::ATTR_CLIENT_VERSION)

Makes no difference if I set
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

PHP Version

PHP 8.1.12

Operating System

Ubuntu 20.04

The text was updated successfully, but these errors were encountered:

cmb69 · 2022-11-18T16:47:01Z

Hmm, there is https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt, which queries a NUMERIC(15,2) row, and that works (also locally for me).

Can you please specify the exact table definition – ideally having a complete script which creates and fills the table, and then does the query, printing the resulting row via var_dump()?

clarumedia · 2022-11-18T17:25:24Z

Test Results for the script at https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt

Additional comment added 19/11/2022: My apologies, I neglected to mention that I had to use "dialect=1;" to get the test to work in either PHP7.4 or 8.1.12. My previous scripts in PHP7.4 were only ever doing selects.
"Metadata update statement is not allowed by the current database SQL dialect 1"

PHP 7.4

string(5) "-1.00"
string(5) "-0.99"
string(5) "-1.01"

PHP 8.1.12 (php8.1-interbase 8.1.12-1+ubuntu20.04.1+deb.sury.org+1)

string(4) "0.00"
string(11) "20615843.02"
string(12) "-10307921.51"

hormus · 2022-11-19T04:34:45Z

Hi @clarumedia please Test phpt code use pdo_firebird extension installed and enabled not interbase extension
https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/skipif.inc
https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/testdb.inc
and https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt

the fix seems to me never came for the shortstop extension please @cmb69 can you confirm if you are not busy? Thank you

cmb69 · 2022-11-19T11:31:26Z

string(4) "0.00"
string(11) "20615843.02"
string(12) "-10307921.51"

This is seriously screwed. What's the version of your database server?

clarumedia · 2022-11-19T12:29:23Z

@cmb69

print $dbh->getAttribute(PDO::ATTR_SERVER_INFO);

PHP 7.4

Server Version: Firebird/Windows/Intel/i386 (access method), version "WI-V2.0.1.12855 Firebird 2.0" Firebird/Windows/Intel/i386 (remote server), version "WI-V2.0.1.12855 Firebird 2.0/tcp (DBSERVERHOSTNAME)/P10" Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-V3.0.5.33220 Firebird 3.0/tcp (arm-lnx-1)/P10" on disk structure version 11.0

PHP 8.1

Server Version: Firebird/Windows/Intel/i386 (access method), version "WI-V2.0.1.12855 Firebird 2.0" Firebird/Windows/Intel/i386 (remote server), version "WI-V2.0.1.12855 Firebird 2.0/tcp (DBSERVERHOSTNAME)/P10" Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-V3.0.5.33220 Firebird 3.0/tcp (arm-lnx-1)/P10" on disk structure version 11.0

clarumedia · 2022-11-19T12:53:31Z

@hormus Am I missing something on setting up pdo_firebird on Ubuntu 20.04 ? I had installed php8.1-interbase because it was all that was listed ?

apt-cache search php firebird
php7.4-interbase - Interbase module for PHP
php5.6-interbase - Interbase module for PHP
php7.0-interbase - Interbase module for PHP
php7.1-interbase - Interbase module for PHP
php7.2-interbase - Interbase module for PHP
php7.3-interbase - Interbase module for PHP
php8.0-interbase - Interbase module for PHP
php8.1-interbase - Interbase module for PHP
php8.2-interbase - Interbase module for PHP

PDO Connection

My PDO connection string is like this:

"firebird:dbname=DBSERVERHOSTNAME:c:\ProgramData\Db\mydatabase.FDB;dialect=1;"

The incorrect numeric values occur whether dialect=1 is used or not, but using dialect=1 is the only way to get the above test script to work in either PHP7.4 or PHP8.1 "Metadata update statement is not allowed by the current database SQL dialect 1"

https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt

clarumedia · 2022-11-19T13:18:08Z

Test results for https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt without using dialect=1

PHP 7.4

Warning: PDO::exec(): SQLSTATE[HY000]: General error: -817 Dynamic SQL Error SQL error code = -817 Metadata update statement is not allowed by the current database SQL dialect 1 in /opt/firebird_test.php on line 16

Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /opt/firebird_test.php on line 17

Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /opt/firebird_test.php on line 18

Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /opt/firebird_test.php on line 19

Warning: PDO::prepare(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 21 in /opt/firebird_test.php on line 24

Fatal error: Uncaught Error: Call to a member function execute() on bool in /opt/firebird_test.php:25
Stack trace:
#0 {main}
thrown in /opt/firebird_test.php on line 25

PHP 8.1

PHP Fatal error: Uncaught Error: Call to a member function execute() on bool in /opt/firebird_test.php:25
Stack trace:
#0 {main}
thrown in /opt/firebird_test.php on line 25

cmb69 · 2022-11-19T13:38:41Z

Server Version: Firebird/Windows/Intel/i386 (access method), version "WI-V2.0.1.12855 Firebird 2.0" Firebird/Windows/Intel/i386 (remote server), version "WI-V2.0.1.12855 Firebird 2.0/tcp (DBSERVERHOSTNAME)/P10" Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-V3.0.5.33220 Firebird 3.0/tcp (arm-lnx-1)/P10" on disk structure version 11.0

That looks like a Firebird 2.0 server, but that version is discontinued since 12 Apr 2012. Please try with a Firebird 3.0 or 4.0 server (at least for testing purposes).

clarumedia · 2022-11-19T17:41:07Z

Server Version: Firebird/Windows/Intel/i386 (access method), version "WI-V2.0.1.12855 Firebird 2.0" Firebird/Windows/Intel/i386 (remote server), version "WI-V2.0.1.12855 Firebird 2.0/tcp (DBSERVERHOSTNAME)/P10" Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-V3.0.5.33220 Firebird 3.0/tcp (arm-lnx-1)/P10" on disk structure version 11.0

That looks like a Firebird 2.0 server, but that version is discontinued since 12 Apr 2012. Please try with a Firebird 3.0 or 4.0 server (at least for testing purposes).

@cmb69

Test results for firebird3.0-server on Ubuntu 20.04 https://unixcop.com/install-firebird-ubuntu/
Database created at /var/lib/firebird/3.0/data/second_database.fdb with Dialect 1 using isql-fb command

SET SQL DIALECT 1;
CREATE DATABASE '/var/lib/firebird/3.0/data/second_database.fdb'
USER 'SYSDBA' PASSWORD '1234';

chmod 777 /var/lib/firebird/3.0/data/second_database.fdb

Dialect 1

Test Script: https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt
PDO connection string: firebird:dbname=localhost:/var/lib/firebird/3.0/data/second_database.fdb;dialect=1;

PHP 7.4

string(5) "-1.00"
string(5) "-0.99"
string(5) "-1.01"

PHP 8.1

string(4) "0.00"
string(11) "20615843.02"
string(12) "-10307921.51"

No Dialect

Test Script: https://github.com/php/php-src/blob/PHP-8.1/ext/pdo_firebird/tests/bug_64037.phpt
PDO connection string: firebird:dbname=localhost:/var/lib/firebird/3.0/data/second_database.fdb;

PHP 7.4 and PHP 8.1

PHP Warning: PDO::exec(): SQLSTATE[HY000]: General error: -817 Dynamic SQL Error SQL error code = -817 Metadata update statement is not allowed by the current database SQL dialect 1 in /home/chris/x.php on line 16
PHP Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /home/chris/x.php on line 17
PHP Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /home/chris/x.php on line 18
PHP Warning: PDO::exec(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 13 in /home/chris/x.php on line 19
PHP Warning: PDO::prepare(): SQLSTATE[HY000]: General error: -204 Dynamic SQL Error SQL error code = -204 Table unknown PRICE At line 1, column 15 in /home/chris/x.php on line 24
PHP Fatal error: Uncaught Error: Call to a member function execute() on bool in /home/chris/x.php:25
Stack trace:
#0 {main}
thrown in /home/chris/x.php on line 25

cmb69 · 2022-11-21T11:45:36Z

SET SQL DIALECT 1;
CREATE DATABASE '/var/lib/firebird/3.0/data/second_database.fdb'
USER 'SYSDBA' PASSWORD '1234';

Thanks! This way I can reproduce the issue.

cmb69 · 2022-11-21T13:05:04Z

This way I can reproduce the issue.

Well, not necessarily the exact issue you've reported, but at least I have some ideas. First, dialect 1 stores NUMERIC(15,2) as double (facepalm), but as of PHP 8.1.0, we only expect SQL_SHORT, SQL_LONG or SQLI_INT64 when .sqlscale < 0. This triggers an assertion failure for me (debug build). Just removing the EMPTY_SWITCH_DEFAULT_CASE() makes the test pass. A minimal fix for that:

 ext/pdo_firebird/firebird_statement.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/ext/pdo_firebird/firebird_statement.c b/ext/pdo_firebird/firebird_statement.c
index d2b0a720b6..a4fea65c95 100644
--- a/ext/pdo_firebird/firebird_statement.c
+++ b/ext/pdo_firebird/firebird_statement.c
@@ -382,6 +382,9 @@ static int firebird_stmt_get_col(
 				case SQL_INT64:
 					n = *(ISC_INT64*)var->sqldata;
 					break;
+				case SQL_DOUBLE:
+					n = 0;
+					break;
 				EMPTY_SWITCH_DEFAULT_CASE()
 			}

However, I doubt that this fixes the issue for you, but if possible, give it a try.

There is at least another issue: unscaled SQL_INT64 columns are always treated as PDO_PARAM_INT on 64bit architectures, although ::getColumnMeta() always reports such columns as PDO_PARAM_STR. This is unlike to be the cause of the reported issue. That is not the case. Everything fine in this regard.

clarumedia · 2022-11-21T13:57:40Z

@cmb69 It's been a very long time since I've compiled PHP. I'd be very grateful of any pointers for testing your fix ?
Thank you very much !

cmb69 · 2022-11-21T14:07:58Z

Basically, you need to

./buildconf
./configure --disable-all --disable-cgi --disable-phpdbg --enable-pdo --with-pdo-firebird
make

Maybe you need to specify the installation dir of Firebird (--with-pdo-firebird=DIR). And maybe you need to install the firebird-dev package first (apt-get install firebird-dev). If anything goes wrong, consult config.log for details.

clarumedia · 2022-11-22T09:21:02Z

@cmb69 Which branch are you on ?

cmb69 · 2022-11-22T10:01:12Z

I tested with PHP-8.1.

clarumedia · 2022-11-29T12:51:57Z

@cmb69 Apologies for the delay.

Test results after compiling PHP branch 8.1 with your modification (above) to ext/pdo_firebird/firebird_statement.c and then running the test script with the compiled PHP in php-src/sapi/cli/php

Connecting with:firebird:dbname=localhost:/var/lib/firebird/3.0/data/second_database.fdb;dialect=1
string(5) "-1.00"
string(5) "-0.99"
string(5) "-1.01"

Looks like that's fixed it. :-)

Dialect 1 databases store and transfer `NUMERIC(15,2)` values as doubles, which we need to cater to in `firebird_stmt_get_col()` to avoid `ZEND_ASSUME(0)` to ever be triggered, since that may result in undefined behavior. Since adding a regression test would require to create a dialect 1 database, we go without it.

cmb69 · 2022-11-29T14:40:29Z

Thanks for checking, @clarumedia!

Looks like that's fixed it. :-)

Interesting! So apparently the issue was "only" the ZEND_ASSUME(0) which caused a compiler "optimization" which lead to reading of uninitialized memory. I've just submitted a PR with a slightly simplified solution; maybe you can try that, too.

clarumedia · 2022-11-29T15:25:21Z

@cmb69

Test results for your simplified fix: https://github.com/php/php-src/pull/10021/files

Connecting with:firebird:dbname=localhost:/var/lib/firebird/3.0/data/second_database.fdb;dialect=1
string(5) "-1.00"
string(5) "-0.99"
string(5) "-1.01"

Looking good :-)

* PHP-8.1: Fix GH-9971: Incorrect NUMERIC value returned from PDO_Firebird

* PHP-8.2: Fix GH-9971: Incorrect NUMERIC value returned from PDO_Firebird

clarumedia · 2022-11-30T12:08:05Z

Thank you @cmb69 :-)

clarumedia added Bug Status: Needs Triage labels Nov 18, 2022

clarumedia changed the title ~~Incorrect NUMERIC value returned from PDO query in PHP 8.1~~ Incorrect NUMERIC value returned from Firebird PDO query in PHP 8.1 Nov 18, 2022

cmb69 added the Extension: pdo_firebird label Nov 18, 2022

cmb69 added Status: Needs Feedback and removed Status: Needs Triage labels Nov 18, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 18, 2022

cmb69 added Status: Needs Feedback and removed Status: Needs Triage labels Nov 19, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 19, 2022

cmb69 added Status: Needs Feedback and removed Status: Needs Triage labels Nov 19, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 19, 2022

cmb69 added Status: Verified and removed Status: Needs Triage labels Nov 21, 2022

cmb69 self-assigned this Nov 21, 2022

cmb69 added Status: Needs Feedback and removed Status: Verified labels Nov 21, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 21, 2022

cmb69 added Status: Needs Feedback and removed Status: Needs Triage labels Nov 21, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 22, 2022

cmb69 added Status: Needs Feedback and removed Status: Needs Triage labels Nov 22, 2022

github-actions bot added Status: Needs Triage and removed Status: Needs Feedback labels Nov 29, 2022

cmb69 added Status: Verified and removed Status: Needs Triage labels Nov 29, 2022

cmb69 linked a pull request Nov 29, 2022 that will close this issue

Fix GH-9971: Incorrect NUMERIC value returned from PDO_Firebird #10021

Closed

cmb69 added a commit that referenced this issue Nov 30, 2022

Merge branch 'PHP-8.1' into PHP-8.2

e29ca36

* PHP-8.1: Fix GH-9971: Incorrect NUMERIC value returned from PDO_Firebird

cmb69 closed this as completed in 44377c3 Nov 30, 2022

cmb69 added a commit that referenced this issue Nov 30, 2022

Merge branch 'PHP-8.2'

7402d90

* PHP-8.2: Fix GH-9971: Incorrect NUMERIC value returned from PDO_Firebird

Incorrect NUMERIC value returned from Firebird PDO query in PHP 8.1 #9971

Incorrect NUMERIC value returned from Firebird PDO query in PHP 8.1 #9971

Comments

clarumedia commented Nov 18, 2022

Description

PHP Version

Operating System

cmb69 commented Nov 18, 2022

Uh oh!

clarumedia commented Nov 18, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

hormus commented Nov 19, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cmb69 commented Nov 19, 2022

Uh oh!

clarumedia commented Nov 19, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

clarumedia commented Nov 19, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

clarumedia commented Nov 19, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cmb69 commented Nov 19, 2022

Uh oh!

clarumedia commented Nov 19, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Dialect 1

No Dialect

Uh oh!

cmb69 commented Nov 21, 2022

Uh oh!

cmb69 commented Nov 21, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

clarumedia commented Nov 21, 2022

Uh oh!

cmb69 commented Nov 21, 2022

Uh oh!

clarumedia commented Nov 22, 2022

Uh oh!

cmb69 commented Nov 22, 2022

Uh oh!

clarumedia commented Nov 29, 2022

Uh oh!

cmb69 commented Nov 29, 2022

Uh oh!

clarumedia commented Nov 29, 2022

Uh oh!

clarumedia commented Nov 30, 2022

Uh oh!

clarumedia commented Nov 18, 2022 •

edited

Loading

hormus commented Nov 19, 2022 •

edited

Loading

clarumedia commented Nov 19, 2022 •

edited

Loading

clarumedia commented Nov 19, 2022 •

edited

Loading

clarumedia commented Nov 19, 2022 •

edited

Loading

clarumedia commented Nov 19, 2022 •

edited

Loading

cmb69 commented Nov 21, 2022 •

edited

Loading