SQL query fails when using bindValue, but works when value is explicitly stated

[Twinbird]

Hello,

 

I have the following code:

$sql = 'SELECT id, name, research_interests FROM students ORDER BY name ASC';
$s = $pdo->prepare($sql);
$s->execute();

This code works with no problems. But when I change it to use bindValue(), it fails.

$sql = 'SELECT id, name, research_interests FROM students ORDER BY name :order';
$s = $pdo->prepare($sql);
$s->bindValue(':order', 'ASC');
$s->execute();

What could be causing the problem?

 

This is the error I receive:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 '?' at line 1' in /var/www/grad/support/myMeetings.php:146 Stack trace: #0 /var/www/grad/support/myMeetings.php(146): PDO->prepare('SELECT id, name...') #1 /var/www/grad/controllers/myMeetings.php(73): listStudents() #2 /var/www/grad/index.php(36): include('/var/www/grad/c...') #3 {main}

Thanks,

 

Twin

 

[mac_gyver]

only data values can be put into a prepared query after it is prepared. a keyword like "ASC" is not data. it's part of the query syntax and can only be put into the query at the time it is passed to the ->prepared() method.

[lemmin]

The default type for bindValue() is String. As mac_gyver said, the order orientation ASC is a keyword, not a string. (You wouldn't want quotes around it, right?) Since it isn't a string, you don't need to escape it or anything, so you can simply concatenate the variable to your query string without worrying about binding it:

$sql = 'SELECT id, name, research_interests FROM students ORDER BY name '.$order;

Make sure $order isn't generated directly from the user or you could create an injection vulnerability.

[Twinbird]

Thanks for the help mac_gyver and lemmin!