ZF2-265: Zend\Db\Sql\Insert::prepareStatement() produces incorrect database quoting

Description

Counters::insertRow($array_of_values) from the following code block produces this SQL statement: INSERT INTO "counters" ("id", "name", "type", "silhouette", "AF", "AAF", "RF", "FP", "T", "DF", "MF", "UMF", "plus", "misc") VALUES ('999', 'Test', 'BB', 'test.jpg', '99', '99', '99', '99', '99', '99', '99', '99', 'test', 'test')

This statement is subsequently kicked back by MySQL as it should have identifiers quoted using backticks (``).

Here is the code block: <?php /** * Works with 'counters' table to insert, update and render ship and air counters */

namespace Application\Model;

use Zend\Db\Adapter\Adapter as Adapter, Zend\Db\Sql as Sql;

class Counters {

protected static $_adapter;
protected static $_tableName = 'counters';
protected static $_dbName = 'slot';
protected static $_host = 'localhost';
protected static $_cols = 
    array(
        'id'            => 0,   // int(11) NOT NULL,
        'name'          => '',  // varchar(64) NOT NULL COMMENT 'actual name of the ship',
        'type'          => '',  // char(3) NOT NULL COMMENT 'BB, CV, CA etc. VF, VT => plane',
        'silhouette'    => '',  // varchar(64) NOT NULL COMMENT 'filename of unit silhouette',
        'AF'            => 0,   // int(2) unsigned NOT NULL DEFAULT '0' COMMENT 'attack factor',
        'AAF'           => 0,   // int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'anti-aircraft factor',
        'RF'            => 0,   // int(2) unsigned NOT NULL DEFAULT '0' COMMENT 'range factor',
        'FP'            => 0,   // int(2) unsigned NOT NULL DEFAULT '0' COMMENT 'float plane(s)',
        'T'             => 0,   // int(2) unsigned NOT NULL DEFAULT '0' COMMENT 'torpedos',
        'DF'            => 0,   // int(2) unsigned NOT NULL DEFAULT '1' COMMENT 'defense factor',
        'MF'            => 0,   // int(3) unsigned NOT NULL DEFAULT '11' COMMENT 'movement factor (surface)',
        'UMF'           => 0,   // int(2) unsigned DEFAULT '0' COMMENT 'undersea movement factor (subs only)',
        'plus'          => '',  // char(8) DEFAULT NULL COMMENT 'plus: M => mines, DC => depth charges, C => carrier based, L => land based',
        'misc'          => '',  // varchar(255) NOT NULL,
);

/**
 * Creates instance of database adapter
 */
public static function getAdapter()
{
    if (!self::$_adapter) {
        self::$_adapter = new Adapter(array(
                            'host'          => self::$_host,
                'driver'    => 'mysqli',
                'username'  => 'test',
                'password'  => 'password',
                'dbname'    => self::$_dbName
                ));
    }
    return self::$_adapter;
}

public static function getTableName()
{
    return self::$_tableName;
}

public static function getColumns()
{
    return self::$_cols;
}

public static function getColumnNames()
{
    return array_keys(self::$_cols);
}


/**
 * Adds a row
 * @param array $values
 */
public static function insertRow($values)
{
            // originally tried this using Zend\Db\TableGateway
            // but ran into even worse problems
            // ended up rewriting using the adapter and Zend\Db\Sql\Insert
    $adapter = self::getAdapter();
    $select = new Sql\Insert();
    $select->into(self::getTableName());
    $select->columns(self::getColumnNames());
    $select->values($values);
    $statement = $adapter->createStatement();
    $select->prepareStatement($adapter, $statement);
    echo $select->getSqlString(); exit;
    return $statement->execute();
}

}

Comments

NOTE: using $select->getSqlString(new Zend\Db\Adapter\Platform\Mysql) produced proper database quoting

In your comment is the correct usage, otherwise you'll get Sql92 standard quoting. Also might want to have a look at this setting in MySQL: http://dev.mysql.com/doc/refman/…

Going to close as not an issue.