Does codeigniter $this->db->query() or $this->db->

问题:

I want to ask about these 2 method $this->db->escape() and $this->db->query()

Do those can prevent SQL Injection?

回答1:

By default query() does not have any injection prevention methods unless you are using binds.

$this->db->query("SELECT * FROM sometable WHERE column1 = '?'", array($this->input->post('someitem')));

However, if you use query builder: get(), insert(), and update() as well any of the other query builder methods pass the incoming data through a function that among other things, escapes the data.

At face value, escaping the data is part of preventing SQL injection, and the other part is prepared queries (which with traditional php is accomplished using PDO and prepared statements). As query builder carries most of the load for you I wouldn't worry about that too much.

In CI you are safe doing:

$item = $this->input->post('someitem');

$this->db->insert('sometable', array('column1' => $item));

The insert function will escape $item automatically.

You can also do XSS cleaning of the post via $item = $this->input->post('someitem', TRUE);

However you should escape your gets/posts if you are going directly into query()

$item = $this->input->post('someitem');

$item_esc = $this->db->escape_str($item);

$this->db->query("INSERT INTO `sometable` (`column1`) VALUES ('{$item_esc}')");

回答2:

First of all, you should have clear idea of what is db->escape() and db->query() does.

---

db->escape()

This used for users who use Regular Queries rather Query Builder Class. When we use a direct query to DB, there is a high vulnerability to security. So CI made escape() from those attacks. And there are 3 methods we can use and escape() is one of them.

db->query()

This method was added to version 1.5.0 and it's still there in CI database class with major+minor improvements in v1.5 - V3.0( v4 too). This use to execute complex queries and SP in an easy way with better understanding. This method just run a query whatever inside this query() and it has no any SQL injection preventation.

---

escape() can use separately?

No. In order to use these Escaping methods in CI, you have to use query(). escape(), escape_str() and escape_like_str() were added to SQL queries and obviously they need query() to perform any sort of action.

---

Do those can prevent SQL Injection?

Yes of course. They're there to help with security. And read Input Security Filtering post('some_data', TRUE); and Security for better understanding.