ARTICLE AD BOX
I've done a little looking around on string statements for MYSQLi and changed to prepared statements because of injection vulnerabilities. That has been all fine until now. I now need to use order by and asc/desc but with these not being values, they can't be bound parameters. I understand all that.
I might have found a work around (that works for me anyway) but I'm wondering, if this causes any security risks I'm unaware of? if not, I'm wondering why it hasn't been suggested before (not that I can initially find anyway).
I am preparing the SQL statement in a string first and then passing to the prepare statement. I am aware this is frowned upon and it needs to be directly in the prepare statement.
To avoid SQL injection (I'm hoping), I am using switch to select and pass in a pre-set variable.
I'm not using the initial variable names (i.e. $direction_param , $order_col_param) in the concatenated string, so this can't be used nefariously. I'm hardcoding the options for order by and direction.
Any thoughts why this would be a good/bad idea is much appreciated.
$direction_param="d";$order_col_param="a"; switch ($direction_param) {case "a" :$order_direction="asc";break; case "d" :$order_direction="desc";break;default:$order_direction="asc";} switch ($order_col_param) {case "a" :$order_by="items.id";break; case "b":$order_by="items.item_price";break;default:$order_by="items.id";} $pps="select item_code,item_section,item_title,item_info,item_price,item_qty,type_code,section.return_address from items inner join section on items.item_section=section.id where (item_qty>0 or item_rep=\"Y\") and item_active=\"Y\" and item_eol=\"N\" and section.type_code=? order by " . $order_by . " " . $order_direction . " limit ? offset ?;"; $stmt_get_item_details=$mysqli->prepare($pps); $stmt_get_item_details->bind_param("sii",$section,$items_per_page,$offset); $stmt_get_item_details->execute(); $stmt_get_item_details_results = $stmt_get_item_details->get_result();