CODEWITHSUNDEEP
phppdo
Zamony
Zamony

Reputation: 38

PHP PDO doesn't select data

When I get data without "prepare" and "execute", code is working fine. Example:

$this->db->query("select {$val} from {$table_name} where username={$username}")->fetch();

But this code always return False:

$this->db->prepare("select :val from :table_name where username = :username")
->execute(array(':username'=>$username,':val'=>$val,':table_name'=>$this->table_name));

HELP!:(

Thank you for your answers. Now my code is looking here:

$q=$this->db->prepare("select pass from nm_users where username = :username");
return $q->execute(array('username'=>$username));

Return value is True, but I can't get data from DB.

Upvotes: 1

Views: 456

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562328

Don't try to use PDO as a fluent interface. You can't do this:

$db->prepare()->execute();

The reason is that fluent interfaces work only if the function is guaranteed to return an object that has in this case an execute method.

But prepare() returns false on error. The value false isn't an object, and doesn't have an execute() method.

You need to check for false after every prepare() and after every execute():

$stmt = $this->db->prepare("select :val from :table_name where username = :username");
if ($stmt === false) {
  $err = $this->db->errorInfo();
  error_log($err[2]);
}
$result = $stmt->execute(array(':username'=>$username,':val'=>$val,':table_name'=>$this->table_name));
if ($result === false) {
  $err = $stmt->errorInfo();
  error_log($err[2]);
}

If you do this, you'll find that an error was reported on your prepare():

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user' where username = 'bill'' at line 1

The reason is that query parameters are only for constant values. You can't use them for table names, column names, SQL keywords, expressions, lists of values, etc.

I'm inferring that :val is also meant to be a dynamic column name, and that's not allowed either. But in that case, it won't result in an error, it'll just substitute a literal string with the value of $val for every row returned.

In other words, substituting the table name with a parameter is wrong because you can't do a query like SELECT * FROM 'user' (literal string, not table name), and that's how the parameter will act. It's simply invalid SQL.

But the dynamic column name will do a query like SELECT 'val' FROM ... and that's legal, but won't select from the column named val, it'll select the literal string constant 'val'.

Upvotes: 2

arkascha
arkascha

Reputation: 42915

The table name must be contained inside the query when you 'prepare' it, it cannot be added dynamically as the rest of the arguments. Therefore you have to use a combination of two strategies to finalize your query: 

$stmnt=sprintf('select %1$s from %2$s where username=:username',
               $val, $this->table_name);
if (FALSE===($query=$this->db->prepare($stmnt)))
    exit('Buggy statement: '.$stmnt);
$query->execute(array(':username'=>$username));

Unfortunately this also means you have to take care that $this->table_name is escaped correctly!

Upvotes: -1

michi
michi

Reputation: 6625

Parameters cannot be set for table-names etc. and have to be set in the array without the colon:

$dbSelect=$db->prepare("select aField from aTable where username = :username")
$dbSelect->execute(array('username' => $username));

Replace aField and aTable with standard str_replace or sth similar.

Upvotes: 0

Related Questions