Repository navigation
[Bug]: Database cell type not updated properly when copy/pasting blob data #3307
Description
What did you do?
When copying BLOB data into cells in the database which contain TEXT data, the BLOB will be copied into the cell and DB4S will display the type as BLOB, but when the database is read through the sqlite3 command line or python library it will be read as an empty TEXT field.
Steps to Reproduce:
- Create a new database and initialize as follows
CREATE TABLE "test_table" ( "field" BLOB );
INSERT INTO "test_table" ("field") VALUES (X'DEADBEEF');
INSERT INTO "test_table" ("field") VALUES ("String");- Open the database in DB4S and go to the "Browse Data" tab
- Use
ctrl+candctrl+vto copy theBLOBvalue over theTEXTvalue - Write the changes to the database
Verifying through sqlite3 cli
- Open the database
sqlite3 /path/to/database.db - Dump raw data with
.dump
-- Output abridged
INSERT INTO test_table VALUES(X'deadbeef');
INSERT INTO test_table VALUES('ޭ��'); -- BLOB field is output as corrupt TEXTVerifying through python's sqlite3 module
Using the following script
import sqlite3
conn = sqlite3.connect('/path/to/database.db')
cursor = sqlite3.Cursor(conn)
cursor.execute("SELECT field FROM test_table")
for row in cursor.fetchall():
print(type(row[0]), row[0])Depending on the exact binary data in the BLOB field the script will either crash with sqlite3.OperationalError: Could not decode to UTF-8 column 'field' with text'����' or will output the bytes field directly interpreted as UTF-8.
What did you expect to see?
I expected the BLOB value to be exactly copied over the text cell so that the two output lines of the .dump command would be identical.
What did you see instead?
The BLOB data was saved as corrupt text. Depending on how this data is retrieved, it either comes out as garbled text or crashes the program due to invalid UTF-8 encoding.
DB4S Version
3.12.2
What OS are you seeing the problem on?
Linux
OS version
Arch Linux
Relevant log output
No response
Prevention against duplicate issues
- I have searched for similar issues
Activity
justinclift commentedon Mar 28, 2023
Interesting. As a thought, would you be ok to try our very latest "continuous" AppImage and see if the problem still shows up?
https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/continuous
Not saying that it's definitely fixed in that, it's more that we've made a lot of improvements since the 3.12.2 release, including some that might address this.
nhhollander commentedon Mar 28, 2023
Looks like the issue is still present in that build.
justinclift commentedon Mar 28, 2023
Ouch. Thanks for testing it @nhhollander.
justinclift commentedon Mar 28, 2023
I'm seeing the same behaviour here, creating the table with the above steps.
When opening the database in DB4S, it thinks both of the cells have identical contents and seems to display them as such in the Browse Data tab.
They're clearly not stored in the database in the same format though, which is causing interoperability problems with other programs.
justinclift commentedon Mar 28, 2023
@MKleusberg @mgrojo @scottfurry @lucydodo Anyone have time/interest to investigate this? 😄
i-s-o commentedon Mar 28, 2023
Doesn't
sqlite3store the data type at the cell level?When you insert a TEXT value to a cell, it will be of type TEXT. Then, it looks like when DB4S inserts a new value in the same cell using the Browser, it keeps the original data type. This may very well be the desired behavior (it is for me).
To override the cell type to BLOB you have two options:
UPDATE test_table SET "field" = X'deadbeef' FROM test_table WHERE rowid = 2;Edit Database Celldock, clear the existing data, then switch to BLOB mode, then paste the hex representation of your data:justinclift commentedon Mar 28, 2023
Yep.
Though, doesn't it seem sensible that if you copy one cell to another, then DB4S should make sure the destination cell ends up "the same" as the source?
I'm trying to think of a scenario where that's not the expected behaviour, but nothing is springing to mind. (Am actually very, very tired though)
Thoughts?
chrisjlocke commentedon Mar 28, 2023
In the same database, if you click 'File' and 'Export' and 'Database to SQL file', does the SQL show two 'blob' lines?
Mine does... 🤔
(this is on Windows, just to muddy the waters...)
chrisjlocke commentedon Mar 28, 2023
Editing the edited field afterwards and writing changes then 'corrects' the issue.
(ie, so DB4S shows the binary data 'de ad be ef' .. change the field by changing it to something else, applying, then changing it again to 'de ad be ef' again).
(short way round of what @i-s-o did...)
nhhollander commentedon Mar 28, 2023
Since the actual type of a cell in sqlite is directly associated with the value of the cell, I would expect that it is also copied over, especially when the data types are incompatible. There could be situations where you want to copy values but retain the destination cell type, but that feels like it should be an alternative behavior (maybe with a different key combination, like
alt+ctrl+vorshift+ctrl+v).As far as overriding the type by pasting into the "Edit Database Cell" field, that does work but you can only update cells one at at time that way, vs selecting multiple cells in the "Browse Data" view and pressing
ctrl+vto overwrite all of them at once. That's what I was doing when I initially encountered this issue.If you execute that query as shown the cells retain the
TEXTtype andX'deadbeef'becomesX'deadefbfbdefbfbd. I think this is because0xdeadbeefis not a valid utf-8 string and when exporting it to a sql file it has to forcibly adjust the value or crash.Paste non-text data as BLOB
mgrojo commentedon Aug 4, 2023
I've made an improvement. Now, if we paste non-text data, we set it as BLOB datatype in the DB. That is, If we detect it as BLOB for display, we set the data as BLOB in the DB.
Ideally, if we are copying internally, we should preserve the data type of the original cell(s), but with this workaround, at least we are not inserting binary data as if it were text. This will avoid crashing other applications like sqlite3 if the data cannot be parsed as text.
The ideal solution is difficult, because the current codebase doesn't take data types into account, relaying always in SQLite type affinity. But this approach has some holes:
We were always pasting as text and then the data affinity converted it to INTEGER, REAL or TEXT according to the column type. But if the column is BLOB, the result is TEXT, even if that didn't make sense. You could paste an image, and although we said BLOB in the cell, it was actually TEXT. You could see it using the mentioned trick with
.dump. Now if we copy data which we say it's BLOB, it's inserted as BLOB type in the DB.I don't know if you think this solves the issue, but at least I will remove the release-blocker label.
mgrojo commentedon Aug 4, 2023
And thanks for the detailed report, @nhhollander. Could you test the next nightly build to see if this has some undesired side effect?
Paste non-text data as BLOB
sky5walk commentedon Apr 16, 2024
I cannot edit a BLOB field containing only text with the latest nightly. :(
The text is viewable in the Cell Editor, but all attempts to Apply changes are blocked with:
EDIT: I can make changes if I switch to HEX in the Cell Editor. Not very convenient but a workaround.
chrisjlocke commentedon Apr 16, 2024
Could you previously? And why have you raised this in an issue about pasting BLOB data?
sky5walk commentedon Apr 16, 2024
Yes, I believe I could always edit a blob with the Cell editor in text mode.
I post here because pasting large buffers of text are treated as a blob in SQLite or at the least, should be allowed since it is just binary data anyway.