php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #20143 dbh->limitQuery dies in oci8.php because of sql error
Submitted: 2002-10-28 20:04 UTC Modified: 2002-11-21 17:10 UTC
From: aprasad at nhgri dot nih dot gov Assigned:
Status: No Feedback Package: PEAR related
PHP Version: 4.2.3 OS: Solaris
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2002-10-28 20:04 UTC] aprasad at nhgri dot nih dot gov
When running a limitQuery like: 

$query = $dbh->limitQuery('select t1.id, t2.id from t1,t2',
                           1, 2);
echo $query->getDebugInfo();
You get the Warning: OCIStmtExecute: ORA-00918: column ambiguously defined in /usr/local/lib/php/DB/oci8.php on line 532

returns Object [nativecode=ORA-00900: invalid SQL statement]

This appears to me to be because the sql query generated on line 528:
$q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; 
(SELECT * FROM (select t1.id, t2.id from t1, t2) where NULL=NULL" )
Does not compile for Oracle.  

I'm not sure why you have to use a sub-query here and not just use the query passed in to get the column names.  
ie. $q_fields = $query;

If you make it past this problem you later have the problem
of trying to execute:
    $query = "SELECT $fields FROM".
             "  (SELECT rownum as linenum, $fields FROM".
             "      ($query)".
             "  WHERE rownum <= ". ($from + $count) .
             ") WHERE linenum >= $from";

which will fail also because $fields will contain id, id

I haven't done a lot of PHP or Oracle development before, and I couldn't think up a solution to this problem that doesn't involve lots of query parsing which probably should be avoided if possible.  

For now I've modified the code to return an error if there are column names returned from OCIColumName() that are the same.  

At the bottom of this message is the context diff for my very poorly written added warning which is not production ready. I needed something fast. It uses a redmsg() function that I've defined elsewhere because I have no idea how to do error handling. I've only been writing PHP for about two weeks so it is extremely ugly. (Whew, that was a lot of disclaimers)

If someone comes up with a good fix I would be interested in seeing it.

Thanks,
Arjun Prasad

*** oci8.php.orig       Mon Oct 28 20:47:14 2002
--- oci8.php    Mon Oct 28 20:56:27 2002
***************
*** 526,533 ****
          // Let Oracle return the name of the columns instead of
          // coding a "home" SQL parser
          
!         $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
!         if (!$result = OCIParse($this->connection, $q_fields)) {
              return $this->oci8RaiseError();
          }
          if (!OCIExecute($result, OCI_DEFAULT)) {
--- 526,536 ----
          // Let Oracle return the name of the columns instead of
          // coding a "home" SQL parser
          
!         //  BUG FIX: modification of the query unnecessary here
!         //  -- Arjun Prasad <aprasad@nhgri.nih.gov>
!         //  $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
!         //  if (!$result = OCIParse($this->connection, $q_fields)) {
!         if (!$result = OCIParse($this->connection, $query)) {
              return $this->oci8RaiseError();
          }
          if (!OCIExecute($result, OCI_DEFAULT)) {
***************
*** 538,543 ****
--- 541,566 ----
          for ( $i = 1; $i <= $ncols; $i++ ) {
              $cols[] = OCIColumnName($result, $i);
          }
+         // BUG alert: check to make sure no column names are the
+         // same if they are then report error.
+         $dup_cols = array();
+         for ($i = 0; $i < $ncols; $i++) {
+             for ($n = $i + 1; $n < $ncols; $n++) {
+                 if ($cols[$i] == $cols[$n]) {
+                     $dup_cols[] = $cols[$i];
+                 }
+             }
+         }
+         if (sizeof($dup_cols) > 0) {
+             $col_list = implode(', ', $dup_cols);
+             redmsg("Error: returned duplicate columns ($col_list). "
+                   ."Modify query to give each returned column a unique name.");
+         } 
+         // now just let it fail.  (This should be only a temporary solution)
+         // END BUG "alert" -- Arjun Prasad <aprasad@nhgri.nih.gov>
+                     
+                     
+                 
          $fields = implode(', ', $cols);
          // XXX Test that (tip by John Lim)
          //if(preg_match('/^\s*SELECT\s+/is', $query, $match)) {


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-11-01 08:49 UTC] mj@php.net
Where is the function redmesg() defined? I guess you should use PEAR::raiseError() instead.
 [2002-11-01 10:24 UTC] aprasad at nhgri dot nih dot gov
Again, I'm a php newbie.  Looking at the PEAR documentation I could not figure out how to get PEAR::raiseError to print out  an error message.  I changed my dumb little error routine

redmsg("Error: returned duplicate columns ($col_list). "
 ."Modify query to give each returned column a unique name");

to

PEAR::raiseError(
 [2002-11-01 10:32 UTC] aprasad at nhgri dot nih dot gov
PEAR::raiseError("...")
or PEAR::raiseError("...", 1, PEAR_ERROR_PRINT)
or PEAR::raiseError("...", 1, PEAR_ERROR_DIE)
or return PEAR::raiseError("...")

None of which returned my error message to the browser.  Is there something I'm missing?

Thanks,
Arjun
 [2002-11-09 10:20 UTC] mj@php.net
PEAR::raiseError("...") should be ok here. Could you then perhaps provide a new patch against the latest version of oci8.php for us?
 [2002-11-21 17:10 UTC] sniper@php.net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.


 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Tue Jul 16 06:01:26 2019 UTC