PDO s query vs execute

Question

Are they both do the same thing  only differently   Is there any difference besides using prepare between   sth    db- gt query  SELECT   FROM table     result    sth- gt fetchAll      and     sth    db- gt prepare  SELECT   FROM table     sth- gt execute     result    sth- gt fetchAll

User · Accepted Answer

query runs a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues   execute runs a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters  execute will also perform better if you are repeating a query multiple times  Example of prepared statements    sth    dbh- gt prepare  SELECT name  colour  calories FROM fruit     WHERE calories  lt   calories AND colour    colour     sth- gt bindParam   calories    calories    sth- gt bindParam   colour    colour    sth- gt execute        calories or  color do not need to be escaped or quoted since the       data is separated from the query   Best practice is to stick with prepared statements and execute for increased security   See also  Are PDO prepared statements sufficient to prevent SQL injection

User · Answer

Gilean s answer is great  but I just wanted to add that sometimes there are rare exceptions to best practices  and you might want to test your environment both ways to see what will work best   In one case  I found that query worked faster for my purposes because I was bulk transferring trusted data from an Ubuntu Linux box running PHP7 with the poorly supported Microsoft ODBC driver for MS SQL Server   I arrived at this question because I had a long running script for an ETL that I was trying to squeeze for speed  It seemed intuitive to me that query could be faster than prepare  amp  execute because it was calling only one function instead of two  The parameter binding operation provides excellent protection  but it might be expensive and possibly avoided if unnecessary   Given a couple rare conditions    If you can t reuse a prepared statement because it s not supported by the Microsoft ODBC driver  If you re not worried about sanitizing input and simple escaping is acceptable  This may be the case because binding certain datatypes isn t supported by the Microsoft ODBC driver  PDO  lastInsertId is not supported by the Microsoft ODBC driver    Here s a method I used to test my environment  and hopefully you can replicate it or something better in yours   To start  I ve created a basic table in Microsoft SQL Server  CREATE TABLE performancetest       sid INT IDENTITY PRIMARY KEY      id INT      val VARCHAR 100       And now a basic timed test for performance metrics    logs         test   function  String  type  Int  count   3000  use   pdo   amp  logs         start   microtime true        i   0      while   i  lt   count             sql    INSERT INTO performancetest  id  val  OUTPUT INSERTED sid VALUES   i  value  i             if   type      query                  smt    pdo- gt query  sql             else                smt    pdo- gt prepare  sql                smt - gt execute                       sid    smt- gt fetch PDO  FETCH ASSOC   sid             i               total    microtime true  -  start        logs  type       total      echo   total  type n        trials   15   i   0  while   i  lt   trials        if  random int 0 1      0             test  query          else            test  prepare               i       foreach   logs as  type   gt   log         total   0      foreach   log as  record             total     record             count   count  log       echo    count   type Average     total  count PHP EOL      I ve played with multiple different trial and counts in my specific environment  and consistently get between 20-30  faster results with query than prepare execute     5 8128969669342 prepare   5 8688418865204 prepare   4 2948560714722 query   4 9533629417419 query   5 9051351547241 prepare   4 332102060318 query   5 9672858715057 prepare   5 0667371749878 query   3 8260300159454 query   4 0791549682617 query   4 3775160312653 query   3 6910600662231 query   5 2708210945129 prepare   6 2671611309052 prepare   7 3791449069977 prepare    7  prepare Average  6 0673267160143    8  query Average  4 3276024162769     I m curious to see how this test compares in other environments  like MySQL

User · Answer

No  they re not the same  Aside from the escaping on the client-side that it provides  a prepared statement is compiled on the server-side once  and then can be passed different parameters at each execution  Which means you can do    sth    db- gt prepare  SELECT   FROM table WHERE foo         sth- gt execute array 1     results    sth- gt fetchAll PDO  FETCH ASSOC     sth- gt execute array 2     results    sth- gt fetchAll PDO  FETCH ASSOC     They generally will give you a performance improvement  although not noticeable on a small scale  Read more on prepared statements  MySQL version

[php] PDO's query vs execute

Examples related to php

Examples related to pdo