sql escaping errors once again...

[eluser]miau[/eluser]
Hi,

I'm a bit confused with automatic query escaping in CI.

Manual recommends this approach:

Code:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

But when I run this code on my machine:

Code:

$sql = 'test\\';        
$this->db->query("select user_id from user where name=?", array($sql));

i get:

An Error Was Encountered

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 ''ds\'' at line 1

select * from user where name='ds\'

My config:

* Apache 2.0.59
* PHP 5.2.4, magic_quotes_gpc=Off
* MySql 5.0.5

There's a lot on discussion on this topic, but i haven't found any clear answer how can I use automatic escaping instead of doing manual escape on each variable used in SQL query.

BTW maybe it's a good idea to add a notice in manual that query bindings doesn't work?

Greetings !
miau

[eluser]mamboo[/eluser]
The problem appears when you use bindings, line 577 in DB_driver.php:

Code:

$sql = preg_replace("#".preg_quote($this->bind_marker, '#')."#", str_replace('$', '\$', $val), $sql, 1);

ugly fix:

Code:

$sql = preg_replace("#".preg_quote($this->bind_marker, '#')."#", str_replace('$', '\$', str_replace('\\','\\\\',$val)), $sql, 1);

Hope this helps.

Pisi

[eluser]Pygon[/eluser]
You need to escape your backslashes in PHP. Therefore, if you want "\\" to be output, your variable needs to contain "\\\\". It's not really the responsibility of the db driver to ensure you've properly escaped your backslashes in my opinion.

[eluser]mamboo[/eluser]
[quote author="Pygon" date="1205883696"]You need to escape your backslashes in PHP. Therefore, if you want "\\" to be output, your variable needs to contain "\\\\". It's not really the responsibility of the db driver to ensure you've properly escaped your backslashes in my opinion.[/quote]

Like the User Guide says, 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..
Everything else it's escaped by

Code:

$val=$this->escape($val);

but

Code:

preg_replace()

in line 577 removes 1 of 2 backslashes from the new $val, so you need a fix for this.