CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

CodeIgniter - escaping SQL

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);

Upvotes: 0

Views: 1370

Answers (2)

Marc Audet
Marc Audet

Reputation: 46785

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.

Upvotes: 3

Chris Bier
Chris Bier

Reputation: 14437

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

You had double apostraphes around the lastname_rhymes value.

Upvotes: 1

Related Questions