Escaping SQL Queries in Codeigniter

Escaping SQL queries in Codeigniter

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

SQL query escaping + codeigniter

$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . $this->db->escape($id);

if you want to select the language of the user given by $id it should work that way.

dealing with numbers an alternative would be:

$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . (int)$id;

codeigniter does also support prepared statements as "query bindings":

The secondary benefit of using binds is that the values are
automatically escaped, producing safer queries. You don't have to
remember to manually escape data; the engine does it automatically for
you.

$this-db-escape() function adding single quote in codeigniter

When you use the query builder class to construct your queries the values are escaped automatically by the system so you don't have to use the function $this->db->escape. In your case, each value was escaped by the escape function and the system did it again for each value when executing the insert function.

Now if you want to run custom queries using the function $this-db->query it is a good practice to escape the data like bellow:

$sql = "INSERT INTO table (column) VALUES(".$this->db->escape($value).")";
$this->db->query($sql);

codeigniter escaping queries

This is a bug in CodeIgniter 3.0.0 and 3.0.1. It was fixed in version 3.0.2, and the current stable version is 3.0.3 ...

All you need to do is update your CI setup.

Escape the order by query in codeigniter

Use below code:

$this->db->_protect_identifiers = FALSE;

$this->db->order_by('IFNULL(update_date,create_date)', 'DESC', false);

$this->db->_protect_identifiers = TRUE;

Codeigniter - Automatically escape SQL queries

Referring to here http://codeigniter.com/user_guide/database/queries.html

You may consider using the Query Bind which is at the bottom of the url.
Try to practice binding queries it is faster and cleaner way of doing.

Bind Queries Example:

$sql = "SELECT * FROM some_table WHERE id = ? AND price = ?"; 

$this->db->query($sql, array(3, 100));

Note** with binding method it is always automatically escape.

How to prevent automatically escaping in $this-db-update query in Codeigniter

I structured one solution, though its not a full version but it may help for other users.

In my case I wanted to escape just one column's value so I put that column in set function and all other array in update function like

$this->db->set("col1","value1",FALSE);
$this->db->update("tablenme",$allotherdataarray,$wherearray);

It worked for me.

---

Related Topics