1

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.

Zamony
  • 38
  • 5
  • possible duplicate of [Why does my PDO statement -> execute return false?](http://stackoverflow.com/questions/6420176/why-does-my-pdo-statement-execute-return-false) – Pekka Feb 17 '13 at 17:39
  • 1
    You sadly can't use dynamic table names. Also of interest: [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) – Pekka Feb 17 '13 at 17:39
  • If I use this query without dynamic table name, it returns True. But I can't get data from ->fetch()(non-object) – Zamony Feb 17 '13 at 17:43
  • Why do you need dynamic table name? Why can't you type just `users`? – Your Common Sense Feb 17 '13 at 17:46
  • Because table has prefix, but I already try to use static table name. – Zamony Feb 17 '13 at 17:55

3 Answers3

2

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'.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Anonymous downvote, please explain why you think this answer is wrong, and maybe I can improve it. – Bill Karwin Feb 17 '13 at 17:51
  • See my edited code above that shows exactly how to access errorInfo() and the error you should get as a result. – Bill Karwin Feb 17 '13 at 18:40
  • Thank you! I got this error. Now everything is clear. But now, when I changed the query: $this->db->prepare ("select pass from nm_users where username=: username"); variable $result should return a PDO object. But it returns True. It's strangely. – Zamony Feb 18 '13 at 13:39
  • You should put the `:` right next to the parameter name, don't separate them with a space. – Bill Karwin Feb 18 '13 at 17:56
  • In my code all is true. In message from above, a space was added by StackOverflow editor. The error is seemed strange, I didn't believe in that I will find solution. Thank you for help, may be it's problem of my system. – Zamony Feb 19 '13 at 15:47
  • I cannot reproduce any situation where query() returns `true`. I even checked the source code for PDO, but query() never returns `true`. It only returns a PDOStatement object, or `false` on error. – Bill Karwin Feb 19 '13 at 22:50
0

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.

michi
  • 6,565
  • 4
  • 33
  • 56
  • In PHP.NET Examples they has colons. But no matter what my code doesn't work:(http://php.net/manual/ru/pdostatement.execute.php – Zamony Feb 17 '13 at 18:03
-1

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!

arkascha
  • 41,620
  • 7
  • 58
  • 90
  • Thank you for your answer! But when I apply fetch() method for result of code from above, I receive:Call to a member function fetch() on a non-object. May be I do something wrong... – Zamony Feb 17 '13 at 17:52
  • OK, that means that the object `$query` has not been created. You should indeed check that before using the object. I added that check to the answer. Not sure what it is, maybe `$this->table_name` does not hold a valid table name? – arkascha Feb 17 '13 at 18:02
  • Some error occurred:Parse error: syntax error, unexpected '$query' – Zamony Feb 17 '13 at 18:10
  • Ok ok, I forgot a `;`. I DO apologize! But one question: you really could not see that yourself? Or do you always copy and paste everything someone shows to you and give up frustrated without even trying to understand what it is you copy? – arkascha Feb 17 '13 at 18:12
  • Well, don't. You won't succeed if you do. You will produce crap. – arkascha Feb 17 '13 at 18:17
  • Agree. But what about the problem? – Zamony Feb 17 '13 at 18:23
  • Ok, that is sad. But since I don't know what your current problem is I can't help, I am afraid... – arkascha Feb 18 '13 at 21:42