current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 3 down vote favorite

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.

share|improve this question
1  
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. – Dan F. Sep 28 '12 at 16:26
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.