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?

回答1:

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

回答2:

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