Wrong escaping in mysqli adapter #296

Exceptionfault opened this Issue Aug 25, 2011 · 2 comments


None yet
2 participants

When using the mysqli adapter and the following database select:

return $this->db->select("a.x, a.y, COALESCE(a.z, 0) as z')
                ->from('a')->join("b", "left")->order_by('a.sortorder')

The resulting SQL Statement will look like this:

SELECT `a`.`x`, `a`.`y`, COALESCE(a.z, `0)` as z  [snip]

See the wrong order of ) and ' in the COALESCE function. When I add a whitespace in the method call like this:

COALESCE(a.z, 0        ) 

then the generated sql is correct.

SELECT `a`.`x`, `a`.`y`, COALESCE(a.z, `0`) as z  [snip]

I've notived even onother bad thing...

$this->db->select("COALESCE(price * amount, 0.0 )");

results in

SELECT COALESCE(price * amount, `0`.`0` ) 

ktomk commented Aug 29, 2011

That's because some string search and replace is in action rather than parsing the SQL. It's document that CI only tries:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement. [Highlight added]

Use $this->db->select("COALESCE(price * amount, 0.0 )", FALSE); instead.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment