Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-915

emulate named parameters for statement with the mysqli driver

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Drivers
    • Security Level: All
    • Labels:
      None

      Description

      Hi,

      Would it be reasonable to try to emulate named parameters in the mysqli driver?

      The goal is that we still could use named parameters and that the DBAL mysqli driver would automatically replace the named parameters by questions marks and pass the parameters in the right order according to those question marks ?

      The main problem I see is that we might replace stuff in the query that shouldn't be replaced. And in that case it might be good to have a way to disable that behavior (don't know if it's easy to do in the DBAL code base).
      On the other hand we could also ask the user to change it's parameter name even if it's not ideal it's also probably the fastest fix. The corner problem here is that I don't know the rules that are applied by pdo_mysql to replace the named parameters in a prepared statement, if there are any.

      Is it a good or bad idea and why ?
      Thanks

        Issue Links

          Activity

          Hide
          mikeSimonson added a comment -

          TLDR;
          It seems that executeQuery fails to treat the param as int when it's told too and that a named parameter is used.

          Closer look.

          I did change the prepare call into a call to executeQuery.
          It now looks like that:

          $stmt = $this->getEntityManager()
                      ->getConnection()
                      ->executeQuery("
                                SELECT ..... FROM ..... lots of join 
                                WHERE id = :id
                               ", array('id' => 10000107),
                         array(\PDO::PARAM_INT)
          );
          

          That query fails miserably (aka mysql use 100% of the processor for what seems like forever and I kill it).
          I realized that the query passed to phpmyadmin runs smootly if I write the were like this

                          WHERE id = 10000107
          

          but fails also if the query is passed with the id quoted

                          WHERE id = '10000107'
          
                          WHERE id = "10000107"
          

          I think that a part of the problem is that when I do executeQuery with a named parameter and a paramType as \PDO::PARAM_INT, the parameter is not passed as an int but as a string.
          The funny one is that you can use any quoting you want in your param if you don't use named parameters, and all those run smoothly :

          $stmt = $this->getEntityManager()
                      ->getConnection()
                      ->executeQuery("
                                SELECT ..... FROM ..... lots of join 
                                WHERE id = ?
                               ", array('1' => 10000107),
                         array(\PDO::PARAM_INT)
          );
          
          , array('1' => '10000107'),
                         array(\PDO::PARAM_INT)
          );
          
          , array('1' => "10000107"),
                         array(\PDO::PARAM_INT)
          );
          

          If anyone see any reason why that fails I am more than interested.
          Besides the fact that mysql probably shouldn't have any problem with the way the is passed ( as string or int), I also think that executeQuery fails to treat the param as int when it's told too and that a named parameter is used.

          What do you think ?

          Show
          mikeSimonson added a comment - TLDR; It seems that executeQuery fails to treat the param as int when it's told too and that a named parameter is used. Closer look. I did change the prepare call into a call to executeQuery. It now looks like that: $stmt = $ this ->getEntityManager() ->getConnection() ->executeQuery(" SELECT ..... FROM ..... lots of join WHERE id = :id ", array('id' => 10000107), array(\PDO::PARAM_INT) ); That query fails miserably (aka mysql use 100% of the processor for what seems like forever and I kill it). I realized that the query passed to phpmyadmin runs smootly if I write the were like this WHERE id = 10000107 but fails also if the query is passed with the id quoted WHERE id = '10000107' WHERE id = "10000107" I think that a part of the problem is that when I do executeQuery with a named parameter and a paramType as \PDO::PARAM_INT, the parameter is not passed as an int but as a string. The funny one is that you can use any quoting you want in your param if you don't use named parameters, and all those run smoothly : $stmt = $ this ->getEntityManager() ->getConnection() ->executeQuery(" SELECT ..... FROM ..... lots of join WHERE id = ? ", array('1' => 10000107), array(\PDO::PARAM_INT) ); , array('1' => '10000107'), array(\PDO::PARAM_INT) ); , array('1' => "10000107"), array(\PDO::PARAM_INT) ); If anyone see any reason why that fails I am more than interested. Besides the fact that mysql probably shouldn't have any problem with the way the is passed ( as string or int), I also think that executeQuery fails to treat the param as int when it's told too and that a named parameter is used. What do you think ?
          Hide
          Steve Müller added a comment - - edited

          Not sure if that fixes the issue but you have to pass a map of types as third argument like

          $query = 'SELECT foo FROM bar WHERE id = :id';
          $stmt = $this->getEntityManager()
              ->getConnection()
              ->executeQuery($query, array('id' => 10000107), array('id' => \PDO::PARAM_INT));
          

          Otherwise the parameters will be bound without a specific type, therefore seemingly mapping to string by default.
          See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connection.php#L1477-L1483

          Edit: Sorry fixed the example.

          Show
          Steve Müller added a comment - - edited Not sure if that fixes the issue but you have to pass a map of types as third argument like $query = 'SELECT foo FROM bar WHERE id = :id'; $stmt = $ this ->getEntityManager() ->getConnection() ->executeQuery($query, array('id' => 10000107), array('id' => \PDO::PARAM_INT)); Otherwise the parameters will be bound without a specific type, therefore seemingly mapping to string by default. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connection.php#L1477-L1483 Edit: Sorry fixed the example.
          Hide
          mikeSimonson added a comment -

          Aarg just saw your email.

          Thanks it works perfectly now.

          Show
          mikeSimonson added a comment - Aarg just saw your email. Thanks it works perfectly now.
          Hide
          mikeSimonson added a comment -

          Should I just add a new example in the documentation with a named parameter (bellow the one with a positional param) in http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#executequery ?

          Show
          mikeSimonson added a comment - Should I just add a new example in the documentation with a named parameter (bellow the one with a positional param) in http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#executequery ?
          Hide
          Steve Müller added a comment -

          Yeah might be a good idea to add the corresesponding examples with named parameters for executeQuery(), fetchAll(), fetchArray(), fetchColumn(), fetchAssoc(). Go ahead, open a PR and I'll merge then. Thanks.

          Show
          Steve Müller added a comment - Yeah might be a good idea to add the corresesponding examples with named parameters for executeQuery(), fetchAll(), fetchArray(), fetchColumn(), fetchAssoc(). Go ahead, open a PR and I'll merge then. Thanks.

            People

            • Assignee:
              Steve Müller
              Reporter:
              mikeSimonson
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: