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

Answer 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.