Title: [Bug]: Database cell type not updated properly when copy/pasting blob data · Issue #3307 · sqlitebrowser/sqlitebrowser · GitHub
Open Graph Title: [Bug]: Database cell type not updated properly when copy/pasting blob data · Issue #3307 · sqlitebrowser/sqlitebrowser
X Title: [Bug]: Database cell type not updated properly when copy/pasting blob data · Issue #3307 · sqlitebrowser/sqlitebrowser
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 p...
Open Graph 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 re...
X 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 re...
Opengraph URL: https://github.com/sqlitebrowser/sqlitebrowser/issues/3307
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"[Bug]: Database cell type not updated properly when copy/pasting blob data","articleBody":"### What did you do?\n\nWhen 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.\r\n\r\n### Steps to Reproduce:\r\n1. Create a new database and initialize as follows\r\n```sql\r\nCREATE TABLE \"test_table\" ( \"field\" BLOB );\r\nINSERT INTO \"test_table\" (\"field\") VALUES (X'DEADBEEF');\r\nINSERT INTO \"test_table\" (\"field\") VALUES (\"String\");\r\n```\r\n2. Open the database in DB4S and go to the \"Browse Data\" tab\r\n3. Use `ctrl`+`c` and `ctrl`+`v` to copy the `BLOB` value over the `TEXT` value\r\n4. Write the changes to the database\r\n\r\n### Verifying through `sqlite3` cli\r\n1. Open the database `sqlite3 /path/to/database.db`\r\n2. Dump raw data with `.dump`\r\n```sql\r\n-- Output abridged \r\nINSERT INTO test_table VALUES(X'deadbeef');\r\nINSERT INTO test_table VALUES('ޭ��'); -- BLOB field is output as corrupt TEXT\r\n```\r\n\r\n### Verifying through python's `sqlite3` module\r\nUsing the following script\r\n```py\r\nimport sqlite3\r\n\r\nconn = sqlite3.connect('/path/to/database.db')\r\ncursor = sqlite3.Cursor(conn)\r\ncursor.execute(\"SELECT field FROM test_table\")\r\nfor row in cursor.fetchall():\r\n print(type(row[0]), row[0])\r\n```\r\nDepending 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.\r\n\n\n### What did you expect to see?\n\nI 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.\n\n### What did you see instead?\n\nThe `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.\n\n### DB4S Version\n\n3.12.2\n\n### What OS are you seeing the problem on?\n\nLinux\n\n### OS version\n\nArch Linux\n\n### Relevant log output\n\n_No response_\n\n### Prevention against duplicate issues\n\n- [X] I have searched for similar issues","author":{"url":"https://github.com/nhhollander","@type":"Person","name":"nhhollander"},"datePublished":"2023-03-28T00:48:18Z","url":"https://github.com/sqlitebrowser/sqlitebrowser/issues/3307"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:4fe5a248-cb64-beae-1078-21362a5e207b |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | CE8A:1F037:19BF77:223C6D:6AFD1185 |
| html-safe-nonce | c4e98b57771db73b3b69b6a2f23d7e55e2421d587558c64c0696623e6d9f7c04 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJDRThBOjFGMDM3OjE5QkY3NzoyMjNDNkQ6NkFGRDExODUiLCJ2aXNpdG9yX2lkIjoiODUyNTM4MDQxMDcwMjM2OTE1NyIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 60d76e8eef6cf6db62e7791f8c3f693f080ea9675c751e8c0ed134f8f7bea678 |
| hovercard-subject-tag | issue:1643028219 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/sqlitebrowser/sqlitebrowser/3307/issue_layout |
| twitter:image | https://opengraph.githubassets.com/b6ee99bf8b614d62036cbc8a76dabaf93633abc8e2e503a973b9bfe163373de7/sqlitebrowser/sqlitebrowser/issues/3307 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/b6ee99bf8b614d62036cbc8a76dabaf93633abc8e2e503a973b9bfe163373de7/sqlitebrowser/sqlitebrowser/issues/3307 |
| og:image:alt | 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 re... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | nhhollander |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7c34e25df4e46e972c8f0225e63410231bdeb9ea5634597c712796bbcdb2e627 |
| turbo-cache-control | no-preview |
| go-import | github.com/sqlitebrowser/sqlitebrowser git https://github.com/sqlitebrowser/sqlitebrowser.git |
| octolytics-dimension-user_id | 7454271 |
| octolytics-dimension-user_login | sqlitebrowser |
| octolytics-dimension-repository_id | 19416551 |
| octolytics-dimension-repository_nwo | sqlitebrowser/sqlitebrowser |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 19416551 |
| octolytics-dimension-repository_network_root_nwo | sqlitebrowser/sqlitebrowser |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 8fa571ebaa95e4e6bf872197c0e84b7dc48e6785 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width