`PDO::PARAM_INT` is ignored in `pdo_oci` ext

[mvorisek]

Description

The following code:

<?php

$sql = 'SELECT :a a, dump(:a) a_type, :b b, dump(:b) b_type, '
    . '4 num, dump(4) num_type, \'4\' str, dump(\'4\') str_type, '
    . '(select 1 from dual where \'04\' = :a) a_where, '
    . '(select 1 from dual where \'04\' = 4) num_where '
    . 'FROM dual';
$num = 4;
$str = '4';

// test oci8 ext
$conn = oci_connect('system', 'atk4_pass', '127.0.0.1/xe');
$statement = oci_parse($conn, $sql);
oci_bind_by_name($statement, ':a', $num, -1, SQLT_INT);
oci_bind_by_name($statement, ':b', $str);
oci_execute($statement);
$row = oci_fetch_array($statement, OCI_ASSOC);
print_r($row);
var_dump($row['A_WHERE']);

// test pdo_oci
$pdo = new \PDO('oci:dbname=127.0.0.1/xe', 'system', 'atk4_pass');
$statement = $pdo->prepare($sql);
$statement->bindValue(':a', $num, \PDO::PARAM_INT);
$statement->bindValue(':b', $str);
$statement->execute();
$row = $statement->fetch(\PDO::FETCH_ASSOC);
print_r($row);
var_dump($row['A_WHERE']);

Expected output:

- actual
+ expected
 Array
 (
     [A] => 4
     [A_TYPE] => Typ=2 Len=2: 193,5
     [B] => 4
     [B_TYPE] => Typ=1 Len=1: 52
     [NUM] => 4
     [NUM_TYPE] => Typ=2 Len=2: 193,5
     [STR] => 4
     [STR_TYPE] => Typ=96 Len=1: 52
     [A_WHERE] => 1
     [NUM_WHERE] => 1
 )
 string(1) "1"
 Array
 (
     [A] => 4
-    [A_TYPE] => Typ=1 Len=1: 52
+    [A_TYPE] => Typ=2 Len=2: 193,5
     [B] => 4
     [B_TYPE] => Typ=1 Len=1: 52
     [NUM] => 4
     [NUM_TYPE] => Typ=2 Len=2: 193,5
     [STR] => 4
     [STR_TYPE] => Typ=96 Len=1: 52
-    [A_WHERE] =>
+    [A_WHERE] => 1
     [NUM_WHERE] => 1
 )
-NULL
+string(1) "1"

As you can see, oci8 binds the parameter with correct type, but pdo_oci has a bug. pdo_oci needs to be fixed as binding with incorrect type have implications as shown in the example ('04' = 4 is true, but '04' = '4' is not).

In other DBs (I tested MySQL, PostgreSQL, T-SQL/MSSQL) PDO seems to be honoring the bind type correctly.

Can be tested localy using Dockerized Oracle Database:

docker run -it -p 1521:1521 -eORACLE_PASSWORD=atk4_pass gvenzl/oracle-xe:18-slim-faststart

PHP Version

any

Operating System

any

[SakiTakamachi]

I haven't tried it yet so I could be wrong, but this probably applies to pdo_odbc, pdo_firebird as well. The behavior when specifying PDO::PARAM_STR in pdo_pgsql is also a little uneasy.

It's midnight in Japan, so I'll check it tomorrow if I have time.

[SakiTakamachi]

There is an RFC that is currently being voted on, if it is passed, pdo_oci will no longer be bundled, so I would like to wait for the results of the RFC first.

[mvorisek]

I belive this issue is a bug thus it should be fixed in PHP 8.1+. The fix should be not long as oci8 is working correctly, probably a missed case/typo when converting an integer type when binding the param using the native driver.

[SakiTakamachi]

I agree that it's probably a bug. However, I think the impact is too large for a change made in a patch version.

The current pdo_oci is designed to ignore all PARAM_TYPE specifications. Fixing only the INT case is half-hearted, and fixing everything will have too much of an impact.

[mvorisek]

Please move this to https://github.com/php/pecl-database-pdo_oci repo issues.

[mvorisek]

Please move this to https://github.com/php/pecl-database-pdo_oci repo issues - I have no permission to do that.