CodeIgniter PDO driver uses query instead of prepare? Isn't this less secure?

2
votes

I am new to the CodeIgniter framework for php and was looking at the PDO database driver with Version 2.1.0. I noticed it uses the PDO 'query' function and not 'prepare' and 'bindParam'/'bindValue'.

Doesn't this completely miss the point of using PDO in the first place and in fact make it less protected from sql injection than using the normal mysql driver they provide. It doesn't seem to be escaping queries like it does with the other provided drivers? Or am I completely misinterpreting something?

EDIT: It looks as if CodeIgniter may in fact be using PDO::quote to sanitize. But even the php documentation says this is not recommended as it is less secure and seemed to miss the point of PDO in the first place

5
phpcodeigniterpdocodeigniter-2
Codeigniter is not a database. It should be able to use ANY PDO supported database driver. So, maybe you mean a specific database doesn't support prepared statements? - Jeremy Harris
I believe the PDO driver does actually escape queries. open system/database/driver/pdo and have a read through the scripts. - Philip
@cillosis I am aware, I was talking about the PDO wrapper class the provided. Maybe I should have been more specific - user1146939
@Philip what's good in escaping queries? - Your Common Sense

5 Answers

1
votes

I've not used CodeIgniter so I actually had to do a little research on this. As it turns out, CodeIgniter does offer a semblance of parameterized queries called Query Bindings.

It works like this:

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

According to the documentation:

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.

It is odd to me though that it abstracts the prepared statements functionality. I figured that was pretty much cut and dry. Heh :/

1
votes

I'm looking at the source code, and I see escape_str calls quote, and DB_driver->escape calls escape_str. I haven't quite tracked down the overall structure. So I don't know for sure if escape itself is called where it should be.

However, PDO::quote is safe if used correctly. Prepared statements are easier for application programming, but PDO::quote may be a better choice for libraries that provide their own abstraction.

1
votes

I dunno CI but there is a simple rule to remember:

Despite of the common [weird] belief,

Escaping alone does nothing good.

As a matter of fact, it should be always escaping+quoting.
If we don't quote escaped data, we get no good from escaping.

So, I suppose that CI does both. If so - it should be safe.

The only consequence I can think of is LIMIT parameters. If you pass them as variables of string type, CI query might throw an error, like PDO in compatibility mode does. I'd be grateful if you test this behavior and post the result.

0
votes

That actually sounds right to me. The docs for pdo::query() say "data inside the query should be properly escaped." If you follow the link to pdo::quote(), there's a prominent warning:

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

I can't offer any insight into why codeigniter is using query() instead of prepare(), though.

0
votes

To use pdo with prepare statement, you need to do a little modification. 

http://christopherickes.com/web-app-development/using-pdo-in-codeigniter/

then, you can use prepare statement.