How can i prevent sql injection with codeiginiter

Question

I want to prevent my statement for injection but i am getting confused about active records and query bindings.

This is my current mysql query called results.

$results = $this->EE->db->query("SELECT t.transactionid, t.transactiontime, t.created, ct.title, cd.field_id_6, cd.field_id_5, cd.field_id_7, t.pricebefordiscount, t.priceafterdiscount, t.error, t.cardid, em.email, emd.m_field_id_2, emd.m_field_id_6, emd.m_field_id_5, emd.m_field_id_7, emd.m_field_id_4, t.restaurant_id
FROM exp_members as em
   INNER JOIN transactions as t on (em.member_id = t.cardid-10000000)
   INNER JOIN exp_channel_titles as ct on (t.restaurant_id = ct.entry_id)
   INNER JOIN exp_channel_data as cd on (ct.entry_id = cd.entry_id)
   INNER join exp_member_data as emd on em.member_id = emd.member_id
WHERE em.member_id = '".($_GET['cardid']-10000000)."'");

And this is how i tried to prevent mysql injection. Is this safe enough?

$results = $this->EE->db->query("SELECT t.transactionid, t.transactiontime, t.created, ct.title, cd.field_id_6, cd.field_id_5, cd.field_id_7, t.pricebefordiscount, t.priceafterdiscount, t.error, t.cardid, em.email, emd.m_field_id_2, emd.m_field_id_6, emd.m_field_id_5, emd.m_field_id_7, emd.m_field_id_4, t.restaurant_id
FROM exp_members as em
   INNER JOIN transactions as t on (em.member_id = t.cardid-10000000)
   INNER JOIN exp_channel_titles as ct on (t.restaurant_id = ct.entry_id)
   INNER JOIN exp_channel_data as cd on (ct.entry_id = cd.entry_id)
   INNER join exp_member_data as emd on em.member_id = emd.member_id
WHERE em.member_id = '".$this->db->escape(($_GET['cardid']-10000000))."'");

But is this also an option or ?

$this->load->database();
$this->load->library('table');

$this->db->select(' t.transactionid, t.transactiontime, t.created, ct.title, cd.field_id_6, cd.field_id_5, cd.field_id_7, t.pricebefordiscount, t.priceafterdiscount, t.error, t.cardid, em.email, emd.m_field_id_2, emd.m_field_id_6, emd.m_field_id_5, emd.m_field_id_7, emd.m_field_id_4, t.restaurant_id');
$this->db->from('exp_members'); 
$this->db->join('transactions', 'exp_members.member_id = transactions.cardid-10000000', 'inner');
$this->db->join('exp_channel_titles', 'transactions.restaurant_id = exp_channel_titles.entry_id', 'inner');
$this->db->join('exp_channel_data', 'exp_channel_titles.entry_id = exp_channel_data.entry_id', 'inner');
$this->db->join('exp_member_data', 'exp_members.member_id = exp_member_data.member_id', 'inner');
$this->db->where('exp_members.member_id', $this->db->escape(($_GET['cardid']-10000000))); 
$query = $this->db->get();
echo $query;

Is this safe enough or right approach or am i missing something.

score 1 · Answer 1 · answered May 07 '13 at 21:10

1

Last two approaches are correct to avoid SQL injection. On last code, using Active Record, you don't need to call escape as CodeIgniter will do it automatically.

answered May 07 '13 at 21:10

Alex Barroso

839
6
14

In approach nr 2: which is right _______________ WHERE em.member_id = '".$this->db->escape(($_GET['cardid']-10000000))."'"); Or WHERE em.member_id = '?'", array($this->input->get('cardid'-10000000))); – Zaz May 07 '13 at 21:22
Both are correct but the second method is cleaner. On both methods you don't need to write quotes. $this->db->escape already adds quotes and you just need to write em.member_id = ? and CodeIgniter will set quotes automatically http://ellislab.com/codeigniter/user-guide/database/queries.html – Alex Barroso May 07 '13 at 21:36

How can i prevent sql injection with codeiginiter

1 Answers1