How to prevent SQL injection

[eluser]spezia 018[/eluser]
Hi guys.

In my project I have some "native" SQL queries (SELECT * FROM example...) and some queries done through Active Record class.

In the entire system, we retrieve the data by using $this->input->post() instead of using $_POST global variable.

Now, I would like to improve system security by adding mysql_real_escape_string() function as a validation of each variable that comes via POST request.

I was wondering if it's wise to extend Input class, then override the post() method? I've heard that is good practice to do data validation just before doing MySQL queries (in models) instead of global processing POST variable. What do you think?

[eluser]Otemu[/eluser]
Hi,

Check out the http://ellislab.com/codeigniter/user-gui...ation.html

In addition to the validation functions like the ones we used above, you can also prep your data in various ways. For example, you can set up rules like this:

Code:

$this->form_validation->set_rules('username', 'Username', 'trim|required|min_length[5]|max_length[12]|xss_clean');
$this->form_validation->set_rules('password', 'Password', 'trim|required|matches[passconf]|md5');
$this->form_validation->set_rules('passconf', 'Password Confirmation', 'trim|required');
$this->form_validation->set_rules('email', 'Email', 'trim|required|valid_email');

In the above example, we are "trimming" the fields, converting the password to MD5, and running the username through the "xss_clean" function, which removes malicious data.

[eluser]CroNiX[/eluser]
If you use CI's active record or query bindings for database operations, all variables are already been run through mysql_real_escape_string(). I'd convert your "native" sql queries to Active Record, or at least run any variables through $this->db->escape() before executing them in a query. To me, the database layer should be taking care of using database functions, like mysql_real_escape_string(), and not somewhere else like in input::post().

You might also run into some problems if you do it on post variables, since they get run through form validation (or should be), and if some things are escaped it could cause them to fail validation as the data has changed and things added.