SQL query escaping + codeigniter

Question

I'm using codeigniter and most of the time use active record for my queries (which automatically escapes them), but this query doesn't seem to fit neatly into it because of the variable. So I need to figure out how to escape the query manually.

Codeigniter docs suggest escaping the queries this way:

$sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")";

My original query

$sql = "SELECT * FROM (`user_language`) WHERE `user_id` = '{$id}'";

My escaped query

$sql = "SELECT * FROM (`user_language`) WHERE `user_id` = '{$id}' VALUES(".$this->db->escape($user_language).")";

But I'm having trouble getting the syntax right. Error messages are:

• PHP error message: Undefined variable: user_language
• SQL error: syntax wrong...near 'VALUES(NULL)' at line 1

Solution 1

$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . $this->db->escape($id);

if you want to select the language of the user given by $id it should work that way.

dealing with numbers an alternative would be:

$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . (int)$id;

codeigniter does also support prepared statements as "query bindings":

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.

Solution 2

I'm confused why you say you cannot use the Active Record class with CI, this is a simple SQL call (example below uses method chaining):

$this->db->select('*')->from('user_language')->where('user_id', $id);
$query = $this->db->get();

Your $id is then escaped properly, and you mitigate any injection. Personally I use AR whenever possible, it allows me to write quick efficient code, and not worry about the bad things with SQL calls (custom queries).

Comments

[chowwy]

Thank YOU; it works! I accepted and upvoted your answer. Side question: Is there any way that I can confirm the query is escaped? I've got a few other queries to escape and I want to check them after.

[Hajo]

well i'm using prepared statements and no escaping for years. i don't know if codeigniter supports them: php.net/manual/en/pdo.prepared-statements.php

[chowwy]

Okay, thanks. I think Codeigniter uses something called Active Record which automatically escapes queries. But some queries don't fit neatly into its syntax, so that's why I needed help. Thanks again.

[Hajo]

codeigniter seems to call prepared statements "query bindings". its in their documentation.

[chowwy]

Okay, thanks. You see my lack of knowledge. I haven't used the bindings either, mostly because of the variables. Again, appreciate your help.

[chowwy]

Okay, this is upvoted. It's not that I couldn't, but I hadn't heard of method chaining. I didn't know how to indicate the variable in the where clause. Very much appreciated.

[Jakub]

@chowwy, its all in the User Guide, its very thorough with good examples

[chowwy]

Yes, I use that as a reference. Thanks again.

[David 天宇 Wong]

> codeigniter does also support prepared statements as "query bindings": CodeIgniter escapes everything automatically when you use their Active Records. Query binding or not. Your answer is no longer valid.

[Hajo]

sorry to say that david, but you seem to be wrong. the codeigniter manual shows a hint on every active record method that provides automatic escaping, but not all methods got that hint, so not all methods provide that functionality.