5

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
chowwy
  • 1,126
  • 8
  • 26
  • 45

2 Answers2

12
$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.

Hajo
  • 849
  • 6
  • 21
  • 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. – chowwy May 03 '12 at 16:22
  • well i'm using prepared statements and no escaping for years. i don't know if codeigniter supports them: http://php.net/manual/en/pdo.prepared-statements.php – Hajo May 03 '12 at 16:24
  • 1
    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. – chowwy May 03 '12 at 16:25
  • codeigniter seems to call prepared statements "query bindings". its in their documentation. – Hajo May 03 '12 at 16:26
  • 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 May 03 '12 at 16:33
  • > 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. – David 天宇 Wong Feb 05 '13 at 20:05
  • 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. – Hajo Nov 06 '13 at 04:41
4

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

Jakub
  • 20,418
  • 8
  • 65
  • 92
  • 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. – chowwy May 03 '12 at 17:19
  • @chowwy, its all in the [User Guide](http://codeigniter.com/user_guide/database/index.html), its very thorough with good examples – Jakub May 03 '12 at 20:19
  • Yes, I use that as a reference. Thanks again. – chowwy May 03 '12 at 23:22