note 96851 deleted from security.database.sql-injection by danbrown
| From: | danbrown@php.net | Date: | Fri, 19 Mar 2010 15:26:05 +0000 |
| Subject: | note 96851 deleted from security.database.sql-injection by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-167204@lists.php.net to get a copy of this message | ||
Note Submitter: ThoK
----
A dyamic solution to secure the "ORDER BY" value which can not be secured by
mysql_real_escape_string()
This is necessary if you get column-name and/or sort-direction from user, like in GridPanel of
Ext.JS or other ajax-frameworks etc. and you do not want to maintain fixed array of valid fieldnames
to match in php itself.
<?php
# get existing columns for ORDER BY check
$order_names = array();
$query = 'SHOW COLUMNS FROM
table1';
$result = mysql_query($query);
if ($result !== FALSE) {
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$order_names[] = $row['Field'];
}
}
}
# build ORDER BY, match names, best way to prevent sql-injection here
if (in_array($_REQUEST['sort'], $order_names)) {
$dir = $_REQUEST['dir'] == 'DESC' ? 'DESC' : 'ASC';
$order_by = ' ORDER BY ' . $_REQUEST['sort'] . ' ' .
$dir;
} else {
$order_by = '';
}
# build full query
$query = 'SELECT * FROM table1';
$query .= $order_by;
?>
Small side-effect of this approach is (same with a stored fixed array in .php to compare), attacker
could try wordlists against your script and so enumerate your table fieldnames by looking at results
if sorted or not.