search

contao-community-alliance / meta-palettes

Dynamic configured palettes for contao.
6 stars 8 forks source link

Error with escaping "rows" and MariaDB #40

Closed Paddy0174 closed 5 years ago

Paddy0174 commented 5 years ago

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:

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

fritzmg commented 5 years ago

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

fritzmg commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

It should be fixed with 842693de5a5c3535fe3cf50ae505680f11372fe9. 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 commented 5 years ago

@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 commented 5 years ago

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