CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Nyxynyx
Nyxynyx

Reputation: 63599

Escaping SQL queries in Codeigniter

I am inserting some data into a MySQL table using CodeIgniter. Because I am using INSERT IGNORE INTO and do not want to edit the active records class to enable this feature, I am generating the SQL query manually.

$this->db->query("INSERT IGNORE INTO my_table(lat, lng, date, type)
                        VALUES ('" . $data['lat'] . "', '" . $data['lng'] . "', '" . $data['date'] . "', '" . $data['type'] . "')");

Problem: The query failed when the string in $data['type'] contained a single quote. How can I make it such that these characters that need to be escaped gets escaped automatically, like when using Active records?

Upvotes: 12

Views: 39321

Answers (2)

Moyed Ansari
Moyed Ansari

Reputation: 8461

use $this->db->escape(); it will escape the string automatically

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:

$this->db->query("INSERT IGNORE INTO my_table(lat, lng, date, type)
VALUES ('" . $this->db->escape($data['lat']) . "', '" . $this->db->escape($data['lng']) . "', '" . $this->db->escape($data['date']$this->db->escape . "', '" . $this->db->escape($data['type']) . "')");

Here is the reference Click Here

Upvotes: 11

Yan Berk
Yan Berk

Reputation: 14428

Another way is to use Query Binding which automatically escapes all the values:

$sql = "INSERT IGNORE INTO my_table(lat, lng, date, type) VALUES (?,?,?,?);"; 
$this->db->query($sql, array($data['lat'], $data['lng'], $data['date'], $data['type']));

Upvotes: 26

Related Questions