Pdo Named Parameters and Redundant position/data (MySql) #5116

Closed
fyrye opened this Issue Sep 15, 2013 · 6 comments

Projects

None yet

4 participants

@fyrye
fyrye commented Sep 15, 2013

Error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens .../Zend/Db/Adapter/Driver/Pdo/Statement.php 240

To describe the issue, with named parameters in Pdo_Mysql driver. Parameters are not being bound correctly to the Statement::$parameterContainer and their pseudo aliased parameters from the Sql object parameters.

The same issue can be found in Update, Delete, and Insert.
Query:

    $sql = new Zend\Db\Sql\Sql( $adapter );
    $select = $sql->select()
            ->from( array( 't1' => 'company' ) )
            ->where( array( 'companyName' => ':companyName' ) );
    $sth = $sql->prepareStatementForSqlObject( $select );
    $value1 = 'test';
    $result = $sth->execute( array( ':companyName' => $value1 ) );

Resulting Query:

SELECT `t1`.* FROM `company` AS `t1` WHERE `companyName` = :where1

Resulting Parameters:

[parameterContainer:protected] => Zend\Db\Adapter\ParameterContainer Object
        (
            [data:protected] => Array
                (
                    [where1] => :companyName
                    [:companyName] => test
                )

            [positions:protected] => Array
                (
                    [0] => where1
                    [1] => :companyName
                )

            [errata:protected] => Array
                (
                )

        )

PdoStatement->BindValues
(retrieved from Zend/Db/Adapter/Driver/Pdo/Statement::bindParametersFromContainer())

 $parameter = is_int($name) ? ($name + 1) : $name;
 echo "$parameter = $value<br/>"; 
 $this->resource->bindParam($parameter, $value, $type);
where1 = :companyName
:companyName = test

changing the named parameter to the pseudo created name allows the query to execute properly, but this is not intended behavior.

$sth->execute( array( ':where1' => $value1 ) );

Resulting edited parameters

[data:protected] => Array
                (
                    [where1] => :companyName
                    [:where1] => test
                )

            [positions:protected] => Array
                (
                    [0] => where1
                    [1] => :where1
                )

PdoStatement->BindValues

where1 = :companyName
:where1 = test

The issue originates from Zend/Db/Adapter/Driver/Pdo/Statement::execute()

        /** START Standard ParameterContainer Merging Block */
        if (!$this->parameterContainer instanceof ParameterContainer) {
            if ($parameters instanceof ParameterContainer) {
                $this->parameterContainer = $parameters;
                $parameters = null;
            } else {
                $this->parameterContainer = new ParameterContainer();
            }
        }

        if (is_array($parameters)) {
                /*
                * Creates the unaliased position => value pair
                * No matching is done to the aliased position from Where/Join/SubSelectWhere
                */
            $this->parameterContainer->setFromArray($parameters);
        }
@coud
coud commented Sep 16, 2013

I have the same issue but this one is for mysqli.
#4984

@fyrye
fyrye commented Jan 25, 2014

I ended up writing a hack to process the parameters offsets with their positional data to match everything since it was causing failed updates and a very nasty delete where an entire table's data was lost.

I believe the issue is with the parameter container but instead of looking more into it decided to fix my immediate issues. However the issue still exists with the origin/master so will leave the ticket open.

Hope this helps you if you needed it @coud.

_Zend\Db\Adapter\Driver\Pdo\Statement::bindParametersFromContainer()_

if ($this->parametersBound) {
    return;
}

$positions = $this->parameterContainer->getPositionalArray();
$parameters = $this->parameterContainer->getNamedArray();
foreach ( $positions as $k => $v ) {
    if ( ( true === in_array( $v, $parameters ) ) ) {
        $name = null;
        foreach ( $parameters as $index => $value ) {
            if ( $v === $value ) {
                $name = $index;
                break;
            }
        }
        if ( null === $name ) {
            throw new \Exception( 'Unable to find the parameter name and value matches' );
        }
        if ( true === is_string( $v ) && substr( $v, 0, 1 ) === ':' && true === array_key_exists( $v, $parameters ) ) {
            $parameters[$name] = $parameters[$v];
            unset( $parameters[$v] );
        }
        if ( true === is_string( $v ) && null === $parameters[$name] && substr( $v, 0, 1 ) === ':' ) {
            $parameters[$name] = null;
            unset( $parameters[$v] );
        }
    }
    if ( $v === '?' ) {
        unset( $parameters[$name] );
    }
}
foreach ($parameters as $name => &$value) {
    if ( substr( $value, 0, 1 ) === ':' && substr( $value, 1 ) === $name ) {
        $value = null;
    }
    if ( is_bool($value) ) {
        $type = \PDO::PARAM_BOOL;
    } elseif ( is_null( $value ) ) {
        $type = \PDO::PARAM_NULL;
    } else {
        $type = \PDO::PARAM_STR;
    }
    if ($this->parameterContainer->offsetHasErrata($name)) {
        switch ($this->parameterContainer->offsetGetErrata($name)) {
            case ParameterContainer::TYPE_INTEGER:
                $type = \PDO::PARAM_INT;
                break;
            case ParameterContainer::TYPE_NULL:
                $type = \PDO::PARAM_NULL;
                break;
            case ParameterContainer::TYPE_LOB:
                $type = \PDO::PARAM_LOB;
                break;
        }
    }

    // parameter is named or positional, value is reference
    $parameter = is_int($name) ? ($name + 1) : $name;
    $this->resource->bindParam($parameter, $value, $type);
}

//this was never set
$this->parametersBound = true;
@ralphschindler
Member

Don't use the : to prefix your names, those are internally used by pdo in their parameter naming scheme.
Edit: in your original code snippet, you should be doing this:

$result = $sth->execute(array('companyName' => $value1));

as the parameter name you used previously is 'companyName' and the original value was ':companyName'

@ralphschindler
Member

We can reopen if this proves to be an issue in the framework.

@fyrye
fyrye commented Mar 12, 2014

Same issue with fresh installation, works fine with indexed parameter, not named parameters as stated in original post.

$sql = new Zend\Db\Sql\Sql( $adapter );
$select = $sql->select()
        ->from( array( 't1' => 'company' ) )
        ->where( array( 'companyName' => ':companyName' ) );
$sth = $sql->prepareStatementForSqlObject( $select );
printf( '<pre>%1$s</pre>', print_r( $sth, 1 ) );
$value1 = 'test';
$result = $sth->execute( array( 'companyName' => 'test' ) );
printf( '<pre>%1$s</pre>', print_r( $result, 1 ) );

Same as with

$result = $sth->execute( array( ':companyName' => 'test' ) );
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /html/projects/zf2/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:240 Stack trace: #0 /html/projects/zf2/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php(240): PDOStatement->execute() #1 /html/projects/zf2/public/index.php(37): Zend\Db\Adapter\Driver\Pdo\Statement->execute(Array) #2 {main} Next exception 'Zend\Db\Adapter\Exception\InvalidQueryException' with message 'Statement could not be executed (HY093 - - )' in /html/projects/zf2/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:245 Stack trace: #0 /html/projects/zf2/public/index.php(37): Zend\Db\Adapter\Driver\Pdo\Statement->execute(Array) #1 {main} thrown in /html/projects/zf2/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php on line 245

Thank you for taking the time to thoroughly investigate the issue and properly test the workaround. Since we are not able to reproduce your results we have decided to drop all Zend based product support from our projects.

@ralphschindler
Member

I was actually focusing on the wrong part of the problem. You're right, an issue exists here. I will find a better way to describe it, and try and find a solution. This actually borders on a feature request as I don't think we fully support named params from our outside API.

The idea originally was that you'd be using the names assigned (where1, subselect1where2, for example) so that there is no collision of names if you were to provide them, and that the backend was able to create names when only values are passed in (which is the standard use case).

@GeeH GeeH added the To Be Closed label Mar 5, 2016
@fyrye fyrye closed this May 12, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment