View Issue Details

IDProjectCategoryView StatusLast Update
0006290OXID eShop (all versions)4.01. Database handlingpublic2017-02-21 10:19
Reportermhellweg Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.9.2 / 5.2.2 
Fixed in Version4.10.4 / 5.3.4 
Summary0006290: View cleanup during view generation chokes on certain table names (=> Oxid EE)
DescriptionDuring view generation the function "_cleanInvalidViews" is called on "oxShop". This is used to clean the database of views that are no longer needed.

However, under certain circumstances, the existance of unusual *tablenames* (note: NOT *view* names!) can cause this function to throw database errors after creating views for subshop 1 resulting in incomplete view generation for all other subshops.

This is caused by two factors:
- The query used to select views includes an unescaped underscore (=> mysql wildcard), so not only *views* are selected but *all* tables starting with "oxv"
- The query used to drop views does not quote the view name

If a table name that is selected in the first step contains characters that are trouble in the second step we get the described DB error
Steps To ReproduceSetup:
- create a database table named e.g. "oxvouchers_backup-2015-12-17"
- initiate view generation via backend
- EXCEPTION.log shows something like:

"oxConnectionException-oxException (time: 2015-12-17 09:25:26): [1064]: mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2015-12-17' at line 1] in EXECUTE (DROP VIEW IF EXISTS oxvouchers_backup-2015-12-17"

- views for other subshops are not generated
Additional InformationFix:
- Change view select query in function "_getAllViews" in "oxShopViewValidator" from
"SHOW TABLES LIKE 'oxv_%'"
to
"SHOW TABLES LIKE 'oxv\_%'"

- Change view drop query in function "_cleanInvalidViews" in "oxShop"
from
'DROP VIEW IF EXISTS ' . $sView
to
"DROP VIEW IF EXISTS `$sView`"
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionNot defined

Activities

alfredbez

2016-12-13 13:31

reporter   ~0011900

Pull Request: https://github.com/OXID-eSales/oxideshop_ce/pull/522

anton.fedurtsya

2017-02-21 10:19

administrator   ~0011982

Thanks for your pull request, Alfred. It was merged recently.