SQL ORDER BY variable parameter

Question

I can't find how to use a variable as a parameter of the ORDER BY selector : the following code doesn't work.

$orderBy = 'number';
$q = $instanceBDD->prepare('SELECT * FROM operations ORDER BY :orderBy ASC', 
                           array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$q->execute(array('orderBy' => $orderBy));

whereas

$q = $instanceBDD->prepare('SELECT * FROM operations ORDER BY number ASC',
                   array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$q->execute();

works.

Since I'd like to put this code in a function with $orderBy as a parameter, it would be really convenient to find a way to tackle this problem...

You could create a stored procedure where you pass in the name of the column that you wish to order by. — CM Kanode, Dec 26 '13 at 14:15

score 1 · Answer 1 · answered Dec 26 '13 at 14:12

1

Whats wrong with directly putting it int the string?

$q = $instanceBDD->prepare('SELECT * FROM operations ORDER BY '.$orderBy.' ASC',
    array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

EDIT: Of course $orderBy has to be escape to avoid injections (PDO::quote()).

answered Dec 26 '13 at 14:12

Markus Kottländer

8,228
4
37
61

2

That opens one up for a SQL Injection Attack – CM Kanode Dec 26 '13 at 14:13

score 0 · Accepted Answer · answered Dec 26 '13 at 14:12

I don't think you can :

Use placeholders in an order by clause
Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.

You can use just variables tho, like

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders));
$order=$orders[$key];
$instanceBDD->prepare("SELECT * FROM operations ORDER BY '$order' ASC');

SQL ORDER BY variable parameter

2 Answers2