6

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
Improve this question
6
  • 1
    do you want to insert or select something with your query? Commented May 3, 2012 at 16:15
  • SELECT. The INSERT is from the Codeigniter docs. Commented May 3, 2012 at 16:16
  • updated my answer, hopefully it works for you :] Commented May 3, 2012 at 16:22
  • 1
    Code you please provide us with the link to the Codeigniter docs. I found this but couldn't see your code there. Commented May 3, 2012 at 16:24
  • @JerminBazazian That's the link, it's under the section called Escaping Queries (2nd to last section). Commented May 3, 2012 at 16:26

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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.
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
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.
codeigniter seems to call prepared statements "query bindings". its in their documentation.
Okay, thanks. You see my lack of knowledge. I haven't used the bindings either, mostly because of the variables. Again, appreciate your help.
|
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).

Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.