how to avoid sql injection in codeigniter

Question

In CodeIgniter, how can I avoid sql injection? Is there any method to set in config file to avoid sql injection? I am using this code for selecting values:

$this->db->query("SELECT * FROM tablename WHERE var='$val1'");

and this for inserting values:

$this->db->query("INSERT INTO  tablename (`var1`,`var2`) VALUES ('$val1','$val2')");

Another method used to insert and select values from the database is CodeIgniter's insert() and get() methods. Is any chance to sql injection while using CodeIgniter's bulit-in functions

Answer 1

In CodeIgniter: There are 2 action to prevent SQL Injection. For those who are novelty in web programming, another kind of security hole in web programming which can be fatal because it can expose 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.

Answer 2

You can check if you var contain only letters of numbers, meaning you var mast be in you defined format. before you insert it into the query

Answer 3

While accepting value from client side, Better to use this code,

$client = $this->input->post('client',TRUE);

While inserting better to use codeigniter inserting method,

$this->db->insert('tablename',$values);

When using this method codeingniter automatically do all escape so we no need do escape manual.

Answer 4

You should try to avoid writing your queries directly into a string and then passing them to the query function. A better option would be to use the Active Record class which will build your queries for you and escape the values. http://codeigniter.com/user_guide/database/active_record.html

If you want to avoid using the Active Record class for whatever reason then you can view the Codeigniter documentation for the database class which has an escape method for escaping your values before passing them to the query method. http://www.codeignitor.com/user_guide/database/queries.html

Ben

Answer 5

CodeIgniter's Active Record methods automatically escape queries for you, to prevent sql injection.

$this->db->select('*')->from('tablename')->where('var', $val1);
$this->db->get();

or

$this->db->insert('tablename', array('var1'=>$val1, 'var2'=>$val2));

If you don't want to use Active Records, you can use query bindings to prevent against injection.

$sql = 'SELECT * FROM tablename WHERE var = ?';
$this->db->query($sql, array($val1));

Or for inserting you can use the insert_string() method.

$sql = $this->db->insert_string('tablename', array('var1'=>$val1, 'var2'=>$val2));
$this->db->query($sql);

There is also the escape() method if you prefer to run your own queries.

$val1 = $this->db->escape($val1);
$this->db->query("SELECT * FROM tablename WHERE var=$val1");

Answer 6

you can use

$this->db->escape()

method..

$sql = "INSERT INTO table (title) VALUES(".$this->db->escape($omgomg).")";

other methods are listed here.

http://codeigniter.com/user_guide/database/queries.html