How to write an INSERT IGNORE query with PHP variables in CodeIgniter

Question

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?

And what about in a SELECT query? How I can escape them?

Fernando Torres
– Fernando Torres

2018-03-23 03:29:08 +00:00
Commented Mar 23, 2018 at 3:29 — Fernando Torres
– Fernando Torres, Commented Mar 23, 2018 at 3:29

miken32 · Accepted Answer · 2025-06-10 21:22:21Z

26

It is unsafe not to use Query Binding. This will automatically escape all the values:

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

edited Jun 10 at 21:22

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Jun 10, 2012 at 12:14

Yan Berk

14.5k9 gold badges58 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saneem Over a year ago

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)

Roland Over a year ago

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

rybo111 · Accepted Answer · 2014-05-29 19:24:12Z

11

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

edited May 29, 2014 at 19:24

rybo111

12.6k4 gold badges64 silver badges74 bronze badges

answered Jun 10, 2012 at 12:08

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

4 Comments

d11wtq Over a year ago

At the very end of the page, the section about parameter binding is a much better option ;)

Frank Conry Over a year ago

updated link: codeigniter.com/userguide2/database/queries.html

yaxe Over a year ago

$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.

mickmackusa Jun 21 at 1:26

The doc quoted text and the implemented snippet are contradictory. escape() adds quotations around values as needed, and yet the snippet is erroneously adding quotations manually.

Collectives™ on Stack Overflow

How to write an INSERT IGNORE query with PHP variables in CodeIgniter

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related