PDO Database access WHERE title = $title

Question

I'm trying to learn to use PDO instead of MySQLi for database access and I'm having trouble selecting data from the database. I want to use:

$STH = $DBH->query('SELECT * FROM ratings WHERE title=$title ORDER BY date ASC');
$STH->setFetchMode(PDO::FETCH_ASSOC);

while($row = $STH->fetch()) {
    echo $row['title']; 
}

but I'm getting this error:

Fatal error: Call to a member function setFetchMode() on a non-object in /home/owencont/public_html/owenstest.com/ratemystudents/index.php on line 6

If I take out the WHERE statement it works fine. How can I select a row based on if it's value matches a variable?

Thanks,

Owen

score 2 · Accepted Answer · answered Mar 07 '11 at 18:04

2

It's likely a SQL syntax error, because you forgot to quote $title. It ended up as bareword in the query (also not even interpolated as string), resulting in an error. And your PDO connection was not configured to report errors. Use ->quote() on arguments before the ->query():

$title = $DBH->quote($title);
$STH = $DBH->query("SELECT * FROM ratings WHERE title=$title ");

Or better yet, use parameterized SQL:

$STH = $DBH->prepare("SELECT * FROM ratings WHERE title=? ");
$STH->execute(array($title));

answered Mar 07 '11 at 18:04

mario

144,265
20
237
291

Shouldn't this be working then? $title = 'the title'; $title = $DBH->quote($title); $STH = $DBH->prepare("SELECT * FROM ratings WHERE title=? ORDER BY date ASC"); $STH->execute( array($title) ); while($row = $STH->fetch(PDO::FETCH_ASSOC)) { echo $row['title']; } – Owen Conti Mar 07 '11 at 18:13
@Owen: No, do only one of the two. Either ->quoting, or `?` bound parameters with ->prepare and ->execute. – mario Mar 07 '11 at 18:17
Thank you for your help, your method seems to be the best and safest. – Owen Conti Mar 07 '11 at 18:22

score 1 · Answer 2 · answered Mar 07 '11 at 18:02

Take a look at PDO::prepare and PDOStatement::execute. The safest way to add user content to a query is to prepare a basic statement and bind the parameter to it. Example (note the question mark in the SQL statement):

$STH = $DBH->query('SELECT * FROM ratings WHERE title=? ORDER BY date ASC');
$STH->execute( array( $title ) );

while( $row = $STH->fetch( PDO::FETCH_ASSOC ) );

score 1 · Answer 3 · edited May 23 '17 at 12:26

1

Make PDO throw errors so you can see what exactly goes wrong. See How to squeeze error message out of PDO?
You are probably missing quotes around $title but this scenario really calls for prepared statements instead.

edited May 23 '17 at 12:26

Community

1
1

answered Mar 07 '11 at 18:03

Pekka

442,112
142
972
1,088

score 0 · Answer 4 · answered Mar 07 '11 at 18:01

0

remove the variable out of the sql statement because its a php variable

$STH = $DBH->query('SELECT * FROM ratings WHERE title=' . $title . 'ORDER BY date ASC');

answered Mar 07 '11 at 18:01

Leo-Neil Quilatan

56
3

It's SQL concatenation without escaping and without quoting -- it's asking for SQL injection. PDO has prepared statements with placeholders, and those should be used whenever possible. "Correct" answers should not contain security vulnerabilities and worst practices. – Charles Mar 07 '11 at 18:11
You've got a lot of downvoting to do then :P Let the community vote up better correct answers that teach those methods, and save downvotes for totally wrong answers. That's just my opinion. – Kevin Peno Mar 07 '11 at 18:18
hm that makes sense Charles, thanks lol, I learned something new but thanks alot Kevin for supporting me :P! – Leo-Neil Quilatan Mar 07 '11 at 18:22

score 0 · Answer 5 · answered Mar 07 '11 at 18:03

0

Use double quotes instead of single quotes as a parameter of the query-method.

The reason you're getting this error is because the query-method fails and so the $STH object isn't created. You should implement some error handling.

answered Mar 07 '11 at 18:03

Bv202

3,924
13
46
80

PDO Database access WHERE title = $title

5 Answers5

Linked

Related