Ryan Griggs Ryan Griggs - 1 month ago 14
MySQL Question

MeekroDB - how to safely pass ORDER BY field names and arguments?

I am using MeekroDB in a PHP project. For some queries, I need to pass arbitrary field names to sort by. There are NO examples of ORDER BY clauses on the meekro site.

How can I safely pass field names and avoid SQL injection vulnerabilities? I realize I could check every field name with a list of valid fields beforehand, but I'm trying to make this code more generalized as a basic "get" function: function get(Array $filters, Array $sort_by)

Will the %b placeholder (backticks) be sufficient to protect against arbitrary code injection when passing field names?

For example:

SELECT * FROM table1 ORDER BY %b


Or for multiple fields:

SELECT * FROM table1 ORDER BY %lb


Is this safe?

Also, how can I then include the DESC or ASC modifiers arbitrarily as needed?

Answer

Yes, you can safely use b and lb for the purpose, as both implemented using formatTableName method that is safe.

Unfortunately, direction modifiers should be sanitized by hand, like this

$dirs  = ["ASC","DESC"]; 
$key   = array_search($_GET['dir'], $dirs); // see if we have such a value
$dir   = $dirs[$key]; //if not, first one will be set automatically. smart enuf :)
$query = "SELECT * FROM table1 ORDER BY %b $dir"; //value is safe