CodeIgniter: Two action to prevent SQL Injection

In my previous post, I did explain to you about how to prevent XSS injection for your CodeIgniter Apps. And yet, for those who are novelty in web programming, I tell you the another kind of security hole in web programming which can be fatal because It can exposes your inner side of application’s database, it is SQL Injection.

And thankfully again, Codeigniter has capability to deal with it. But unfortunately, many of CI programmer I collaborated (and even you) did (or might) forget this two action to prevent any circumstances of SQL injection.

Stick with ActiveRecord capability

The first thing is do not in any circumstances dealing with querying the data by using full query like this :

$this->db->query("select * from users where user=$user and password=$password")

You don’t know what exactly inside $user or $password variable when it comes to user who will do deliberately the wrong thing. Even XSS sanitiser won’t deal with someone who inputs combination of quote, semicolon or dash character in it. So in this case, you need to learn this Active Record thing because it has input sanitiser capability dedicated to prevent SQL injection. And don’t worry, it support kind of function chaining like this :

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);

$query = $this->db->get();

But remember, it won’t work if you still do combining usual (partially) query function inside of active record function like this :

$query = $this->db->where("title LIKE '%$input%'");

Which actually could be changed like this.

$query = $this->db->like("title", $input);

The point is, use every bit of possibility of CodeIgniter’s Active Record and don’t mess with it.

But If that ain’t work, there is an alternative

If you have a very long query and don’t bother to convert it to Active Record’s style, you can sanitised your input manually by using this function :

$sanitised_title = $this->db->escape($title);

// For use inside LIKE query
$sanitised_title = $this->db->escape_like_str($title);

And you can safely concatenate the sanitised/escaped input inside your query.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *