phpsqlmysqlcodeigniterquoting

Despite the name escape(), CodeIgniter's escaping method is adding quotes around my already manually quoted strings

I'm trying to do an update to my database. One of the column values contains apostrophes, etc. I have used $this->db->escape in CodeIgniter around the strings that may contain such characters, but I still get the following error:

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 'O\'Keeffe, O\'Keefe'' WHERE `survey_id` = 188' at line 1

UPDATE `survey` SET `firstname_confidence_score` = 100, `firstname_rhymes` = '''', `lastname_confidence_score` = 85, `lastname_rhymes` = ''O\'Keeffe, O\'Keefe'' WHERE `survey_id` = 188;

How do I fix this?

UPDATE:

$sql = "UPDATE `$table_name` SET `firstname_confidence_score` = $firstname_confidence_score, `firstname_rhymes` = '" . $this->db->escape($firstname_rhymes) . "', `lastname_confidence_score` = $lastname_confidence_score, `lastname_rhymes` =  '" . $this->db->escape($lastname_rhymes) . "' WHERE `$primary_id` = $id;";
$result = $this->db->query($sql);
Solution

  • Since you are using $this->db->escape(), you are automatically adding single quotes around the data.

    You query simply needs to be:

     $sql = "UPDATE `$table_name` 
         SET `firstname_confidence_score` = $firstname_confidence_score,  
             `firstname_rhymes` = " . $this->db->escape($firstname_rhymes) . ",  
             `lastname_confidence_score` = $lastname_confidence_score, 
             `lastname_rhymes` =  " . $this->db->escape($lastname_rhymes) . 
        "WHERE `$primary_id` = $id;";

    You do not need the single quotes around $this->db->escape($firstname_rhymes) and so on.