| Summary: | Database identifiers should be protected with tick marks | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Jan Steinman <jan.steinman> |
| Component: | Database-Schema | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | minor | CC: | caulier.gilles, metzpinguin |
| Priority: | NOR | ||
| Version: | 7.6.0 | ||
| Target Milestone: | --- | ||
| Platform: | macOS (DMG) | ||
| OS: | macOS | ||
| Latest Commit: | https://invent.kde.org/graphics/digikam/commit/474608ef5f74b84bd0f9afbd5c3389f73d95f831 | Version Fixed In: | 7.7.0 |
| Sentry Crash Report: | |||
| Attachments: | SQL that contains identifiers with operand characters throw an error if no escaped with tick marks. | ||
It will work also for sqlite database ? (In reply to caulier.gilles from comment #1) > It will work also for sqlite database ? It would appear so: https://www.sqlite.org/lang_keywords.html The variant with tick marks doesn't work. GRANT ALL PRIVILEGES ON 'digikam.*' TO 'maik'@'127.0.0.1'; 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 ''digikam.*' TO 'maik'@'127.0.0.1'' at line 1 In fact, it must be so-called backticks. Maik (In reply to Maik Qualmann from comment #3) > The variant with tick marks doesn't work. > > GRANT ALL PRIVILEGES ON 'digikam.*' TO 'maik'@'127.0.0.1'; > > 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 ''digikam.*' TO > 'maik'@'127.0.0.1'' at line 1 > > In fact, it must be so-called backticks. > > Maik ARGH! Yes, of course, you are right. I'll update the bug to say so. Not only that, but each hierarchical part must be separately back-ticked. So `digikam.*` will never work, but `digikam`.* should, at least in MySQL. Likewise, `DigiKam-Core.Albums` won't work, but `DigiKam-Core`.`Albums` should. The reference I noted in Comment 2 is ambiguous about Sqlite. I can't find a proper BNF for their language spec. Git commit 474608ef5f74b84bd0f9afbd5c3389f73d95f831 by Maik Qualmann. Committed on 08/03/2022 at 21:03. Pushed by mqualmann into branch 'qt5-maintenance'. add backticks for database requirement description FIXED-IN: 7.7.0 M +2 -1 NEWS M +8 -8 core/libs/database/utils/widgets/dbsettingswidget.cpp https://invent.kde.org/graphics/digikam/commit/474608ef5f74b84bd0f9afbd5c3389f73d95f831 Yes, the hierarchical part was a typo on my part. In the patch it is correct. Maik |
Created attachment 147376 [details] SQL that contains identifiers with operand characters throw an error if no escaped with tick marks. SUMMARY *** In Configure —> Database —> Requirements, SQL to be executed by the user should be delimited with tick marks ("`") so that arbitrary characters can be used. *** STEPS TO REPRODUCE 1. Set up for a mysql/maraidb database. 1. Set up databases with the base name of "DigiKam-" 2. Attempt to follow the instructions in "Configure —> Database —> Requirements" for creating the necessary databases. OBSERVED RESULT "Configure —> Database —> Requirements" says to execute (in part): <code>CREATE DATABASE DigiKam-Core; GRANT ALL PRIVILEGES ON DigiKam-Core.* TO 'DigiKam'@'localhost'; CREATE DATABASE DigiKam-Thumbs; GRANT ALL PRIVILEGES ON DigiKam-Thumbs.* TO 'DigiKam'@'localhost'; CREATE DATABASE DigiKam-Face; GRANT ALL PRIVILEGES ON DigiKam-Face.* TO 'DigiKam'@'localhost'; CREATE DATABASE DigiKam-Similarity; GRANT ALL PRIVILEGES ON DigiKam-Similarity.* TO 'DigiKam'@'localhost'; </code> If that code is executed, MySQL/MariaDB tries to subtract "Core" from "DigiKam", etc. because it does not recognize "DigiKam-Core" as a database name, and it throws an SQL error. EXPECTED RESULT Databases are created SOFTWARE/OS VERSIONS Windows: macOS: 10.15.7 Linux/KDE Plasma: (available in About System) KDE Plasma Version: KDE Frameworks Version: Qt Version: ADDITIONAL INFORMATION Whenever a user can enter SQL identifiers (or portions thereof) and those identifiers can be displayed as something the user can execute, the generated identifier should be surrounded by tick marks ("`") so MySQL/MariaDB will recognize them as identifiers, rather than try to parse them as expressions.