CODEWITHSUNDEEP
phpmysqlcodeigniter
Sam Teng Wong
Sam Teng Wong

Reputation: 2439

Prevent SQL Injection in CodeIgniter

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...

Upvotes: 0

Views: 470

Answers (3)

Vikash Kumar
Vikash Kumar

Reputation: 1111

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

Upvotes: 0

viral
viral

Reputation: 3743

Try,

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

Upvotes: 2

Muhammad Usama
Muhammad Usama

Reputation: 247

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

Upvotes: 0

Related Questions