php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #32298 handling NULL parameters in ibase_prepare/ibase_execute
Submitted: 2005-03-14 10:35 UTC Modified: 2005-03-16 10:00 UTC
Votes:2
Avg. Score:4.5 ± 0.5
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (100.0%)
From: cryo28 at rbcmail dot ru Assigned:
Status: Wont fix Package: InterBase related
PHP Version: 5.0.3 OS: Win2000
Private report: No CVE-ID: None
 [2005-03-14 10:35 UTC] cryo28 at rbcmail dot ru
Description:
------------
Requesting feature is ability to pass null parameters with ibase_prepare/execute. At this moment, a didn't found any way for selecting rows by sql statement containing param in where_clause which values can be null. 

A similar work is done, for example, in FibPlus components for delphi by autoconverting "WHERE FIELD=:param" to "WHERE FIELD IS NULL" if :param=NULL.

Reproduce code:
---------------
CREATE TABLE TESTTABLE (
    ID         INTEGER NOT NULL,
    NULLFIELD  INTEGER
);

INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (1, NULL);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (2, NULL);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (3, 1);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (4, 1);

COMMIT WORK;

<?php

$db = ibase_connect('localhost:d:\inet\bases\wwwcat.gdb', 'SYSDBA', 'masterkey', 'win1251', null, 3);
$q_sql_1 = 'SELECT ID FROM TESTTABLE WHERE NULLFIELD IS NULL';
$q_sql_2 = 'SELECT ID FROM TESTTABLE WHERE NULLFIELD = 1';

$st_sql_1 ='SELECT ID FROM TESTTABLE WHERE NULLFIELD = ?';
$st_sql_2 ='SELECT ID FROM TESTTABLE WHERE NULLFIELD IS ?';
//PHPDocument1 string 10 - ibase_prepare() [<a href='function.ibase-prepare'>function.ibase-prepare</a>]: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 45 ? 


$st1 = ibase_prepare($db, $st_sql_1);
$st2 = ibase_prepare($db, $st_sql_2);

echo $q_sql_1."\r\n";
$q1 = ibase_query($q_sql_1);
while ($r1 = ibase_fetch_object($q1)) var_dump($r1);
echo "\r\n".'-----------------------'."\r\n";

echo $q_sql_2."\r\n";
$q2 = ibase_query($q_sql_2);
while ($r2 = ibase_fetch_object($q2)) var_dump($r2);
echo "\r\n".'-----------------------'."\r\n";


echo $st_sql_1.' param = 1'."\n\r";
$q4 = ibase_execute($st1, 1);
while ($r4 = ibase_fetch_object($q4)) var_dump($r4);
echo "\r\n".'-----------------------'."\r\n";


echo $st_sql_1.' param = NULL'."\n\r";
$q3 = ibase_execute($st1, NULL);
while ($r3 = ibase_fetch_object($q3)) var_dump($r3);
echo "\r\n".'-----------------------'."\r\n";

echo $st_sql_2.' param = NULL'."\n\r";
$q5 = ibase_execute($st2, NULL);
while ($r5 = ibase_fetch_object($q5)) var_dump($r5);
echo "\r\n".'-----------------------'."\r\n";
?>

Expected result:
----------------
Expected result is ability to pass NULL params to ibase_execute and get right value as if query was WHERE NULLFIELD IS NULL


Actual result:
--------------
Content-type: text/html

X-Powered-By: PHP/5.0.3



SELECT ID FROM TESTTABLE WHERE NULLFIELD IS NULL

object(stdClass)#1 (1) {
  ["ID"]=>
  int(1)
}
object(stdClass)#2 (1) {
  ["ID"]=>
  int(2)
}


-----------------------

SELECT ID FROM TESTTABLE WHERE NULLFIELD = 1

object(stdClass)#2 (1) {
  ["ID"]=>
  int(3)
}
object(stdClass)#1 (1) {
  ["ID"]=>
  int(4)
}


-----------------------

SELECT ID FROM TESTTABLE WHERE NULLFIELD = ? param = 1

object(stdClass)#1 (1) {
  ["ID"]=>
  int(3)
}
object(stdClass)#2 (1) {
  ["ID"]=>
  int(4)
}


-----------------------

SELECT ID FROM TESTTABLE WHERE NULLFIELD = ? param = NULL



-----------------------

SELECT ID FROM TESTTABLE WHERE NULLFIELD IS ? param = NULL



-----------------------



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-03-14 22:21 UTC] sniper@php.net
Please try using this CVS snapshot:

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


 [2005-03-16 09:34 UTC] cryo28 at rbcmail dot ru
I've tried it on latest snapshot downloaded from link above (win32 version). PHP 5.1.0-dev. Got same result. Here is a little changed script and results of execution (table creationg and filling is the same).

<?php
$db = ibase_connect('localhost:d:\inet\bases\wwwcat.gdb', 'SYSDBA', 'masterkey', 'win1251', null, 3);
$sql = 'SELECT * FROM TESTTABLE WHERE NULLFIELD = ?';

$st = ibase_prepare($db, $sql);

echo $sql.'. Param = NULL'."\r\n";
$q1 = ibase_execute($st, NULL);
var_dump($q1);
while ($r1 = ibase_fetch_assoc($q1)) var_dump($r1);

echo "\r\n ----------------------------- \r\n";

echo $sql.'. Param = 1'."\r\n";
$q2 = ibase_execute($st, 1);
var_dump($q2);
while ($r2 = ibase_fetch_assoc($q2)) var_dump($r2);
?>




SELECT * FROM TESTTABLE WHERE NULLFIELD = ?. Param = NULL
resource(4) of type (interbase result)

 ----------------------------- 
SELECT * FROM TESTTABLE WHERE NULLFIELD = ?. Param = 1
resource(5) of type (interbase result)
array(2) {
  ["ID"]=>
  int(3)
  ["NULLFIELD"]=>
  int(1)
}
array(2) {
  ["ID"]=>
  int(4)
  ["NULLFIELD"]=>
  int(1)
}
 [2005-03-16 10:00 UTC] abies@php.net
As you point out, this would involve rewriting the SQL based on the value of parameters. In the current situation, PHP doesn't touch the SQL, and relies on the client library to check for correctness. Adding the capability you describe to PHP would inevitably require some SQL parsing at the PHP level, which is something we could do without.

Furthermore, the whole point of prepare()/execute() is that the SQL is processed only once, and executed multiple times   with different parameter values. Changing the SQL between executions kind of defeats this purpose, as you would have to re-prepare the modified statement.

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 12:01:31 2024 UTC