Why Is Pdo Better For Escaping MySQL Queries/Querystrings Than MySQL_Real_Escape_String

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

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.

mysql_real_escape_string with PDO PHP

Yes, PDO automatically escapes your data, so you don't need to use mysql_real_escape_string. See here, for example.

INSERT INTO: mysql_real_escape_string alternative

PDO::quote is the equivalent of mysql_real_escape_string. If there's some reason you can't use a prepared statement, you can use it like this:

$q = "INSERT INTO articles VALUES (" . $idc->quote($_GET["article"]) . ")";

A significant difference is that it includes the surrounding quotes around the string, while mysql_real_escape_string doesn't (so you would have to put quotes in your INSERT string).

Does mysql_real_escape_string() FULLY protect against SQL injection?

According to Stefan Esser, "mysql_real_escape_string() [is] not safe when SET NAMES is used."

His explanation, from his blog:

SET NAMES is usually used to switch the encoding from what is default to what the application needs.
This is done in a way that mysql_real_escape_string doesn’t know about this. This means if you switch to some multi byte encoding that allows backslash as 2nd 3rd 4th… byte you run into trouble, because mysql_real_escape_string doesn’t escape correctly. UTF-8 is safe…

Safe way to change encoding is mysql_set_charset, but that is only available in new PHP versions

He does mention that UTF-8 is safe, though.

Do I need to escape my variables if I use MySQLi prepared statements?

No, if you use prepared statements everywhere in your application you are safe from SQL injection. However, an important "gotcha" is 2nd order injection attacks which happen when some queries use prepared statements and others don't.

According to this answer of a similar question on SO:

prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

In summary, prepared statements create a separation between the data being sent and the SQL query itself, ensuring that the data can not be misinterpreted as the SQL query. However, an attacker can still enter SQL as data, and although it will not be executed when it is first stored if you are using prepared statements, you must still use caution when retrieving said results. Prepared statements protect your application in that particular place, but because SQL is still allowed to be stored in the database, your application is unsafe if you're later using that data without parameterization.

Escaping MySQL Query issue

I really don't see any sanitizing of your $_POST data, and there is really no need to run htmlentities before you insert into the database, that should be done when you take that data and display it on the page. Make sure to sanitize your posts!! Using mysql_real_escape_string() or preferably PDO with prepared statements.

If you are running mysql_real_escape_string() on this whole query, after you build it, than that is what is breaking it.

Use it on the individual posts, and / or cast variables that should only ever be numbers to integers.

Heres what I would change it to in your case:

$posted = $_POST;

foreach($posted as &$value)
    $value = mysql_real_escape_string($value);

$date = strtotime($posted['date']);


$q = "INSERT INTO bio_manager_pubs(userid,category,citation,date,link,requests) VALUES
(
'{$userid}',
'{$posted['category']}',
'{$posted['pub'])}', 
FROM_UNIXTIME({$posted['date']}),
'{$posted['link']}',
'0'
)";

---

Related Topics