Does CodeIgniter automatically 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'));