PDO support for multiple queries PDO MYSQL PDO MYSQLND

Question

I do know that PDO does not support multiple queries getting executed in one statement  I ve been Googleing and found few posts talking about PDO MYSQL and PDO MYSQLND      PDO MySQL is a more dangerous   application than any other traditional   MySQL applications  Traditional MySQL   allows only a single SQL query  In   PDO MySQL there is no such limitation    but you risk to be injected with   multiple queries    From  Protection against SQL Injection using PDO and Zend Framework  June 2010  by Julian   It seems like PDO MYSQL and PDO MYSQLND do provide support for multiple queries  but I am not able to find more information about them  Were these projects discontinued  Is there any way now to run multiple queries using PDO

User · Accepted Answer

As I know  PDO MYSQLND replaced PDO MYSQL in PHP 5 3  Confusing part is that name is still PDO MYSQL  So now ND is default driver for MySQL PDO  Overall  to execute multiple queries at once you need   PHP 5 3  mysqlnd Emulated prepared statements  Make sure PDO  ATTR EMULATE PREPARES is set to 1  default   Alternatively you can avoid using prepared statements and use  pdo- gt exec directly   Using exec  db   new PDO  quot mysql host localhost dbname test quot    root            works regardless of statements emulation  db- gt setAttribute PDO  ATTR EMULATE PREPARES  0     sql    quot  DELETE FROM car   INSERT INTO car name  type  VALUES   car1    coupe     INSERT INTO car name  type  VALUES   car2    coupe     quot     db- gt exec  sql    Using statements  db   new PDO  quot mysql host localhost dbname test quot    root            works not with the following set to 0  You can comment this line as 1 is default  db- gt setAttribute PDO  ATTR EMULATE PREPARES  1     sql    quot  DELETE FROM car   INSERT INTO car name  type  VALUES   car1    coupe     INSERT INTO car name  type  VALUES   car2    coupe     quot     stmt    db- gt prepare  sql    stmt- gt execute      A note  When using emulated prepared statements  make sure you have set proper encoding  that reflects actual data encoding  in DSN  available since 5 3 6   Otherwise there can be a slight possibility for SQL injection if some odd encoding is used

User · Answer

Like thousands of people  I m looking for this question  Can run multiple queries simultaneously  and if there was one error  none would run I went to this page everywhere But although the friends here gave good answers  these answers were not good for my problem So I wrote a function that works well and has almost no problem with sql Injection  It might be helpful for those who are looking for similar questions so I put them here to use  function arrayOfQuerys  arrayQuery         mx   true       conn- gt beginTransaction        try           foreach   arrayQuery AS  item                 stmt    conn- gt prepare  item  query                  stmt- gt execute  item  params                  result    stmt- gt rowCount                if  result    0                   mx   false                      if  mx    true                conn- gt commit             else               conn- gt rollBack          catch  Exception  e             conn- gt rollBack            echo  Failed       e- gt getMessage              return  mx      for use example       arrayQuery   Array      Array           query    gt   UPDATE test SET title     WHERE test id                params    gt  Array  aa1   1             Array           query    gt   UPDATE test SET title     WHERE test id                params    gt  Array  bb1   2           arrayOfQuerys  arrayQuery     and my connection       try            options   array                For updates where newvalue   oldvalue PDOStatement  rowCount     returns zero  You can use this              PDO  MYSQL ATTR FOUND ROWS   gt  true                     conn   new PDO  mysql host  servername dbname  database    username   password   options            conn- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION         catch  PDOException  e            echo  Error connecting to SQL Server       e- gt getMessage            Note  This solution helps you to run multiple  statement together  If an incorrect a statement occurs  it does not execute any other statement

User · Answer

After half a day of fiddling with this  found out that PDO had a bug where     --    This would run as expected   pdo- gt exec  valid-stmt1  valid-stmt2       --    This would error out  as expected   pdo- gt exec  non-sense  valid-stmt1       --    Here is the bug   pdo- gt exec  valid-stmt1  non-sense  valid-stmt3       It would execute the  valid-stmt1    stop on  non-sense   and never throw an error  Will not run the  valid-stmt3    return true and lie that everything ran good   I would expect it to error out on the  non-sense   but it doesn t   Here is where I found this info  Invalid PDO query does not return an error  Here is the bug  https   bugs php net bug php id 61613    So  I tried doing this with mysqli and haven t really found any solid answer on how it works so I thought I s just leave it here for those who want to use it    try         db connection      mysqli   new mysqli  host    user     password    database        if  mysqli- gt connect errno           throw new Exception  Connection Failed      mysqli- gt connect errno          mysqli- gt connect error            exit                  read file         This file has multiple sql statements       file sql   file get contents  filename sql         if  file sql     null     empty  file sql     strlen  file sql   lt   0           throw new Exception  File is empty  I wont run it                   run the sql file contents through the mysqli s multi query function         here is where it gets complicated           if the first query has errors  here is where you get it       sqlFileResult    mysqli- gt multi query  file sql          this returns false only if there are errros on first sql statement  it doesn t care about the rest of the sql statements        sqlCount   1      if   sqlFileResult    false            throw new Exception  File      fullpath      Query     sqlCount         mysqli- gt errno         mysqli- gt error                       so handle the errors on the subsequent statements like this         while I have more results  This will start from the second sql statement  The first statement errors are thrown above on the  mysqli- gt multi query  SQL    line     while  mysqli- gt more results              sqlCount               load the next result set into mysqli s active buffer  if this fails the  mysqli- gt error   mysqli- gt errno will have appropriate error info          if  mysqli- gt next result      false               throw new Exception  File      fullpath      Query     sqlCount     Error No      mysqli- gt errno         mysqli- gt error                           catch Exception  e       echo  e- gt getMessage       lt pre gt    e- gt getTraceAsString     lt  pre gt

User · Answer

Try this function   mltiple queries and multiple values insertion   function employmentStatus  Status     pdo   PDO2  getInstance      sql parts   array     for  i 0   i lt count  Status    i          sql parts        userID   val i        requete    pdo- gt dbh- gt prepare  DELETE FROM employment status WHERE userid    userID  INSERT INTO employment status  userid  status  VALUES   implode       sql parts     requete- gt bindParam   userID     SESSION  userID   PDO  PARAM INT   for  i 0   i lt count  Status    i          requete- gt bindParam   val i    Status  i  PDO  PARAM STR     if   requete- gt execute          return true    return  requete- gt errorInfo

User · Answer

Tried following code     db   new PDO  mysql host   dbhost  dbname   dbname  charset utf8    dbuser   dbpass  array PDO  ATTR ERRMODE   gt  PDO  ERRMODE EXCEPTION      Then   try     db- gt query  SET NAMES gbk      stmt    db- gt prepare  SELECT   FROM 2 1 paidused WHERE NumberRenamed     LIMIT 1      stmt- gt execute array   xbf x27 OR 1 1            catch  PDOException  e    echo  DataBase Errorz      e- gt getMessage      lt br gt        catch  Exception  e     echo  General Errorz     e- gt getMessage      lt br gt         And got  DataBase Errorz  SQLSTATE 42000   Syntax error or access violation  1064 You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near      LIMIT 1  at line 1   If added  db- gt setAttribute PDO  ATTR EMULATE PREPARES  false   after  db        Then got blank page  If instead SELECT tried DELETE  then in both cases got error like    DataBase Errorz  SQLSTATE 42000   Syntax error or access violation  1064 You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near    FROM 2 1 paidused WHERE NumberRenamed         OR 1 1     LIMIT 1  at line 1   So my conclusion that no injection possible

User · Answer

PDO does support this  as of 2020   Just do a query   call on a PDO object as usual  separating queries by   and then nextRowset   to step to the next SELECT result  if you have multiple  Resultsets will be in the same order as the queries  Obviously think about the security implications - so don t accept user supplied queries  use parameters  etc  I use it with queries generated by code for example   statement    connection- query  query   do      data      statement- fetchAll PDO  FETCH ASSOC     while   statement- nextRowset

User · Answer

A quick-and-dirty approach   function exec sql from file  path  PDO  pdo        if    preg match all                         s   file get contents  path    m           return       foreach   m 0  as  sql            if  strlen trim  sql                 pdo- gt exec  sql             Splits at reasonable SQL statement end points   There is no error checking  no injection protection   Understand your use before using it   Personally  I use it for seeding raw migration files for integration testing

[php] PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

A note:

Examples related to php

Examples related to mysql

Examples related to pdo