php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #77764 Binding of SQLT_NUM variable resolves as NULL in SQL
Submitted: 2019-03-18 14:11 UTC Modified: 2021-10-18 14:36 UTC
Votes:1
Avg. Score:1.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: m dot a dot ogarkov at gmail dot com Assigned: sixd (profile)
Status: Assigned Package: OCI8 related
PHP Version: 7.3.3 OS: Linux
Private report: No CVE-ID: None
View Developer Edit
 [2019-03-18 14:11 UTC] m dot a dot ogarkov at gmail dot com 
Description:
------------
When binding with SQLT_NUM type, we get NULL in sql/plsql.
Variables is converted to INT, why int? 

Test script:
---------------
<?php
declare(strict_types=1);

$handle = oci_connect(getenv("DB_USER"), getenv("DB_PASSWORD"), getenv("DB_HOST"));
$var = 15.123;
$st = oci_parse($handle, "SELECT :0 FROM DUAL");
oci_bind_by_name($st, ":0", $var, -1, SQLT_NUM);
oci_execute($st);
var_dump(oci_fetch_array($st));
var_dump($var);


Expected result:
----------------
we should get NUMBER sql type:
"15.123" => (float) 15.123
"15" => (float) 15

Actual result:
--------------
array(2) {
  [0]=>
  NULL
  [":0"]=>
  NULL
}
int(15)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-03-26 19:55 UTC] camporter1 at gmail dot com 
The basic issue is that SQLT_NUM is treated just like SLQT_INT, which allows long values only.

For proper handling of doubles (which involves behind the scenes changing them into a string) I'd recommend using SQLT_LNG, or not providing the type at all which does the same thing.
 [2021-09-04 12:19 UTC] php-oci8 dot pkoch at dfgh dot net 
oci_bind_by_name() should reject $type==SQLT_NUM.

Bind-type SQLT_NUM will ask oci8 to store/fetch an integer value into/from a 21 byte array where the integer is stored in the oracle-internal NUMBER-format.

But the pointer that is used with SQLT_NUM-binds points to an 8-byte integer value (zend_long). It's unpredictable what happens in this case. OCI8 will write 21 bytes into a zend_long-value. This overwrites 13 bytes immediately after the zend_long value and the zend_long-value will have a wrong value.

On my machine

$conn=oci_new_connect('scott','tiger','ORCL');
$stat=oci_parse($conn, "begin :out:=123; end;");
oci_bind_by_name($stat, ":out", $result, -1, SQLT_NUM);
oci_execute($stat);
var_dump($result);

results in:

int(1573570)

I think the SQLT_NUM-implementation is missing and SQLT_NUM shoud be rejected until someone has written a conversion-routine between PHP-double and Oracle-NUMBER format.
 [2021-10-18 14:36 UTC] cmb@php.net
-Assigned To: +Assigned To: sixd
 [2021-10-18 14:36 UTC] cmb@php.net 
> OCI8 will write 21 bytes into a zend_long-value.

This would be *very* bad.  Chris, could you please have a look at
this?

> I think the SQLT_NUM-implementation is missing and SQLT_NUM
> shoud be rejected until someone has written a conversion-routine
> between PHP-double and Oracle-NUMBER format.

SQLT_NUM is apparently not properly implemented, but instead of
having the proper conversion, it might be okay to work with the
binary data provided as string.  One may still do the conversion
in PHP.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Wed Jul 02 13:01:34 2025 UTC