how to check sql injection in codigniter?

Question

I am working on codeigniter project. How can I know queries function in my models are preventing the sql injection. Even I'm using different ways to insert data but how can i make sure that which one is safe .

Here's my code:

1) *****************
    $data = array(
   'name' => $_POST['name'],
   'email' => $_POST['email'],
   'phone' => $_POST['phone'],
   'city' => $_POST['city'],
   'current_salary' => $_POST['current_salary'],
   'expected_salary' => $_POST['expected_salary'],
   'reume_link' => $file_name,
   'status' => 0,
   );
   $this->db->insert('my_table_name', $data);

2) **************************
    $query = $this->db->query('SELECT  distinct(name) as name   FROM `my_table_name` WHERE city like "%'.$_POST['state'].'%" ');
    $res = $query->result_array();

3) **************************    
    $query = $this->db->query("insert into my_table_name(nid,sid,cid,data) values('766','$sid',1,'".$_POST['adm_name']."')");

Are the codeigniter function prevent sql injection default or I strictly need to use prepare statement / bind parameter.

Are the simple CI function not safe to use ?

(1) is perfectly fine. Both (2) and (3) good examples of what not to do. It's not even more readable to eschew the reliable approach. So what's the purpose of this question? — mario, Jul 09 '15 at 12:05
Check this [link](http://stackoverflow.com/questions/5857386/how-to-avoid-sql-injection-in-codeigniter); — Saty, Jul 09 '15 at 12:11
@mario things you explain really helpful for me . You mean $this->db->query('my query ') needs to use escape function for every entity. — Vipul sharma, Jul 09 '15 at 12:19
1) is fine each var will be escaped correctly and will be safe. For extra safety you can tell it the use the mysqli or pdo libraries which will use prepared statements. 2 + 3) Don't EVER use queries like this in a production environment. — mic, Jul 09 '15 at 12:39

score 1 · Answer 1 · answered Jul 09 '15 at 12:08

1

You should use $this->db->escape_str for every variable you put inside your query. Another option (even a better one) is to use prepared statements.

answered Jul 09 '15 at 12:08

Dekel

60,707
10
101
129

score 1 · Answer 2 · edited Jul 09 '15 at 15:43

1

Here is an example of how to use prepared statements -

$sql = 'SELECT distinct(name) name FROM `my_table_name` WHERE city like ?';
$query = $this->db->query($sql, array("%$_POST[state]%"));

means you have to substitute actual data with ? marks and pass it in the form of array as a second parameter.

Most of ActiveRecord methods (like insert, get and such) are safe too, as long as you are following guidelines.

edited Jul 09 '15 at 15:43

Flexo

87,323
22
191
272

answered Jul 09 '15 at 12:17

Your Common Sense

156,878
40
214
345

@mario said my first way (in my question is perfect ) . can you advise how can i check it to make sure . – Vipul sharma Jul 09 '15 at 12:23
Please let's not go down this road. – Flexo Jul 09 '15 at 15:44

score 0 · Answer 3 · answered Jul 09 '15 at 12:44

0

If you were using CodeIgniter's Active Record methods it'll automatically escape queries for you to prevent injection.

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

If you don't want to use CI Active Records then there's a function i.e $this->db->escape() in CI

$data1 = $this->db->escape($data1);
$this->db->query("SELECT * FROM table_name WHERE var = '$data1'");

Or you can use query bindings as

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

Even instead of using $_POST and $_GET CI have its method of $this->input->post() and $this->input->get() respectively

answered Jul 09 '15 at 12:44

Narendrasingh Sisodia

21,247
6
47
54

i am getting different way to prevent but nobody is advising how can i check . – Vipul sharma Jul 09 '15 at 12:50
What you needed to be advised can you elaborate – Narendrasingh Sisodia Jul 09 '15 at 13:11
i want to make sure all my queries are safe are prevent the sql injection so how can i test.. dosen't matter whatever method i am using – Vipul sharma Jul 09 '15 at 13:23
Then you need to use Active records instead or need to escape your variables before querying http://stackoverflow.com/questions/5857386/how-to-avoid-sql-injection-in-codeigniter?lq=1 – Narendrasingh Sisodia Jul 09 '15 at 13:34
@Uchiha, I need you help. Look here : http://stackoverflow.com/questions/38607368/how-to-implement-in-to-prepare-statement?noredirect=1#comment64603092_38607368 – samuel toh Jul 27 '16 at 09:26

score -1 · Answer 4 · edited Jul 09 '15 at 12:04

-1

For protection always use $this->input->post('name_of_input') and $this->input->get('name_of_input') instead of $_POST[] & $_GET[]

edited Jul 09 '15 at 12:04

Saty

22,443
7
33
51

answered Jul 09 '15 at 12:04

acesta

79
11

score -1 · Answer 5 · answered Jul 09 '15 at 12:31

-1

check this link for documentation

in config.php set

$config['global_xss_filtering'] = True;

or use xss_clean in validation, check the link for documentation

answered Jul 09 '15 at 12:31

parth

1,803
2
19
27

how to check sql injection in codigniter?

5 Answers5