SQL Injection and Codeigniter

Question

Some doubts regarding Codeigniter and its Input handling capabilities. Some may be a little weird but they are doubts none-the-less.

1. If I use the Active Record Class functions in CodeIgniter, is my input prevented against SQL injection?
2. I read somewhere that it does, but I don't understand it how? or why?
3. Also does xssclean deal with SQL injection in any way?

Answer 1

is my input prevented against SQL injection?

Not exactly ‘automatically’, but it does provide parameterised queries. CodeIgniter or no, you should use parameterised queries in preference to query string hacking whenever possible.

$bof= "a'b";
$zot= 'a\b';

// Insecure! Don't do this!
//
$this->db->query("SELECT foo FROM bar WHERE bof='$bof' AND zot='$zot'"); 

// Secure but annoying to write
//
$this->db->query("SELECT foo FROM bar WHERE bof='".$this->db->escape($bof)."' AND zot='".$this->db->escape($zot)."'"); 

// This is what you want
//
$this->db->query('SELECT foo FROM bar WHERE bof=? AND zot=?', array($bof, $zot)); 

Note this is nothing to do with ‘input’: when you make an SQL query from strings you must use parameterisation or escaping to make them fit, regardless of whether they're user input or not. This is a matter of simple correctness; security is a side-effect of that correctness.

Similarly when you output text into HTML, you need to HTML-encode <, & and " characters in it then. It is absolutely no use trying to fiddle with the input to escape or remove characters that might be troublesome in the future if you happen to use them without escaping in SQL or HTML. You'll mangle your output by having unexpected SQL-escaping in HTML (which is why you see self-multiplying backslashes in badly-written apps) and unwanted HTML-escaping in SQL. And should you take text from somewhere other than that direct user input (say, material already in the database) you aren't protected at all.

Also does xssclean deal with SQL injection in any way?

No. It's aimed at HTML injection. But it's worse than worthless. Never use it.

“XSS filtering” is completely bogus (again, CodeIgniter's or anyone else's). XSS needs to be prevented by correctly HTML-escaping output, not mangling input. XSS filtering will not adequately protect you if your application is not already secure; at best it will obfuscate your existing flaws and give you a false sense of security. It will also mangle a lot of valid input that CI thinks looks like tags.

Answer 2

1. it does if you do it properly

2. You will probably have noticed that all function calls are in a way that user data is passed in one variable each. So you don't even have the possibility to pass SQL controll code and user data in one variable. Speaking short, data is encapsulated in one variable each. Therefore it can be safely encoded without breaking your SQL code. The exception is however if you pass yóur whole query. Then its not possible. If you do

$db->query("select * from table where password = 'hello ' or '1=1");

there is no way of telling what should be escaped and whats not but if you quote it in like this

$db->query("select * from table where password = ?",array('param1'));

the user variable gets encapsulated in one variable and will be escaped.

3. Yes it does but its primpary purpose is not to prevent sql injection, i would rather rely on http://codeigniter.com/user_guide/libraries/input.html