Why is PDO better to avoid MySQL queries and queries than mysql_real_escape_string?

Question

I was told that it is better to use PDO to speed up MySQL rather than mysql_real_escape_string .

Maybe I have a day of brain death (or it may be the fact that I'm not a natural programmer’s imagination, and I’m still very much at the beginner stage when it comes to PHP), but by checking the PHP manual and reading the entry in PDO , I still do not understand what PDO is and why it is better than using mysql_real_escape_string . Perhaps this is due to the fact that I still have not dealt with the complexities of OOP (I assume that this is something related to OOP), but apart from the fact that the variables and values ​​of the array seem to be, I'm still not sure what it is on in fact and how you use it (and why it is better than mysql_real_escape_string . (It may also be related to the fact that I don’t have a clear idea of ​​what the classes are, so when I read the PDO class I'm not really the wisest one).

Having read an article or two in a bit of the 'Developer Zone' on the MySQL website, I still don't get it. Since I can’t even understand what it is at the moment, I think that probably using it is a little outside of me now, but I'm still interested in expanding my education and figuring out how I can improve the situation.

Can someone explain to me in "plain English" what PDO is (or point me towards something on a subject written in plain English), and how are you going to use it?

Answer 1

As soon as the current answers delve into the details, and your question is more aimed at a general overview, I will try:

PDO classes are designed to encapsulate all the functions needed to interact with the database. They do this by defining “methods” (OO-salon for functions) and “properties” (OO-room for variables). You would use them as a complete replacement for all the standard functions that you use now to talk to the database.

Therefore, instead of calling a series of mysql_doSomething () functions, saving your results in your own variables, you will "instantiate" an object from the PDO class ("class" = abstract definition, "object" = concrete, usable instance of the class) and call methods for this object to do the same.

As an example, without PDO, you would 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; } 

whereas this will 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, eg // execute the query, passing in the parameters to replace $statement->execute(array(':comparison' => $comparison)); // fetch results as array $results = $statement->fetchAll(); 

So, at first glance there is not much difference, except for the syntax. But the PDO version has some advantages, the largest of which is database independence:

If you need to talk to the PostgreSQL database, you should only change mysql: to pgsql: in the instance of the new PDO() call. Using the old method, you have to go through all your code, replacing all the mysql_doSomething () functions with their "pg_doSomthing ()" (always checking for potential differences in processing parameters). The same would be true for many other supported database engines.

So, to get back to your question, PDO basically just gives you another way to achieve the same, by offering some shortcuts / improvements / benefits. For example, escaping will happen automatically in the proper way necessary for the database engine you are using. Also parameter substitution (prevents SQL Injections not shown in the example) is much simpler, which makes it less error prone.

You should read some of the basics of OOP to get an idea of ​​the other benefits.

Answer 2

I am not very familiar with PDO, but there is a difference between “prepared operations” and escaped strings. Escaping is the removal of forbidden character strings from a query, but prepared statements tell the database roughly what query to expect .

The request has several parts

Think of it this way: when you query a database, you tell it a few separate things. One may be, for example, "I want you to make a choice." Another could be "limit it to WHERE strings, username is next value".

If you create a query as a string and pass it to the database, it does not know about any part until it receives the filled string. You can do it:

 'SELECT * FROM transactions WHERE username=$username' 

When he receives this string, he must parse it and decide that "this is SELECT with WHERE ".

Confusing details

Suppose a malicious user enters their username as billysmith OR 1=1 . If you are not careful, you can put this on your line, resulting in:

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

... which will return all transactions for all users , because 1 is always equal to 1. Oops, you were hacked!

See what happened? The database did not know what parts to expect in your query , so it just parses the string. Not surprisingly, WHERE had an OR , with two conditions that could satisfy it.

Keeping parts straight

If he knew what to expect , namely, SELECT , which WHERE had only one condition, the attacker could not deceive him.

With a prepared expression, you can give it the correct expectation. You can tell the database "I am going to send you a SELECT , and it will be limited to the lines WHERE username = line I am going to give you. All of this - there are no other parts for the query. Are you ready? OK, here comes the line for comparison with the name user. "

With this expectation, the database will not be fooled: it will return rows where the username column contains the actual row "billysmith OR 1 = 1". If no one has this username, he will not return anything.

Other benefits of trained operators

In addition to security benefits, prepared statements have several speed advantages:

• They can be reused with different parameters, which should be faster than creating a new query from scratch, because the database already knows what you want to ask. He has already built a "request plan."
• Some databases (Postgres is one, I think) will begin to plan the query as soon as they receive the prepared statement - before you actually send the parameters for use with it. Thus, you can see acceleration even at the first request.

For another explanation, see Theo's answer here .

Answer 3

Unlike mysql_real_escape_string, PDO allows you to apply a data type.

 <?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, must be an integer (PDO :: PARAM_INT).

Secondly, to me, PDO parameterized queries are easier to read. I would 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); 

Thirdly, you do not have to specify parameters. PDO takes care of this. For example, mysql_real_query_string:

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

against

 SELECT * FROM user WHERE name = ? 

Finally, PDO allows you to port your application to another db without changing your PHP data calls.

Answer 4

Imagine writing something line by line:

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

this will not save you from injections, because $ id can be 1 OR 1=1 , and you will get all the records from the table. youd should give $ id the correct data type (int in this case)

pdo has another advantage, and that is database interchangeability.

Answer 5

In addition to preventing SQL injection, PDO allows you to prepare a query once and execute it several times. If your query is executed several times (for example, in a loop), this method should be more efficient (I say "should be" because it does not look like this always applies to older versions of MySQL). The prepare / bind method is also more consistent with other languages ​​I worked with.

Answer 6

Why is PDO better for speeding MySQL queries / queries than mysql_real_escape_string?

Just because "escaping" alone does not make sense.
In addition, incomparable matters.

The only problem with shielding is that everyone is mistaken , considering this a kind of "protection."
Everyone says: "I escaped my variables" with the value "I protected my request."
Although shielding has nothing to do with protection.

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

So the answer is:

• PDO when escaping bound values ​​applies not only to escaping, but also to citation - why is it better.
• "escaping" is not synonymous with "protection." "slipping + citation" is approximately there.
• but for some parts of the request, both methods are not applicable.