search

pawelsalawa / sqlitestudio

A free, open source, multi-platform SQLite database manager.
https://sqlitestudio.pl
Other
4.67k stars 571 forks source link

Datagrid with foreign key #5074

Closed marcpley closed 1 week ago

marcpley commented 3 weeks ago

Details

When modifying data in a table with the grid tab, I got an error when selecting a foreign value in the list if the value contain a quote (') [07:02:50] Cannot edit this cell. Details: Error while executing SQL query on database 'Apostrophe': near "A": syntax error

Steps to reproduce

With the attached SQLite file, browse "Table2" and try to modify column "Field2_2" row 3. quote.sqlite3.tar.gz

Operating system

Linux mint 20.1 Kernel: 5.4.0-198-generic x86_64 bits: 64 compiler: gcc v: 9.4.0 Desktop: Cinnamon 4.8.6 wm: muffin dm: LightDM Distro: Linux Mint 20.1 Ulyssa base: Ubuntu 20.04 focal

SQLiteStudio version

3.4.4

marcpley commented 1 week ago

I test with this value for the foreign key :

../guiSQLiteStudio/datagrid/sqlqueryitemdelegate.cpp line 335 cb->setValue(item->getValue());

The string is good: DEBUG: QVariant(QString, "Pl'anche")

But after, somewhere in QueryExecutorParseQuery we have that: Query parsed: "SELECT (Planche == 'Pl'anche') AS curr, ..."

I would be necessary to escape the quote in the data but I don't know where it must be done...

I'm looking for calls of SqlQueryItemDelegate::createEditor but I don't find.

pawelsalawa commented 1 week ago

This required proper value quoting for generated SQL. I've replaced wrapValueIfNeeded() with valueToSqlLiteral() (introduced earlier today) in FkComboBox::getSqlForFkEditor().