How can I Use Prepared Statements in CodeIgniter

Question

Hi all I need to use Prepared Statements in my site. I tried use this

$sql = "SELECT * FROM tbl_user WHERE uid=:id and activation_key=:key";
$query = $this->db->query( 
    $sql, 
    array( ':id' => $uid ,':key' => $activation_key)
);

but this is not working. When I change :id and :key to ? its working.

Answer 1

CodeIgniter does not support Prepared Statements. If you look at the sourcecode for CI's Database class, you will see that they resolve bindings simply by replacing the question marks with the data from the passed array:

• https://github.com/EllisLab/CodeIgniter/blob/develop/system/database/DB_driver.php#L874

They only support Query Binding with unnamed placeholders. See http://ellislab.com/codeigniter/user-guide/database/queries.html

Query Bindings

Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function.

and http://ellislab.com/forums/viewthread/105112/#528915

Even though CI doesn’t support prepared statements, it does support Query Bindings. With prepared statements you have to call some type of prepare() function and then some type of execute() function. With query bindings, you only have to call one function and it basically does the same thing. Because of this, I like query bindings better than prepared statements.

On a sidenote, changing ? to :foo is merely changing from unnamed to named bindings (which CI apparently does not support either). Just because you use either or doesn't mean you are preparing the statements.

Answer 2

I came across this question as I faced a similar issue. The answer is correct that CI doesn't support prepared statements. However it doesn't mean that you can't use prepared statements!

In the following example I am using PDO as my connection class but the following code will work:

$q = $this->db->conn_id->prepare('SELECT * FROM tbl_user WHERE uid=? and activation_key=?');
$q->execute(array($param1,$param2));
print_r($q->fetchAll());

Note the conn_id is the PDO object against which you can run your prepared statements.

What this won't allow however is for you to get the query string which the native CI functions allow. You will need something like Get Last Executed Query in PHP PDO for that.

Further more however this doesn't stop you using the Query Builder to build your statements which you can then use in the PDO prepare. For example -

$db->where('uid = ?',null,false);
$db->where('activation_key = ?',null,false);
$q = $this->db->conn_id->prepare($db->get_compiled_select('tbl_user'));

Would build the query and would allow you to see the basic query if you output $db->get_compiled_select('tbl_user');

As @site80443 points out, CI4 now supports prepared statements and this can be found here: https://codeigniter.com/user_guide/database/queries.html?highlight=prepared#prepared-queries