1

I am using PDO statement as below

select * from `admine_user` where `user_id` = ? and passw = ?
$resultfm1 = DB::instance()->prepare($query)->execute
(array($escapedid,$hashedpass))->fetchAll();

I am thinking to use

select * from `admine_user` where `user_id` = :user and passw = :pwd
$resultfm1 = DB::instance()->prepare($query)->execute
(array(":user"=>$escapedid,":pwd"=>$hashedpass))->fetchAll();

Out of above statements which is better to use which can prevent SQL injection effectively as now i can not use mysql_real_escape_string

Gags
  • 3,759
  • 8
  • 49
  • 96
  • Thanks @sectus.. and someone downvoted, can i know reason and thanks for person who upvoted.. I think we are here to learn – Gags Jun 30 '14 at 06:18
  • 2
    You should make sure your prepares aren't emulated (`$con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);`) - [Read More.](http://stackoverflow.com/questions/8776344/how-to-view-query-error-in-pdo-php/8776392#8776392). But yes both queries do the same thing. – Darren Jun 30 '14 at 06:18
  • Yes they both prepare if your db instanve is pdo. They are actualle the same query. One just uses nameholders. – Michal Jun 30 '14 at 06:18
  • Neither is better than the other, and they will both effectively prevent SQL injection. Use the one you like better. – Mark Miller Jun 30 '14 at 06:19
  • It is set to `TRUE` .. if it is TRUE then is SQL injection Possible? – Gags Jun 30 '14 at 06:21
  • Where did these doubts come from? – sectus Jun 30 '14 at 06:23
  • @CodeFreak There is a still a possibility -> [Read More](http://stackoverflow.com/a/134138/2518525) Related to my comment before. – Darren Jun 30 '14 at 06:25
  • @sectus .. i was studying it somewhere but was not satisfied.. so thought to ask experts here :) – Gags Jun 30 '14 at 06:37

1 Answers1

5

MySQL only supports positional parameters (the ? placeholders), so PDO internally converts named parameters into positional parameters during the prepare step.

So in fact both styles ultimately do the same thing with respect to MySQL.

Also, "emulated prepares" doesn't actually do anything in the prepare step, it just saves the query string. When you execute, you supply values and they are interpolated into the query and then submitted to MySQL. If you don't trust this process, then disable emulated prepares.

This is not to say that PDO does anything unsafe, although early versions of PDO had some bugs.

Just make sure you're using a current version of PDO (basically anything in PHP 5.3 or newer), and then both styles are as safe as one can be.

Notwithstanding any regression bugs that may occur after I write this...

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • +1 I didn't know that PDO converts named parameters to positional. Does this mean that there is a performance benefit to using positional parameters over named? – Mark Miller Jun 30 '14 at 06:27
  • yeah.. @bill karwin .. i am using PHP 5.3 only – Gags Jun 30 '14 at 06:28
  • @MarkM, don't worry about any performance difference between positional and named parameters. It's just a few lines of C code, so the difference will barely be measurable. You can benchmark it if you want, but I predict your time will be better spent optimizing your PHP code instead, or analyzing SQL queries to create the right indexes. – Bill Karwin Jun 30 '14 at 14:01