2
  1. is there any alternatives in PDO as in mysql is mysql_real_escape_string?
  2. why we should set 'false' -> ATTR_EMULATE_PREPARES constant?
GAURAV MAHALE
  • 1,032
  • 10
  • 18
  • Learn about `PDO prepared statements` – Fabio Jun 12 '13 at 06:55
  • 3
    You *don't* use `mysql_real_escape_string` together with prepared statements. Read [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/), especially towards the end. – deceze Jun 12 '13 at 06:56
  • 1
    You can find examples of how to use parametrized queries in the [PHP documenation](http://www.php.net/manual/en/pdostatement.execute.php). With those prepared statements you don't have to escape anything yourself, PDO will take care of it. – martinstoeckli Jun 12 '13 at 07:23

2 Answers2

9

Using the mysql extension, the only way to dynamically embed values into queries is:

$query = "SELECT ... WHERE foo = '$bar'";

To avoid syntax errors which in the worst case can be exploited as SQL injections, you need to apply mysql_real_escape_string to $bar here to escape the value properly. Read The Great Escapism (Or: What You Need To Know To Work With Text Within Text) if you don't know what this means.

Prepared statements offer a completely different way to use dynamic values by entirely separating the query and the values:

$stmt = $db->prepare('SELECT ... WHERE foo = :bar');
$stmt->execute(array(':bar' => $bar));

This is actually sent to the database as two separate pieces; the syntax can never be messed up or exploited because the two pieces are never actually merged to begin with.

This feature is not supported by all databases though, some (older) databases still want the old style, non-prepared escaped queries. ATTR_EMULATE_PREPARES gives the option to, well, emulate prepared statements. You can still use the $db->prepare()->execute() API in your code, but behind the scenes PDO will escape the value and concatenate it into the query. To explicitly forbid PDO to do that and force it to use the native prepared statements API the database offers, set ATTR_EMULATE_PREPARES to false.

You should do this if your database natively supports prepared statements; all halfway recent databases do. If you let PDO emulate prepared statements, there's still a small chance of SQL injections under certain conditions (mostly under artificially constructed multi-byte connection encoding situations AFAIK).

Under no circumstances do you use a function from the mysql extension together with the PDO extension; it wouldn't work to begin with, since mysql_real_escape_string needs a separate database connection established through mysql_connect.

deceze
  • 510,633
  • 85
  • 743
  • 889
0
  1. you don't have to use mysql_real_escape_string in PDO in Prepared statement
  2. not necessarily. You can use either way

To answer edited question

NO, there are no alternatives. You should use prepared statements.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • What are the reasons, not to deactivate emulation (for newer database versions at least)? I never got to the bottom of this question and am still uncertain what to use. – martinstoeckli Jun 12 '13 at 07:42
  • 2
    No wonder as there is not a single answer one *should* use. Either is acceptable – Your Common Sense Jun 12 '13 at 07:45
  • 1
    So "shouldn't" should really read *"not necessarily"*? Personally I like to use the native functionality whenever possible, it just gives me warmer fuzzies than string concatenation. – deceze Jun 12 '13 at 07:51