note 96851 added to security.database.sql-injection

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. ---- Server IP: 69.147.83.197 Probable Submitter: 84.148.91.109 ---- Manual Page -- http://www.php.net/manual/en/security.database.sql-injection.php Edit -- https://master.php.net/note/edit/96851 Del: integrated -- https://master.php.net/note/delete/96851/integrated Del: useless -- https://master.php.net/note/delete/96851/useless Del: bad code -- https://master.php.net/note/delete/96851/bad+code Del: spam -- https://master.php.net/note/delete/96851/spam Del: non-english -- https://master.php.net/note/delete/96851/non-english Del: in docs -- https://master.php.net/note/delete/96851/in+docs Del: other reasons-- https://master.php.net/note/delete/96851 Reject -- https://master.php.net/note/reject/96851 Search -- https://master.php.net/manage/user-notes.php