What is the PDO equivalent of function mysql_real_escape_string?

Question 1

I am modifying my code from using mysql_* to PDO. In my code I had mysql_real_escape_string(). What is the equivalent of this in PDO?

Question 2

Well No, there is none!

Technically there is PDO::quote() but it is rarely ever used and is not the equivalent of mysql_real_escape_string()

That's right! If you are already using PDO the proper way as documented using prepared statements, then it will protect you from MySQL injection.

# `Example:`

Below is an example of a safe database query using prepared statements (pdo)

  try {
     // first connect to database with the PDO object. 
     $db = new PDO("mysql:host=localhost;dbname=xxx;charset=utf8", "xxx", "xxx", [
       PDO::ATTR_EMULATE_PREPARES => false, 
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
     ]); 
 } catch(PDOException $e){
     // if connection fails, show PDO error. 
   echo "Error connecting to mysql: " . $e->getMessage();
 }

And, now assuming the connection is established, you can execute your query like this.

if($_POST && isset($_POST['color'])){ 

    // preparing a statement
    $stmt = $db->prepare("SELECT id, name, color FROM Cars WHERE color = ?");

    // execute/run the statement. 
    $stmt->execute(array($_POST['color']));

    // fetch the result. 
    $cars = $stmt->fetchAll(PDO::FETCH_ASSOC); 
    var_dump($cars); 
 }

Now, as you can probably tell, I haven't used anything to escape/sanitize the value of $_POST["color"]. And this code is secure from myql-injection thanks to PDO and the power of prepared statements.

It is worth noting that you should pass a charset=utf8 as attribute, in your DSN as seen above, for security reasons, and always enable PDO to show errors in the form of exceptions.

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

so errors from you database queries won't reveal sensitive data like your directory structure, database username etc.

Last but not least, there are moments when you should not trust PDO 100%, and will be bound to take some extra measures to prevent sql injection, one of those cases is, if you are using an outdated versions of mysql [ mysql =< 5.3.6 ] as described in this answer

But, using prepared statements as shown above will always be safer, than using any of the functions that start with mysql_

Good reads

PDO Tutorial for MySQL Developers

Question 3

This is a more up-to-date answer to this question.

The old way of preventing multi query execution was to disable emulated prepares, however this was only applicable to the PDO::prepare() method. In newer versions of PHP (>= 5.5.21 and >= 5.6.5), a new constant has been introduced to disable this multi query execution in both PDO::prepare() and PDO::query(). (Constants aren't usually added in patch versions, but this was done due to the severity of a Drupal SQL injection attack brought about by this capability).

The new constant is PDO::MYSQL_ATTR_MULTI_STATEMENTS and must be set on object creation (as the fourth argument to the PDO constructor) - setting it on a pre-existing object with PDO::setAttribute() will not work.

$pdo = new PDO('mysql:host=_;dbname=_', '', '', [PDO::MYSQL_ATTR_MULTI_STATEMENTS => false]);

Question 4

Perhaps something like this. (untested)

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";
$args=array();

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = :val1"
    $args[':val1']=$val1;
} else {
    $TempSQL = $TempSQL . " field2 = :val2 and field3 = :val3";
    $args[':val2']=$val2;
    $args[':val3']=$val3;
}

$stmt=$db->prepare($TempSQL);
$stmt->execute($args);

Question 5

It looks like your reduce results are being re-reduced. That is, reduce is called more than once for each key and then called again with those results. You can handle that with a reduce function like this:

function(keys, values, rereduce) {
  if (rereduce) {
    return sum(values);
  } else {
    return values.length;
  }
}

Alternatively, you can change the map function so that the values are always a count of documents:

// map
function(doc) {
  emit(doc.name, 1);
}

// reduce
function(keys, values, rereduce) {
  return sum(values);
}

Question 6

In C#, you can specify a cast by putting the type you want to cast to in parenthesis in front of the reference variable that you want to cast ((type)instance).

So, to cast the object (dq) to the type IUIBuildingBlock, you could use the following code:

((IUIBuildingBlock)dq).QuestionText = reader("QuestionText");

(Note that this will throw an exception if the cast is done on an object that doesn't implement IUIBuildingBlock, but so will CType, so I assume that is not a problem.)

What is the PDO equivalent of function mysql_real_escape_string?

Answers

# `Example:`

PDO Tutorial for MySQL Developers

php,mysql,pdo,sql-injection

What is the PDO equivalent of function mysql_real_escape_string?

Answers

# Example:

PDO Tutorial for MySQL Developers

php,mysql,pdo,sql-injection

# `Example:`