[SOLVED] Prepared Statement issues
Posted: Thu Feb 17, 2011 9:57 am
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:
And from DBConnection.php
And from DBConnection.php
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.
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>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;
}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;
}