Error with escaping "rows" and MariaDB #40

Paddy0174 · 2018-11-12T11:50:36Z

I have an error with the correct escaping for the reserved word "rows" in tl_layout. This issue was discovered in contao-core before (see #153 ).

Unfortunately I have now the same issue with meta-palettes. See this thread in the board:
click here

@fritzmg found the error in SubSelectPalettesListener.php in line 304

Now all the data you might need, it's an uberspace7 server:

CentOS 7
PHP 7.1.23 (same version in the CM and CLI)
MariaDB 10.3
Contao 4.6.6 (downgraded from 4.6.7), new install with db backup from the old 4.4.x version and upgrade)

My composer.json

    "name": "contao/managed-edition",
    "type": "project",
    "description": "Contao Open Source CMS",
    "license": "LGPL-3.0-or-later",
    "authors": [
        {
            "name": "Leo Feyer",
            "homepage": "https://github.com/leofeyer"
        }
    ],
    "require": {
        "php": "^7.1",
        "cliffparnitzky/tiny-mce-font-awesome": "^2.1",
        "contao-bootstrap/bundle": "^2.1",
        "contao/calendar-bundle": "^4.6",
        "contao/comments-bundle": "^4.6",
        "contao/faq-bundle": "^4.6",
        "contao/listing-bundle": "^4.6",
        "contao/manager-bundle": "4.6.*",
        "contao/news-bundle": "^4.6",
        "contao/newsletter-bundle": "^4.6",
        "delahaye/dlh_googlemaps": "^2.4",
        "do-while/contao-backupdb-bundle": "^1.3",
        "heimrichhannot/contao-fieldpalette-bundle": "^0.4.4",
        "hofff/contao-shariff": "^3.0",
        "madeyourday/contao-rocksolid-antispam": "^2.0",
        "menatwork/contao-multicolumnwizard": "^3.3",
        "smithdata/contao-seminar-management-bundle": "1.0.*",
        "terminal42/notification_center": "^1.5",
        "trilobit-gmbh/contao-cookiebar-bundle": "^1.0"
    },
    "conflict": {
        "contao-components/installer": "<1.3",
        "contao/core-bundle": "4.6.7"
    },
    "require-dev": {
        "sensiolabs/security-checker": "^4.1"
    },
    "extra": {
        "branch-alias": {
            "dev-4.6": "4.6.x-dev"
        },
        "contao-component-dir": "assets"
    },
    "repositories": [
        {
            "type": "path",
            "url": "_external/smithdata"
        }
    ],
    "autoload": {
        "psr-4": {
            "App\\": "src/"
        }
    },
    "scripts": {
        "post-install-cmd": [
            "Contao\\ManagerBundle\\Composer\\ScriptHandler::initializeApplication"
        ],
        "post-update-cmd": [
            "Contao\\ManagerBundle\\Composer\\ScriptHandler::initializeApplication"
        ]
    }
}

And the stack trace from app_dev:

An exception occurred while executing 'SELECT rows FROM tl_layout WHERE id='1' LIMIT 0,1':
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows FROM tl_layout WHERE id='1' LIMIT 0,1' at line 1
  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:99
  at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing \'SELECT rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1\':SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1\' at line 1', object(PDOException))
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:184)
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing \'SELECT rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1\':SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1\' at line 1')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:158)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1', array())
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:943)
  at Doctrine\DBAL\Connection->executeQuery('SELECT rows FROM tl_layout WHERE id=\'1\' LIMIT 0,1')
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:276)
  at Contao\Database\Statement->query()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:250)
  at Contao\Database\Statement->execute('1')
     (vendor/contao-community-alliance/meta-palettes/src/Listener/SubSelectPalettesListener.php:307)
  at ContaoCommunityAlliance\MetaPalettes\Listener\SubSelectPalettesListener->fetchValueFromDatabase(object(DC_Table), 'rows')
     (vendor/contao-community-alliance/meta-palettes/src/Listener/SubSelectPalettesListener.php:171)
  at ContaoCommunityAlliance\MetaPalettes\Listener\SubSelectPalettesListener->getValue(object(DC_Table), 'tl_layout', 'rows')
     (vendor/contao-community-alliance/meta-palettes/src/Listener/SubSelectPalettesListener.php:68)
  at ContaoCommunityAlliance\MetaPalettes\Listener\SubSelectPalettesListener->onLoad(object(DC_Table))
     (vendor/contao/core-bundle/src/Resources/contao/drivers/DC_Table.php:203)
  at Contao\DC_Table->__construct('tl_layout', array('tables' => array('tl_theme', 'tl_module', 'tl_style_sheet', 'tl_style', 'tl_layout', 'tl_image_size', 'tl_image_size_item', 'tl_form_layout', 'tl_bs_grid', 'tl_layout'), 'importTheme' => array('Theme', 'importTheme'), 'exportTheme' => array('Theme', 'exportTheme'), 'import' => array('StyleSheets', 'importStyleSheet'), 'export' => array('StyleSheets', 'exportStyleSheet'), 'stylesheet' => array('bundles/contaobootstrapnavbar/backend.css')))
     (vendor/contao/core-bundle/src/Resources/contao/classes/Backend.php:393)
  at Contao\Backend->getBackendModule('themes', null)
     (vendor/contao/core-bundle/src/Resources/contao/controllers/BackendMain.php:160)
  at Contao\BackendMain->run()
     (vendor/contao/core-bundle/src/Controller/BackendController.php:48)
  at Contao\CoreBundle\Controller\BackendController->mainAction()
     (vendor/symfony/http-kernel/HttpKernel.php:149)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:66)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:188)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/app_dev.php:79)

If you need more information, please let me know

The text was updated successfully, but these errors were encountered:

fritzmg · 2018-11-12T11:58:35Z

Please put your error and log messages in three back ticks, not one.

fritzmg · 2018-11-12T12:00:37Z

Presumably, SubSelectPalettesListener::fetchValueFromDatabase needs to be changed to:

    /**
     * Fetch value from database.
     *
     * @param DataContainer $dataContainer Data container driver.
     * @param string        $strSelector   Selector field name.
     *
     * @return mixed
     */
    private function fetchValueFromDatabase($dataContainer, $strSelector)
    {
        $objRecord = \Database::getInstance()
-           ->prepare(sprintf('SELECT %s FROM %s WHERE id=?', $strSelector, $dataContainer->table))
+           ->prepare(sprintf('SELECT `%s` FROM `%s` WHERE id=?', $strSelector, $dataContainer->table))
            ->limit(1)
            ->execute($dataContainer->id);

        if ($objRecord->next()) {
            return $objRecord->$strSelector;
        }

        return null;
    }

Paddy0174 · 2018-11-12T13:07:09Z

@fritzmg Thanks for finding and solving the error, your solution works like a charm! In 4.6.6 and 4.6.8 the error is solved with your change.

And jepp, back ticks and me, we do know, why we hate each other ;)

dmolineus · 2018-11-21T13:22:51Z

It should be fixed with 842693d. Instead of @fritzmg approach I also switched to doctrine/dbal.

@Paddy0174 It would be great if you would check branch hotfix/2.0.5 if it fixes your issue in the mariadb context.

Paddy0174 · 2018-11-22T16:02:02Z

@dmolineus Thanks, works like a charme. Fixes the issue and doesn't seem to disturb anything else, like contao-bootstrap! :)

Thanks for the fast reaction! :)

dmolineus · 2018-11-23T07:54:33Z

Thanks for confirming that it works. Shipped with the 2.0.5 release.

Paddy0174 changed the title ~~Error with escaping and MariaDB~~ Error with escaping "rows" and MariaDB Nov 12, 2018

fritzmg mentioned this issue Nov 12, 2018

escape parameters for direct SQL query #41

Closed

dmolineus self-assigned this Nov 17, 2018

dmolineus added this to the 2.0.5 milestone Nov 17, 2018

dmolineus added the Defect label Nov 17, 2018

dmolineus added a commit that referenced this issue Nov 21, 2018

Use doctrine/dbal for database access and quote identifiers (See #40).

842693d

Paddy0174 closed this as completed Nov 22, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error with escaping "rows" and MariaDB #40

Error with escaping "rows" and MariaDB #40

Paddy0174 commented Nov 12, 2018 •

edited

Loading

fritzmg commented Nov 12, 2018

fritzmg commented Nov 12, 2018 •

edited

Loading

Paddy0174 commented Nov 12, 2018 •

edited

Loading

dmolineus commented Nov 21, 2018

Paddy0174 commented Nov 22, 2018

dmolineus commented Nov 23, 2018

Error with escaping "rows" and MariaDB #40

Error with escaping "rows" and MariaDB #40

Comments

Paddy0174 commented Nov 12, 2018 • edited Loading

fritzmg commented Nov 12, 2018

fritzmg commented Nov 12, 2018 • edited Loading

Paddy0174 commented Nov 12, 2018 • edited Loading

dmolineus commented Nov 21, 2018

Paddy0174 commented Nov 22, 2018

dmolineus commented Nov 23, 2018

Paddy0174 commented Nov 12, 2018 •

edited

Loading

fritzmg commented Nov 12, 2018 •

edited

Loading

Paddy0174 commented Nov 12, 2018 •

edited

Loading