escaping sql - Printable Version

escaping sql - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: escaping sql (/showthread.php?tid=14189)

escaping sql - El Forum - 12-22-2008

[eluser]mradlmaier[/eluser]
Hi All,

I have the following sql:

Code:
$where = "(strcmp(soundex('" . $username . "'), soundex(klang)) = 0) OR username LIKE '%" . $username . "%'";

This works, but as it is good practise, i escape it: $username with $this->db->escape($username) so it looks like this

Code:
$where = "(strcmp(soundex('" . $this->db->escape($username) . "'), soundex(klang)) = 0) OR username LIKE '%" . $this->db->escape($username) . "%'";

and that will produce this error

Quote: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 'michi''), soundex(klang)) = 0) OR username LIKE '%'michi'%' LIMIT 10' at line 6

SELECT profile.id AS `profile_id`, users.id AS `user_id`, users.username AS `username`, geschlechter.name AS `geschlecht`, bundeslaender.name AS `bundesland`, (YEAR(CURRENT_DATE) - YEAR(profile.geburtsdatum)) AS `age` FROM (profile) JOIN users ON users.id = profile.user_id JOIN geschlechter ON geschlechter.id = profile.geschlecht_id JOIN bundeslaender ON bundeslaender.id = profile.bundesland_id WHERE (strcmp(soundex(''michi''), soundex(klang)) = 0) OR username LIKE '%'michi'%' LIMIT 10

So how do i correctly escape the above...?

Michael

escaping sql - El Forum - 12-22-2008

[eluser]anggie[/eluser]
according to the user_guide, you should try this..

Code:
$where = "(strcmp(soundex(" . $this->db->escape($username) . "), soundex(klang)) = 0) OR username LIKE '%" . $this->db->escape($username) . "%'";

escaping sql - El Forum - 12-22-2008

[eluser]Henry Weismann[/eluser]
Notice the single quotes:

Code:
strcmp(soundex(’‘michi’‘), soundex(klang)) = 0) OR username LIKE ‘%‘michi’%’ LIMIT 10

$this->db->escape() This function determines the data type so that it can escape only string data. It also automatically adds single quotes around the data so you don't have to.

The problem is that escape is adding single quotes and you have added them as well which ends up with a bad sql statement.

anggie is right but the like part may need to be different.

escaping sql - El Forum - 12-22-2008

[eluser]Henry Weismann[/eluser]
Try:

Code:
$where = "(strcmp(soundex(" . $this->db->escape($username) . "), soundex(klang)) = 0) OR username LIKE '%" . $this->db->escape_str($username) . "%'";