![]() |
subtle gotcha using query bindings - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: subtle gotcha using query bindings (/showthread.php?tid=1981) |
subtle gotcha using query bindings - El Forum - 07-09-2007 [eluser]Bacteria Man[/eluser] I ran into one of those subtle gotchas that makes perfect sense once the reason for it is identified. I have a query string which uses a single binding: $sql = "SELECT DISTINCT(rp.permissions) FROM role r INNER JOIN role_permissions rp ON rp.role_id = r.role_id WHERE r.role_id IN (?)"; The query call looks like this: $query = $this->db->query($sql, array('roles' => $roles)); ...where $roles equals "2,3" (i.e. a comma delimited string with numeric values) The problem is that CI (and properly so) escapes the string which produces: SELECT DISTINCT(rp.permissions) FROM role r INNER JOIN role_permissions rp ON rp.role_id = r.role_id WHERE r.role_id IN ('2,3') As a result MySQL interprets only the first value and drops any subsequent ones. The obvious solution is to include the $roles variable inline as $sql = "SELECT DISTINCT(rp.permissions) FROM role r INNER JOIN role_permissions rp ON rp.role_id = r.role_id WHERE r.role_id IN ($roles)"; Using a fixed number of question marks wasn't practical because the number of comma-delimited values can vary from query to query. This is ordinarily not a good idea, but in this case the risk is minimal because there's no user-inputted data to contend with. Perhaps this will save someone a little time. subtle gotcha using query bindings - El Forum - 07-10-2007 [eluser]batteries[/eluser] for complicated queries i build them first, too. to avoid this kind of situation. also, why are you creating the key name 'roles' in: Code: $query = $this->db->query($sql, array(’roles’ => $roles)); ? |