12

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?

Nyxynyx
  • 61,411
  • 155
  • 482
  • 830

2 Answers2

26

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']));
Yan Berk
  • 14,328
  • 9
  • 55
  • 52
  • 2
    You can make this even simpler: `$this->db->query($sql, $data);` This works for me! (I guess you shouldn't have any other keys in the data array for it to work) – Saneem Jan 18 '14 at 10:00
  • Or rewrite it to Active Record Pattern: `$this->db->inset('some_table', $data);` much more straight forward and very easy. Just make sure that no auto-increment primary key field makes it into `insert()` method by using `if (!isset($data['id'])) throw new { InvalidArgumentException('data[id] is not allowed for insert'); }` – Roland Sep 19 '17 at 10:15
10

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

rybo111
  • 12,240
  • 4
  • 61
  • 70
Moyed Ansari
  • 8,436
  • 2
  • 36
  • 57
  • 4
    At the very end of the page, the section about parameter binding is a *much* better option ;) – d11wtq Jun 10 '12 at 12:14
  • updated link: https://www.codeigniter.com/userguide2/database/queries.html – Frank Conry Jan 31 '17 at 16:41
  • $this->db->escape() adds the single quotes so we don't have to, yet in your query you have added them.(I don't know if its the version difference or what, I am using 3.1.*) Earlier today I was writing a query and then I faced a Problem where I had made this mistake. – yaxe Feb 20 '18 at 21:20