Prevent SQL Injection in CodeIgniter

Question

does CI Active Record Prevent SQL Injection?

here's my code

    $this->db->select('t.DateTime,
                       su.FirstName,
                       su.MiddleName,
                       su.LastName,
                       l.Description,
                       t.Temperature,
                       t.UnitID,
                       t.Humidity');
    $this->db->from('temperaturelogs as t');
    $this->db->join('systemusers as su', 'su.UserID = t.Encoder');
    $this->db->join('locations as l', 't.LocationID = l.LocationID');
    $this->db->where("Cast(t.DateTime as date) >= '$start_date'");
    $this->db->where("Cast(t.DateTime as date) <= '$end_date'");
    $query = $this->db->get();

    if ($query->num_rows() > 0) 
    {
        return $query->result_array();
    }

when I tried to Enter this Input in End Date.

My Input: '; Truncate Table systemusers; #

Gives me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'truncate table systemusers; #'' at line 6

SELECT t.DateTime, su.FirstName, su.MiddleName, su.LastName, l.Description, t.Temperature, t.UnitID, t.Humidity FROM temperaturelogs as t JOIN systemusers as su ON su.UserID = t.Encoder JOIN locations as l ON t.LocationID = l.LocationID WHERE Cast(t.DateTime as date) >= '2016-03-21' AND Cast(t.DateTime as date) <= ''; truncate table systemusers; #'

the error doesn't have any relevant to my question...

which CI version are you using `echo CI_VERSION;` ? – viral Mar 22 '16 at 05:42 — viral, Mar 22 '16 at 05:42
@Viral i'm using the latest version. – Sam Teng Wong Mar 22 '16 at 05:55 — Sam Teng Wong, Mar 22 '16 at 05:55

score 2 · Accepted Answer · answered Mar 22 '16 at 05:59

2

Try,

$this->db->where('Cast(t.DateTime as date) >=', $start_date);
$this->db->where('Cast(t.DateTime as date) <=', $end_date);

answered Mar 22 '16 at 05:59

viral

3,724
1
18
32

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

Use $this->db->escape($start_date); in your where clause.

Have a look on following SQL query escaping + codeigniter

Your Active Query would then become something like this

$this->db->select('t.DateTime,
                       su.FirstName,
                       su.MiddleName,
                       su.LastName,
                       l.Description,
                       t.Temperature,
                       t.UnitID,
                       t.Humidity');
    $this->db->from('temperaturelogs as t');
    $this->db->join('systemusers as su', 'su.UserID = t.Encoder');
    $this->db->join('locations as l', 't.LocationID = l.LocationID');
    $this->db->where("Cast(t.DateTime as date) >= '".$this->db->escape($start_date)"'");
    $this->db->where("Cast(t.DateTime as date) <= '".$this->db->escape($end_date)"'");
    $query = $this->db->get();

edited May 23 '17 at 11:59

Community

1
1

answered Mar 22 '16 at 05:22

Muhammad Usama

247
1
2
8

but I am using `Active Record`. – Sam Teng Wong Mar 22 '16 at 05:26
That's fine, you can still use variables in it. I have updated my response to include your query. – Muhammad Usama Mar 22 '16 at 05:28
but I use `$this->input->post()`.. does this not escape the date? – Sam Teng Wong Mar 22 '16 at 05:37
Well syntax error says something else. Regardless, I think you should also put extra validations for data types. $start_date/$end_date in this case before you use it in your active query directly. – Muhammad Usama Mar 22 '16 at 05:46
yes... but this solves my problem... I thought `$this->input->post()` escapes the inputs. – Sam Teng Wong Mar 22 '16 at 05:57

score 0 · Answer 3 · answered Mar 22 '16 at 06:00

CodeIgniter provides you to set custom key/value method in sql where clause using the following given method.

You can include an operator in the first parameter in order to control the comparison:

$this->db->where('name !=', $name);
$this->db->where('id <', $id);
// Produces: WHERE name != 'Joe' AND id < 45

Prevent SQL Injection in CodeIgniter

3 Answers3