SQL injection on active record : $this->db->where()

[eluser]nasu[/eluser]
I do not know if this is normal, with "active record" enabled, I have a SQL injection, see below:

First test :

Code:

$ip = "' --";
$where = "ip = '$ip' AND (`end_ban` > '".date('Y-m-d G:i:s')."' OR `end_ban` IS NULL)";

Second test :

Code:

$ip = '" --';
$where = 'ip = "'.$ip.'" AND (`end_ban` > "'.date('Y-m-d G:i:s').'" OR `end_ban` IS NULL)';

Last test

Code:

$ip = "' --";
$where = 'ip = "'.$ip.'" AND (`end_ban` > "'.date('Y-m-d G:i:s').'" OR `end_ban` IS NULL)';

It's my SQL request :

Code:

$this->db->select('id, end_ban');
$this->db->from('member_ban');
$this->db->where($where);
return $this->db->get();

In the first test, I get this :

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2011-05-18 8:58:03' OR `end_ban` IS NULL)' at line 3

SELECT `id`, `end_ban` FROM (`member_ban`) WHERE `ip` = '' --' AND (`end_ban` > '2011-05-18 9:59:32' OR `end_ban` IS NULL

in the second test :

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2011-05-18 10:01:01" OR `end_ban` IS NULL)' at line 3

SELECT `id`, `end_ban` FROM (`member_ban`) WHERE `ip` = "" --" AND (`end_ban` > "2011-05-18 10:01:01" OR `end_ban` IS NULL)

Last test :
everything works


I looked in the user guide to find answers, here's what I found:
« $this->db->where();
Note: All values passed to this function are escaped automatically, producing safer queries.
[...]
4. Custom string: You can write your own clauses manually:
[...]
$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.
»

I tried this with the IP field voluntarily, although I check the validity of the IP before doing my SQL query. I wanted to verify that there was no SQL injection possible for my other queries that could use a text field.

Can you tell me if it's a bug or if I misread the user guide ?
Thank you.

[eluser]Eric Barnes[/eluser]
The active record will escape queries but not when you manually create the where portion.

Rewriting your query to $this->db->where('ip', $ip); will then auto escape it. Here is a full example:

Code:

$where = '(`end_ban` > "'.date('Y-m-d G:i:s').'" OR `end_ban` IS NULL)';
$this->db->select('id, end_ban')
->from('member_ban');
->where('ip', $ip)->where($where);
return $this->db->get();

[eluser]nasu[/eluser]
Thank you for your response and also for what you bring to the community.
In this case I prefer not to use the active record.

[eluser]Eric Barnes[/eluser]
Also you could use the mysql_real_escape_string. IE:

Code:

$where = 'ip = "'.mysql_real_escape_string($ip).'" AND (`end_ban` > "'.date('Y-m-d G:i:s').'" OR `end_ban` IS NULL)';

OR:

Code:

$where = 'ip = "'.$this->db->escape_str($ip).'" AND (`end_ban` > "'.date('Y-m-d G:i:s').'" OR `end_ban` IS NULL)';

[eluser]DeaD SouL[/eluser]
Hi,

I think this will auto escape it too:

Code:

$query = 'SELECT id, end_ban FROM member_ban WHERE ip = ? AND (`end_ban` > ? OR `end_ban` IS NULL)';
$binds = array( $ip, date('Y-m-d G:i:s') );
return $this->db->query( $query, $binds );

If I'm wrong please correct me.