Does CodeIgniter automatically prevent SQL injection?

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?

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 what it does is check if the variables exists to prevent errors.

CodeIgniter provides a few string escaping functions in its database layer.

Excerpt from CI Manual:

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

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

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

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.

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

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.

Use active record for safety and easier coding:

Rather than:

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

Use (same result):

$this->db->where('username',$this->input->post('user_name'));
$dbResult = $this->db->get('users');

It may be a pain but you should convert your queries to active record.

I'm copying from the CodeIgniter manual: "Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system."

And like some people already said, yes this code is susceptible to SQL injection

Optimized with a second post param (TRUE) to filter XSS on the input level:

$this->db->where('username',$this->input->post('user_name', TRUE);
$dbResult = $this->db->get('users');

libraries/input.html

The docs for (at least) 2.2 state, in a big red box:

> Although Active Record will try its best to properly quote any field and table names that you feed it, note that it is NOT designed to work with arbitrary user input. DO NOT feed it with unsanitized user data.

Which to this programmer means "do not rely on Active Record to quote anything".

Using escape function to injection of CI

<?php $username = $this->input->post('username');
$query = 'SELECT * FROM subscribers_tbl WHERE user_name = '.$this->db->escape($email);
$this->db->query($query);?>