How to avoid SQL injection in CodeIgniter?

Question

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

Milkandwater

page roytuts.com/prevent-sql-injection-in-codeigniter/ – Pod

It is also good to use xss_clean method from security helper. – Vuillard

Answer 1

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");

Nuncia

Answer 2

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

Halitosis

Answer 3

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

Chamade

Answer 4

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.

Theosophy

Answer 5

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.

Periodate

Answer 6

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

Cither

Answer 7

CodeIgniter provides inbuilt functions and libraries to prevent this :

$this->db->escape();

This function automatically adds single quotes around the data and determines the data type so that it can escape only string data.

Noeminoesis

Hi w.Daya, this information was posted in another answer in 2011. When posting new answers, try to avoid repeating previously posted information. – Megaphone