how to avoid sql injection in codeigniter

user667030 picture user667030 · May 2, 2011 · Viewed 46.5k times · Source

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

Rocket Hazmat picture Rocket Hazmat · May 2, 2011

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