Does Code Igniter automatically prevent SQL injection?

Question

I just inherited a project because the last developer left. The project is built off of Code Igniter. I've never worked with Code Igniter before.

I took a quick look at the code and I see database calls in the controller like this:

$dbResult = $this->db->query("SELECT * FROM users WHERE username = '".$_POST['user_name']."'");

or calls like this:

$dbResult = $this->db->query("SELECT * FROM users WHERE username = '".$this->input->post('username')."'");

Does code igniter automatically sanitize these queries to prevent sql injection?

Answer 1

No, CodeIgniter will not magically sanitize queries which have been built like this.

Answer 2

According to CI's docs here, the framework filters POST on controller construction. It also optionally does XSS filtering either by manually calling the function or setting a global config.

I've never used CI either except just to play with it, so I'm not sure how far I'd trust this.

Answer 3

CodeIgniter DOES ESCAPE the variables you pass by when using the $this->db->query method. But ONLY when you pass the variables as binds, here's an example:

$dbResult = $this->db->query("SELECT * FROM users WHERE username = '?'", array($this->input->post('username')));

Also remember that $_POST shouldn't be preferred over $this->input->post since that it does is check if the variables exists to prevent errors.

Answer 4

No, the code you posted is susceptible to SQL injection. You need to use query binding to construct your SQL queries. If you're using the CI DB library, you would code it something like this (example from the user guide):

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

$this->db->query($sql, array(3, 'live', 'Rick'));

Answer 5

That doesn't escape anything. You are better off changing it to the bind syntax or the active record syntax

Answer 6

You should use $this->input->post, query binding and active record to have the safer data and then still, test test test to be sure.

Answer 7

Code igniter provides a few string escaping functions in its Database layer.

Excerpt from CI Manual:

http://www.codeignitor.com/user_guide/database/queries.html

It's a very good security practice to escape your data before submitting it into your database. CodeIgniter has three methods that help you do this:

1. $this->db->escape() 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: $sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")";

I'd post the other two examples, but I wouldn't want to take all the fun out of reading the manual.