php - Is there anything that can be put after the "ORDER BY" clause that can pose a security risk? -

php - Is there anything that can be put after the "ORDER BY" clause that can pose a security risk? -

basically, want this:

mysql_query("select ... ... order $_get[order]")

they can create sql error putting non-sense in there, mysql_query allows execute 1 query, can't set 1; drop table ....

is there harm malicious user do, other creating syntax error?

if so, how can sanitize query?

there's lot of logic built on $_get['order'] variable beingness in sql-like syntax, don't want alter format.

to clarify, $_get['order'] won't single field/column. might last_name desc, first_name asc.

yes, sql injection attacks can utilize unescaped order clause vector. there's explanation of how can exploited , how avoid problem here:

http://josephkeeler.com/2009/05/php-security-sql-injection-in-order-by/

that blog post recommends using white list validate order parameter against, safest approach.

to respond update, if clause complex, can still write routine validates against whitelist, example:

function validate_order_by($order_by_parameter) { $columns = array('first_name', 'last_name', 'zip', 'created_at'); $parts = preg_split("/[\s,]+/", $order_by_parameter); foreach ($parts $part) { $subparts = preg_split("/\s+/", $part); if (count($subparts) < 0 || count($subparts) > 2) { // many or few parts. homecoming false; } if (!in_array($subparts[0], $columns)) { // column name invalid. homecoming false; } if (count($subparts) == 2 && !in_array(strtoupper($subparts[1]), array('asc', 'desc')) { // asc or desc invalid homecoming false; } } homecoming true; }

even if order clause complex, it's still made out of values supply (assuming you're not letting users edit hand). can still validate using white list.

i should add together don't expose database construction in urls or other places in ui , alias stuff in parameters in urls , map real values using hash.

php mysql database sql-injection