Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

Question

I've been told that I'd be better using PDO for MySQL escaping, rather than mysql_real_escape_string.

Maybe I'm having a brain-dead day (or it may be the fact I'm by no stretch of the imagination a natural programmer, and I'm still very much at the newbie stage when it comes to PHP), but having checked out the PHP manual and read the entry on PDO, I'm still no clearer as to what PDO actually is and why it's better than using mysql_real_escape_string. This may be because I've not really got to grips with the complexities of OOP yet (I'm assuming it's something to do with OOP), but other than the fact that variables and array values seem to have a colon infront of them, I'm still not sure what it actually is and how you use it (and why it's better than mysql_real_escape_string. (It also may have something to do with the fact that I don't really have a clear understanding of what 'classes' are, so when I read "PDO class" I'm none the wiser really).

Having read an article or two on the 'Developer Zone' bit of the MySQL website, I'm still no clearer. As I can't even figure out what it is at the moment, I think probably using it is a bit beyond me right now, but I'm still interested in broadening my education and finding out how I could improve things.

Could anyone explain to me in 'plain English' what PDO is (or point me in the direction of something on the subject written in plain English), and how you'd go about using it?

Gulp

Prepared statements are heavier than most people think: joshduff.com/270/why-you-should-not-be-using-mysqli-prepare – Shows

Answer 1

As the current answers go into details while your question is more aimed at a general overview, I'll give it a try:

The PDO classes aim to encapsulate all the functionality needed to interact with a database. They do this by defining 'methods' (OO parlor for functions) and 'properties' (OO parlor for variables). You'd use them as a complete replacement for all the 'standard' functions you are using now for talking to a database.

So instead of calling a series of the 'mysql_doSomething()' functions, storing their results in your own variables, you would 'instantiate' an object from the PDO class ('class' = abstract definition, 'object' = concrete, usable instance of a class) and call methods on that object to do the same.

As an example, without PDO, you'd do something like this:

// Get a db connection
$connection = mysql_connect('someHost/someDB', 'userName', 'password');
// Prepare a query
$query = "SELECT * FROM someTable WHERE something = " . mysql_real_escape_string($comparison) . "'";
// Issue a query
$db_result = mysql_query($query);
// Fetch the results
$results = array();
while ($row = mysql_fetch_array($db_result)) {
  $results[] = $row;
}

while this would be the equivalent using PDO:

// Instantiate new PDO object (will create connection on the fly)
$db = new PDO('mysql:dbname=someDB;host=someHost');
// Prepare a query (will escape on the fly)
$statement = $db->prepare('SELECT * FROM someTable WHERE something = :comparison');
// $statement is now a PDOStatement object, with its own methods to use it, e.g.
// execute the query, passing in the parameters to replace
$statement->execute(array(':comparison' => $comparison));
// fetch results as array
$results = $statement->fetchAll();

So on first glance, there is not much difference, except in syntax. But the PDO version has some advantages, the biggest one being database independence:

If you need to talk to a PostgreSQL database instead, you'd only change mysql:to pgsql: in the instantiating call new PDO(). With the old method, you'd have to go through all your code, replacing all 'mysql_doSomething()' functions with their 'pg_doSomthing()' counterpart (always checking for potential differences in parameter handling). The same would be the case for many other supported database engines.

So to get back to your question, PDO basically just gives you a different way to achieve the same things, while offering some shortcuts/improvements/advantages. For example, escaping would happen automatically in the proper way needed for the database engine you are using. Also parameter substitution (prevents SQL Injections, not shown in example) is much easier, making it less error prone.

You should read up on some OOP basics to get an idea of other advantages.

Woo

Again, thanks, just the easy read version I need! I think the problem is I was taught the procedural method (I took a diploma course in web development a couple of years ago with a university that while very good is known for being a little behind the times when it comes to some things, and I haven't really used much of the programming side of things since until recently). Looks like I need to relearn a lot of what I know about PHP the OOP way. Useful link too (duly bookmarked) - thanks again everyone! – Gulp

This is missing the point that prepared statements with :varname type bind variables in the SQL make it impossible for an attacker to do SQL injection, and don't require error-prone escaping - see slide 16 of this good presentation on SQL injection: slideshare.net/kkotowicz/… – Behoof

@RichVel: This Answer was aimed at a general explanation in addition to the existing ones, hence the explicitly mentioned shortcut: "Also parameter substitution (prevents SQL Injections, not shown in example) is much easier, making it less error prone." – Woo

@YourCommonSense: OMG indeed - incredible how long one can overlook such bad examples in own answers :/ Changed to (still simplified) but hopefully more acceptable version. – Woo

Answer 2

I'm not super familiar with PDO, but there is a distinction between "prepared statements" and escaped strings. Escaping is about removing disallowed character strings from the query, but prepared statements are about telling the database what kind of query to expect.

A query has multiple parts

Think of it this way: when you give a query to the database, you're telling it several separate things. One thing might be, for example, "I want you to do a select." Another might be "limit it to rows WHERE the user name is the following value."

If you build up a query as a string and hand it to the database, it doesn't know about either part until it gets the completed string. You might do this:

'SELECT * FROM transactions WHERE username=$username'

When it gets that string, it has to parse it and decide "this is a SELECT with a WHERE".

Getting the parts mixed up

Suppose a malicious user inputs their user name as billysmith OR 1=1. If you're not careful, you might put that into your string, resulting in:

'SELECT * FROM transactions WHERE username=billysmith OR 1=1'

...which would return all the transactions for all users, because 1 always equals 1. Whoops, you've been hacked!

See what happened? The database didn't know what parts to expect in your query, so it just parsed the string. It wasn't surprised that the WHERE had an OR, with two conditions that could satisfy it.

Keeping the parts straight

If only it had known what to expect, namely, a SELECT whose WHERE had only one condition, the malicious user couldn't have tricked it.

With a prepared statement, you can give it that correct expectation. You you can tell the database "I'm about to send you a SELECT, and it's going to be limited to rows WHERE username = a string that I'm about to give you. That's all - there are no other parts to the query. Are you ready? OK, here comes the string to compare to the username."

With that expectation, the database wouldn't be fooled: it would only return rows where the username column contains the actual string 'billysmith OR 1=1.' If nobody has that user name, it would return nothing.

Other benefits of prepared statements

In addition to security benefits, prepared statements have a couple of speed benefits:

• They can be reused with different parameters, which should be faster than building a new query from scratch, because the database already knows basically what you're about to ask for. It has already built its "query plan".
• Some databases (Postgres is one, I think) will start making a query plan as soon as they get the prepared statement - before you've actually sent the parameters to use with it. So you may see a speedup even on the first query.

For another explanation, see Theo's answer here.

Yadirayaeger

Thanks! That's just what I need - the plain English version (i.e. the idiot's guide ;-)) – Gulp

While I was researching for reasons to use PDO over mysqli (while using prepared statements), this is a very good explanation of why prepared statements are better. It's not that you can't avoid issues by escaping strings (in theory), it's that this approach is both more reliable and quicker to implement (and possibly to run). – Censorious

I have about 20 tabs open trying to understand why prepared statements don't need escaping. This answer has explained it perfectly. Everything just clicked after reading. You should convert this answer into a blog post somewhere explaining prepared statements because not one I have come across has explained it as well. – Ustkamenogorsk

@Nathan Long, It's 2016 and your explanation of PDO telling the server what to expect and to only expect that is one of the only explanations that have made sense. Cheers! – Nervy

Answer 3

Unlike mysql_real_escape_string, PDO allows you to enforce a datatype.

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

Note that in the example above, the first parameter, calories, is required to be an integer (PDO::PARAM_INT).

Second, to me, PDO parameterized queries are easier to read. I'd rather read:

SELECT name FROM user WHERE id = ? AND admin = ? 

than

SELECT name FROM user WHERE id = mysql_real_escape_string($id) AND admin = mysql_real_escape_string($admin);

Third, you don't have to make sure you quote parameters properly. PDO takes care of that. For example, mysql_real_query_string:

SELECT * FROM user WHERE name = 'mysql_real_escape_string($name)' //note quotes around param

vs

SELECT * FROM user WHERE name = ?

Finally, PDO allows you to port your app to a different db without changing your PHP data calls.

Braynard

Answer 4

imagine you write something along the lines of:

$query = 'SELECT * FROM table WHERE id = ' . mysql_real_escape_string($id);

this will not save you from injections, because $id could be 1 OR 1=1 and you will get all the records from the table. you’d have to cast $id to the right datatype (int in that case)

pdo has another advantage, and that is the interchangability of database backends.

Hultgren

That's useful to know. As I said, I'm still really at the 'newbie' stage when it comes to PHP and a lot of the code snippets I've used as I've pieced together the app I've developed do have for example $id = (int) $_GET['id']; but to be honest, I'd no idea what the (int) actually meant. Would there be any advantage to casting all variables to their datatype, or is it just required in queries? – Gulp

this is generally considered good practice. always validate and sanitize all user input – Hultgren

Answer 5

In addition to preventing SQL injection, PDO allows you to prepare a query once and execute it multiple times. If your query is executed multiple times (within a loop, for instance), this method should be more efficient (I say "should be", because it looks like that is not always the case on older versions of MySQL). The prepare/bind method is also more in line with other languages I have worked with.

Lowpitched

That's the one thing I love about PDO, apart from the escape-things :) +1 – Metzler

Answer 6

Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

Simply because "escaping" alone makes no sense.
Moreover, it's different incomparable matters.

The only problem with escaping is that everyone takes it wrong, assuming it as some sort of "protection".
Everyone says "I escaped my variables" with the meaning "I protected my query".
While escaping alone has nothing to do with protection at all.

Protection can be roughly achieved in case of I escaped and quoted my data, but it is not applicable everywhere, for the identifiers for example (As well as PDO, by the way).

So, the answer is:

• PDO, when doing escaping for the binded values, applies not only escaping but also quoting - that's why it's better.
• "escaping" is not a synonym for the "protection". "escaping + quoting" roughly is.
• but for some query parts both methods inapplicable.

Silin