Using prepared statements for MySQL query options with PDO

0
votes

I am using PDO and have options that users can select with their search results. Some examples are sort, number of results, page number, etc. I tried using prepared statements to validate this data to prevent SQL injection attacks, but the variables are never passed into the query.

What am I doing wrong? The sort by and number of results are SELECT menus and the page number is a text input form where they can enter a number.

$query = "SELECT SQL_CALC_FOUND_ROWS * FROM people ORDER BY id :sortBy LIMIT $start, :total";
$result = $conn->prepare($query);
$result->bindValue(":sortBy", $sortBy, PDO::PARAM_STR);
$result->bindValue(":total", $total, PDO::PARAM_INT);
1
phpmysqlpdoprepared-statement
How do you determine "the variables are never passed into the query"? – deceze
Why would you bind some, not all? i.e. $start. – Jason McCreary
When I remove the bindValues and just enter ASC and 100 in place of :sortBy and :totalResults in the query, it works. – Zoolander
@Jason McCreary - I did that because $start is a variable I set myself in the PHP code whereas the other two are values the user is entering via the front-end. – Zoolander
I'd still encourage you to bind all dynamic parameters. Consider the future. Maybe someday $start is a passed value. – Jason McCreary

1 Answers

2
votes

Bound parameters are for actual data you want to pass into the query. You can't bind actual control commands- MySQL will interpret them as data, not as a command.

What you could do instead for your sort by is to check what you're sent, and pass 'ASC' or 'DESC' into the query. You're not passing user provided info to the query- instead, you're using to determine which of a set of predefined commands you are going to pass in. No injection risk.