• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
subtle gotcha using query bindings

#1
[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.

#2
[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));

?


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.