Page 1 of 1

[SOLVED] Prepared Statement issues

Posted: Thu Feb 17, 2011 9:57 am
by vaughtg
Greetings all! I am brand new to this forum and relatively new to PHP, although I've been developing web apps for about 5 years.

I'm currently working on a public web app and having to figure out PHP, PDO and prepared statements. I had my database connections and queries working wonderfully (if insecurely) with direct-build queries. But now I'm trying to do prepared statements to reduce the risk of SQL injection and I continually get ORA-00903: invalid table name errors.

I'm just trying to get it to work, so this is what I'm testing with:
From test.php:

Code: Select all

<?php session_start();?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html>
  <head>
  </head>
  <body>
<?php
   require '../classes/DBConnection.php';

   $conn = new DBConnection();
   $dataArray = array("user");
   $tableName = "dual";
   $andArray = array();
   $orArray = array();
   $orderBy = "";
   $testData = $conn->getDataVals($dataArray, $tableName, $andArray, $orArray, $orderBy);
   foreach ($testData as $data){
      echo "value is ".$data;
   }
   
   if(isset($_SESSION['errorMsg'])){
      echo "Error: ".$_SESSION['errorMsg'];
   }
?>
	</body>
</html>
And from DBConnection.php

Code: Select all

      public function getDataVals($dataArray, $tableName, $andArray, $orArray, 
            $orderBy){
         /*
          * receives a SQL query, calls getResultSet to generate an array of the
          *  result set then returns that array to the calling method
          */
         $query = "SELECT ";
         for($i=0; $i<count($dataArray); $i++){
            if($i>0)$query.=", ";
            $query.="?";
         }
         $query.=" FROM ? ";
         if(0<count($andArray) || 0<count($orArray)){
            $query.=" WHERE ";
         }
         for($j=0; $j<count($andArray); $j++){
            if($j>0)$query.=" AND ";
            $query.="? = ?";
         }
         for($k=0; $k<count($andArray); $k++){
            if($k>0)$query.=" OR ";
            $query.="? = ?";
         }
         if(isset($orderBy))$query.=" ORDER BY ?";
         $rsArray = $this->getResultSet($query, $dataArray, $tableName, 
               $andArray, $orArray, $orderBy);
         return $rsArray;
      }
And from DBConnection.php

Code: Select all

   
      private function getResultSet($query, $dataArray, $tableName, 
               $andArray, $orArray, $orderBy){
         /*
          * receives a query, parses and executes that query, puts the result
          *  set into an array and returns that array to the calling method
          */
         $conn = DBConnection::$connection;
         $stmt = $conn->prepare($query);
         $retArray = array();
         $bindParams = array();
         foreach ($dataArray as $value){
            $bindParams[] = $value;
         }
         $bindParams[] = $tableName;
         $andKeys = array_keys($andArray);
         for($j=0; $j<count($andArray); $j++){
            $key = $andKeys[$j];
            $value = $andArray[$j];
            $bindParams[] = $key;
            $bindParams[] = $value;
         }
         $orKeys = array_keys($orArray);
         for($k=0; $k<count($orArray); $k++){
            $key = $orKeys[$j];
            $value = $orArray[$j];
            $bindParams[] = $key;
            $bindParams[] = $val;
         }
         if(isset($orderBy) && "" != $orderBy) $bindParams[] = $orderBy;
         if($stmt->execute($bindParams)){ // ****** THIS IS THE STATEMENT THAT FAILS *****
            while ($row = $stmt->fetch()) {
               foreach ($dataArray as $key){
                  $retArray[$key] = $row[$key];
               }
           }
         }else{
            $errInfo = $conn->errorInfo();
            $this->setErrorMsg("- Database error:getResultSet<br />\nSQLSTATE ".
            		"Error Code: ".$errInfo[0]."<br />\nError Code: ".$errInfo[1].
            		"<br />\nMessage: ".$errInfo[2]."<br />\n");
         }
         return $retArray;
      }
Everything tracks just fine until I get to the line marked as the failure point. After that, it kicks down to outputting the error information, which is consistently an ORA-00903: invalid table name error.

Re: Prepared Statement issues

Posted: Thu Feb 17, 2011 10:02 am
by Weirdan
You cannot use prepared statement variables for anything that isn't data. Query structure should be unambiguous even without any parameters applied - that means parameters cannot be used in place of table names, field names, procedure names, limit parameters, etc.

Re: Prepared Statement issues

Posted: Thu Feb 17, 2011 11:07 am
by vaughtg
Thanks Weirdan! I'll work from that standpoint and see how it goes.

Re: Prepared Statement issues

Posted: Mon Feb 21, 2011 9:36 am
by vaughtg
Is there any way to mark these threads/issues as resolved?

Re: Prepared Statement issues

Posted: Mon Feb 21, 2011 9:51 am
by Weirdan
vaughtg wrote:Is there any way to mark these threads/issues as resolved?
we have a tradition of adding [SOLVED] to the start of the thread title. Edit your first post and change the subject.