Codeigniter active record

Question

I understand that I should use prepared statements to prevent SQL injection. Unfortunately, having read the PHP docs on the matter I am none the wiser. Here is one of my simple active record SQL queries in a CI2 model, could someone show me an example of how I might turn this into a prepared statement - do I even need to?

function get_item($id){
 $this->db
 ->select('*')
 ->from('item_entries')
 ->where('item_entries.item_id', $id)
 ->join('item_categories_rel', 'item_categories_rel.item_id = item_entries.item_id');
 $query = $this->db->get();
 if(is_object($query)){return $query->result_array();}else{return $query;}
 }

Am I correct in thinking prepared statements are only necessary if I am accepting user data - if so lets assume $id is user submitted. Although it is not, I am about to write some form to db statements, so advice in preparation for this is appreciated.

• Dan F.: Your code is fine, everything that goes through ActiveRecord is properly escaped. Unless you do your own where statements, things like $this->db->where('col = ' . $value), you are safe.

Answer 1

I'm not really sure if this question is on topic, but I just started using SQL myself, so here's a chance for me to apply what I've learned. The following is using PDO.

//unspecified placeholder
$sql = "SELECT * FROM item_entries WHERE item_id=?";
$sth = $dbh->prepare( $sql );
$sth->execute( array( $id ) );
//specific placeholder
$sql = "SELECT * FROM item_entries WHERE item_id=:id";
$sth = $dbh->prepare( $sql );
$sth->execute( array( ':id' => $id ) );

The idea being that the SQL statements don't have values directly injected into them by using placeholders. In the first example that placeholder was unspecified ?. You can use any number of unspecified placeholders, but then the array you pass in to the execute() function has to be in the same order. While this may not be a big deal for one input, it could quickly become tedious trying to keep track of multiples. Which is where the second example comes in. It uses specific placeholders :id to define keys in the execution array. This means these elements can be in any order so long as the correct key is associated with the correct input.

As for if this is only necessary for user data? I don't know, but I'm just as prone to make mistakes as users, so it couldn't hurt. I'm quite interested to here if I got this right as well.