Should I really be using PDO and prepared statements?

Question

PDO and prepared statements are still kind of confusing to me, no matter how much I read about them so far. So I know they are more "secure" but is it really that important? I mean I can get the same end result using basic mysql with mysql_real_escape_string() and htmlspecialchars() right?

Answer 1

As long as you sanitize data appropriately for your queries then you don't have to use PDO/prepared statements. Though, I would personally recommend using PDO/prepared statements simply because they do both make development/debugging easier and prepared statements prevent incorrect data types from even getting in to a query.

If you want to learn more about how to create a simple prepared statement look in to the function sprintf. You simply replace any variable strings, integers, etc with a type specifier (in this case %s and %d respectively).

So for example in the following query, I know that id is going to be an integer (it will be numerical) and name will be a string (alphanumeric).

$username = 'Simon';
$id       = 3;
$query    = "SELECT FROM `users` WHERE `id` = {$id} AND `name` = '{$username}'";

If I'm getting either of this variables from an un-trusted source (such as a POST/GET) then I can make sure they are the correct data types by replacing the final line (the $query set) with a sprintf call like this:

$username = 'Simon';
$id       = 3;
$query    = sprintf( "SELECT FROM `users` WHERE `id` = %d AND `name` = '%s'", $id, $username );

sprintf will simply not let me use a string for the $id or an integer for the $name when it is called which ensures the correct types of data are given (this gives me that little extra bit of security). IF incorrect data types ARE given then I believe it'll cast the variables to the requested type.

To read more about sprintf visit here: http://php.net/sprintf

I hope this explains enough (it's my first answer) :).

Answer 2

You could, but PDO and prepared statements are the absolute safest. Could you do it by hand and use the mysql_real_escape_string() function? Sure. In fact, your output might look identical. But in the end, the code that PDO would require would be a hell of a lot shorter than the code if you had done it manually.

Also, if you aren't using prepared statements, you run the risk of human error: say you forget to escape a value or sanitize an input. Mixed in with all of your other code, the one line that isn't properly sanitizing could crop up to be a nightmare down the road.

Hope this helps!

Answer 3

you could try zend_db which uses pdo under the hood. (mdb2 is another option you can use.)

Answer 4

I really like the PDO interface. Once you get used to it, it's a lot cleaner than the mysql_* function style. It took me a while to figure it out, too, but it's worth it.

The part that I found confusing was remembering what methods belong to the PDO DB connection object itself, and which are part of the statement objects.

With certain actions, you'll get a performance benefit from repeating prepared statements, too. For instance, if you're doing a bunch of inserts in a loop, you can prepare the statement, and then bind new data in the loop each time before inserting.

The security is much better too, in that you are relying on a well tested library to escape your data on each insert. It's like cryptography - why do it yourself, when it's something this important? There's no reason to give yourself a chance to get it wrong (i.e., accidentally miss escaping something inserted into a query).

I recommend this guide to PDO, from the author who wrote this excellent giant book on Mysql.

I like to use the positional parameter style, and then you just make an array of the data and pass it in. My queries look like

$pdo_db=pdo_connect('cow_db');
$sql='select this,that,count(those) as snout from lovely_table where name=? and horses=?';
$data=array($username,$horse_count);

$query_stmt=$pdo_db->prepare($sql);
$result_handle=$query_stmt->execute($data);

//then I have a function to load data from the result handle
$info=load_array($result_handle);

You could make functions like this to work with the standard php mysql interface, but why not just use PDO?

Answer 5

I agree with others who say using prepared queries is generally better than using escaping functions. It's easier to use correctly, and there's no way that parameter values can introduce SQL injection problems, since the value are sent to the RDBMS server separately from the SQL query.

However, using parameters is useful only when the dynamic parts of your SQL query is a parameter in lieu of a literal value in an expression. You can't use a query parameter in place of a table name, column name, an SQL expression, or a list of values (e.g. arguments of an IN( ) predicate).

Prepared queries also have better performance than non-prepared queries (at least in MySQL). Most people say the opposite, but the well-respected mysqlperformanceblog.com did the testing:

http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

Answer 6

PHP lets you do anything you want, and it just so happens PDO does what "you want" with a lot less code. :)