DBAL-915: emulate named parameters for statement with the mysqli driver

[doctrinebot]

Jira issue originally created by user mikesimonson:

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

[doctrinebot]

• is referenced by DBAL-918: [GH-614] Correcting the doc because mysqli doesn't support named parameter natively

[doctrinebot]

Comment created by mikesimonson:

Btw I just realised that in the mysqli doctrine driver documentation it's indicated as supported. So maybe I should just add it.

[doctrinebot]

Comment created by @deeky666:

[~mikesimonson] I'm not quite sure what your issue is here as the DBAL Connection already converts named parameters into positional parameters under the hood. See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/SQLParserUtils.php Or am I getting you wrong here?

[doctrinebot]

Comment created by mikesimonson:

Is it possible that I did something the wrong way so that, that emulation is not used. My query is only a select with " WHERE id = :id ". That crash telling me that there is an error in my sql syntax and when I replace it with " WHERE id = ? " it works perfectly. That is with the mysqli driver on symfony2.4.

[doctrinebot]

Comment created by @deeky666:

Can you provide a code snippet of how you executed the query? Did you use the DBAL\Connection object? AFAIK you cannot use the mysqli driver connection directly because the parameter conversion from named to positional is done through DBAL\Connection

[doctrinebot]

Comment created by mikesimonson:

I am in a Repository (entity) and I am using

            ->getConnection()
            ->prepare("SELECT \* FROM person WHERE id =:id");

/ The query is trimmed for readability /

[doctrinebot]

Comment created by mikesimonson:

Just to make sure I tested with that exact same query.

If I use pdo_mysql the query runs fines and then I change the driver in the dbal config file to mysqli and it tell me that my sql is wrong. I change the sql to use question mark and it's fine again.

[doctrinebot]

Comment created by @deeky666:

DBAL\Connection::prepare() does not convert named into positional parameters. It works with pdo_mysql because pdo_mysql supports named parameters natively. You have to use one of the other (direct) query methods like DBAL\Connection::fetch*() or DBAL\Connection::executeQuery().

[doctrinebot]

Comment created by mikesimonson:

So, what you say is that it's not possible to have prepared statement with named parameter and mysqli. And I want to hook that sqlParserUtils method to be able to use the named parameters with mysqli statement too.

Does that make sense ?

[doctrinebot]

Comment created by @deeky666:

Sure you can have a prepared statement and named parameters with mysqli. It's just that DBAL\Connection::prepare() gives you a "raw" prepared statement, whereas executeQuery() gives you a prepared statement with a preprocessed SQL (named to positional conversion, array parameter expansion etc.). I think the fact that DBAL\Connection::prepare() does not convert the parameters for you automatically is that it does not take any parameters (as you have to bind them manually afterwards) which is necessary for the SQLParserUtils to rewrite the SQL appropriately. So either use one of the fetch*() methods with named parameters to retrieve a result directly or use exceuteQuery() to get a prepared statement (with converted named parameters). If you however really want to use prepare() (for whatever reason) then you will have to utilize the SQLParserUtils manually in order to get your named parameters converted into positionals before executing the query. Hope this helps.

[doctrinebot]

Comment created by mikesimonson:

Ok.

So it's just a misunderstanding of my part that to have a prepared statement you need to use the prepare method. In that case I will just use the executeQuery or query.

Thanks for you help.

What are the differences then between all those methods then. Also when I look in the documentation it quite unclear I think. If you go in the mysqli driver documentation it states that the driver support the prepared statement with a named parameter. At least it's that way that I understand it.

When I will have understand the difference between all those method I will try to explicit it in the documentation.

[doctrinebot]

Comment created by @deeky666:

Please have a look at the DBAL documentation to understand the differences between the available query methods in Doctrine\DBAL\Connection:

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#api

The reason why the mysqli driver API documentation states, it supports also named parameters is because the documentation is inherited from the Doctrine\DBAL\Driver\Statement interface which Doctrine\DBAL\Driver\Mysqli\MysqliStatement implements. Basically the interface is adopted from PHP's \PDOStatement and therefore a bit misleading here concerning named parameters, that's true. Sorry for the confusion.

[doctrinebot]

Comment created by mikesimonson:

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 ?

[doctrinebot]

Comment created by @deeky666:

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.

[doctrinebot]

Comment created by mikesimonson:

Aarg just saw your email.

Thanks it works perfectly now.

[doctrinebot]

Comment created by mikesimonson:

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 ?

[doctrinebot]

Comment created by @deeky666:

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.

[doctrinebot]

Issue was closed with resolution "Invalid"

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.