php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35919 Bad substitution with PDO::prepare
Submitted: 2006-01-06 16:17 UTC Modified: 2016-03-08 01:36 UTC
Votes:2
Avg. Score:4.0 ± 1.0
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: atixier at france-loisirs dot com Assigned: wez (profile)
Status: Not a bug Package: PDO OCI
PHP Version: 5CVS-2006-01-06 (snap) OS: AIX 4.3.3
Private report: No CVE-ID: None
 [2006-01-06 16:17 UTC] atixier at france-loisirs dot com
Description:
------------
I'm testing PDO with Oracle 9iR2.
Evrything is OK, but I experience two problems whith PDO:Prepare when using question mark or names parameters.

Whith this sequence:
$stmt=$dbh->prepare("insert into R_QQQ (QUANTIEME,RUN) values (?,?)");
$stmt->execute(array("ZYX", "053"));  
$stmt=$dbh->query("select * from R_QQQ");
$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
  print("<pre>\n");
  print_r($row);
  print("</pre>\n");
}
I get the following result:
Array
(
    [RUN] => 053
    [DH_FIN] => 06-JAN-06
    [QUANTIEME] => 053
)

Second bug: when I try to insert a date whith the following command:
$stmt=$dbh->prepare("insert into R_QQQ (QUANTIEME,DH_FIN,RUN) values (?,?,?)");
$stmt->execute(array("ZYX", "to_date('20050222','YYYYMMDD')","053"));
I get an error ORA-01861:
SQLSTATE[HY000]: General error: 1861 OCIStmtExecute: ORA-01861: literal does not match format string (/prod/outils/php5.1-200601051130/ext/pdo_oci/oci_statement.c:142)

Everyting is OK when using directly: 
PDO:exec ("insert ...");

Thank you for your help.

Expected result:
----------------
Array
(
    [RUN] => ZYX
    [DH_FIN] => 22-FEB-05
    [QUANTIEME] => 053
)

Actual result:
--------------
Array
(
    [RUN] => 053
    [DH_FIN] => 06-JAN-06
    [QUANTIEME] => 053
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-01-19 11:57 UTC] tommys at da2 dot so-net dot ne dot jp
PHP 5.1.2 with Oracle 10gR1:

$id = 123;
$name = 'foo';
$stmt = $dbh->prepare("select ?,? from dual");
$stmt->execute(array($id, $name));
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

Output:
Array ( [:PDO1] => foo [:PDO2] => foo )

With bindValue():
$stmt->bindValue(1, $id);
$stmt->bindValue(2, $name);
$stmt->execute();

is same result.

With named placeholders:
$stmt = $dbh->prepare("select :id, :name from dual");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

Output:
Array ( [:ID] => foo [:NAME] => foo )

With bindParam():
$stmt = $dbh->prepare("select :id, :name from dual");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':name', $name);
$stmt->execute();

Array ( [:ID] => -1.990921021434 [:NAME] => -1.990921021434 
)
 [2006-04-09 07:45 UTC] wez@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.1-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.1-win32-latest.zip


 [2006-04-10 15:04 UTC] atixier at france-loisirs dot com
I installed the lastest CVS: php5.1-200604101230
With the firt sequence (please refer to my first mail): $stmt=$dbh->prepare("insert into R_QQQ (QUANTIEME,DH_FIN,RUN) values (?,?)");
  $stmt->execute(array("ZYX", "053"));
==> Everything is OK

With the second one (insert a date with "to_date" format):
$stmt=$dbh->prepare("insert into R_QQQ (QUANTIEME,DH_FIN,RUN) values (?,?,?)");
  $stmt->execute(array("053", "to_date('20050222','YYYYMMDD')","XYZ"));
I get the following result:
SQLSTATE[HY000]: General error: 1858 OCIStmtExecute: ORA-01858: a non-numeric character was found where a numeric was expected (/prod/outils/php5.1-200604101230/ext/pdo_oci/oci_statement.c:142)

BUT, everything is OK, if I changed the "to_date" Oracle function in array by:
$stmt=$dbh->prepare("insert into R_QQQ (QUANTIEME,DH_FIN,RUN) values (?,?,?)");
$stmt->execute(array("053", "22-FEB-2005","XYZ"));
 [2006-04-10 15:22 UTC] tony2001@php.net
$stmt->execute(array("053", "to_date('20050222','YYYYMMDD')","XYZ"));
This will never work.
Binding doesn't mean "change '?' to what I say", it's more sophisticated. You can only pass values there, expressions like this are not allowed.
 [2006-04-11 06:17 UTC] atixier at france-loisirs dot com
Thanks for your help. This last point is now clear.
 [2016-03-08 01:36 UTC] sixd@php.net
-Package: PDO related +Package: PDO OCI
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC