How do I use pdo's prepared statement for order by and limit clauses(or can I?If not,what should I reference for a complete list of clauses where parameters can be used) ?

Question

$sql = "SELECT * FROM table ORDER BY :sort :dir LIMIT :start, :results";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
     'sort'  => $_GET['sort'], 
     'dir'  => $_GET['dir'], 
     'start'  => $_GET['start'],
     'results' => $_GET['results'],
     )
    );

I tried to use prepare to do the job,but $stmt->fetchAll(PDO::FETCH_ASSOC); returns nothing.

Can someone point out what's the wrong thing I am doing?

Answer 1

You can't bind a parameter to specify a language keyword or a field name - it has to be replacing a literal. Therefore, your limit values I think are fine, but your order by is not. It will be best for you to manually replace sort and dir in the string. Escape them but don't use the DB tools to do so, since they aren't string literals. Basically ensure no special characters are present.

Answer 2

After using :

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I got the message :

Uncaught 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 ''0', '10'' at line 1

So, when you use an array for execute, it consider your inputs as string which is not a good idea for LIMIT

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM table ORDER BY :sort :dir LIMIT :start, :results";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':start', $_GET['start'], PDO::PARAM_INT);
$stmt->bindParam(':results', $_GET['results'], PDO::PARAM_INT);
$stmt->bindParam(':sort', $_GET['sort']);
$stmt->bindParam(':dir', $_GET['dir']);
$stmt->execute();

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($data);