note 112169 deleted from pdo.quote by salathe

Note Submitter: col dot shrapnel at gmail dot com ---- One have to understand that string formatting has nothing to do with identifiers. And thus string formatting should NEVER ever be used to format an identifier ( table of field name). To quote an identifier, you have to format it as identifier, not as string. To do so you have to - Enclose identifier in backticks. - Escape backticks inside by doubling them. So, the code would be: <?php function quoteIdent($field) { return "".str_replace("","`",$field).""; } ?> this will make your identifier properly formatted and thus invulnerable to injection. However, there is another possible attack vector - using dynamical identifiers in the query may give an outsider control over fields the aren't allowed to: Say, a field user_role in the users table and a dynamically built INSERT query based on a $_POST array may allow a privilege escalation with easily forged $_POST array. Or a select query which let a user to choose fields to display may reveal some sensitive information to attacker. To prevent this kind of attack yet keep queries dynamic, one ought to use WHITELISTING approach. Every dynamical identifier have to be checked against a hardcoded whitelist like this: <?php $allowed = array("name","price","qty"); $key = array_search($_GET['field'], $allowed)); if ($key == false) { throw new Exception('Wrong field name'); } $field = $db->quoteIdent($allowed[$key]); $query = "SELECT $field FROM t"; //value is safe ?> (Personally I wouldn't use a query like this, but that's just an example of using a dynamical identifier in the query). And similar approach have to be used when filtering dynamical arrays for insert and update: <?php function filterArray($input,$allowed) { foreach(array_keys($input) as $key ) { if ( !in_array($key,$allowed) ) { unset($input[$key]); } } return $input; } //used like this $allowed = array('title','url','body','rating','term','type'); $data = $db->filterArray($_POST,$allowed); // $data now contains allowed fields only // and can be used to create INSERT or UPDATE query dynamically ?>