Repository navigation
Add option to show (small) BLOBs as hex strings #1201
Description
Details for the issue
By default, SQLiteBrowser shows blobs as unicode characters. In comparison, the SQLite Manager for Firefox uses QUOTE (https://sqlite.org/lang_corefunc.html#quote) for small blobs which results in a nice display that also round-trips. SQLite Manager only uses that for blobs smaller than 50 bytes by default (configurable), and also allows choosing the display style (i.e. string or using QUOTE)
This is for the view under "Browse data". See also the attached screenshots.
SQLiteManager display:
I'm opening this issue because:
- DB4S is crashing
- DB4S has a bug
- DB4S needs a feature
- DB4S has another problem
I'm using DB4S on:
- Windows: ( version: 10 1709)
- Linux: ( distro: ___ )
- Mac OS: ( version: ___ )
- Other: ___
I'm using DB4S version:
- 3.10.1
- 3.10.0
- 3.9.1
- Other: ___
I have also:
- Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
- Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20
Reacted by patrolez
Activity
justinclift commentedon Oct 28, 2017
Interesting thought. At the moment when we detect something's a BLOB, it's supposed to use the placeholder text of
BLOBinstead of trying to display it. Not sure why that's not happening for you.We could probably do the same
QUOTEthing too, though we'll need to get the detect-its-a-blob working correctly.Do you have a sample database handy that shows the problem? If so, maybe upload it to dbhub.io and let us know the URL so we can investigate. 😄
Anteru commentedon Oct 28, 2017
I'll get you some sample database so you can test. I'm storing tons of hashes/UUIDs in my databases and being able to see them directly is super-useful (plus you can directly insert using the
X'ABC...values in SQLite manager)justinclift commentedon Oct 28, 2017
Excellent @Anteru, thanks. 😄
Anteru commentedon Oct 29, 2017
Here's a database: https://db4s-beta.dbhub.io/Anteru/repository.db
If you look at fs_contents, some of them will show up as
BLOB, some as plain text. Thefeaturestable is all text for me.Better BLOB detection in the Browse Data tab
MKleusberg commentedon Oct 29, 2017
I've changed the binary detection code now, so in theory with tomorrow's nightly build all the binary characters should be replaced by the 'BLOB' placeholder text. I think it's no big problem to show them as
X'ABCABC...'as well, however, I'm wondering how we should handle inputting binary data directly. The problem is that in theory somebody might wand to enter something likeX'ABas normal text and not have it converted to binary data. What do you all reckon?Anteru commentedon Oct 29, 2017
Thanks a lot :)
The X'ABCDEF' syntax works both directions. Quick example using the SQLite command line interface:
It seems like supporting this for round-trips makes sense. I'm fine if you have some special-case handling for blobs (i.e. open some extra editor), but being able to quickly enter them using the
X'syntax looks desirable. I certainly don't care so much about large data (where I'd seeBLOBin the table view to start with), but small binary chunks are not uncommon.justinclift commentedon Dec 8, 2017
Thinking about this, yeah this does sound like it would be useful for people working with short binary strings. 😄
sensboston commentedon May 11, 2018
We're using BLOBs for GUIDs, please add an option to recognize and display a GUIDs in BLOBs. Thanks!
mgrojo commentedon Oct 5, 2018
If we're talking here about display, DB4S currently have the display formats feature. You can use the "Hex blob" format and it uses the
hex()function for displaying the BLOB as hexadecimal digits.The issue to consider is that you cannot directly edit the value. While the display format is set, you'd have to enter the binary editor and reenter the whole value, because otherwise you'll change the data from binary to ASCII. We should probably make something about this.
stefan123t commentedon Dec 10, 2020
I am debugging an issue with my Mozilla Firefox cert9.db which essentially is a sqllite DB storing the certificates in binary form.
Importing / Exporting with a safely escaped Hex-Format like INSERT INTO t VALUES (X'DEADBEEF'); would be pretty useful here.
Thanks for still considering this feature.
I tried it with the most recent Version, ie. 3.12.1 with mixed results.
That is I can export the nssPublic table, though the BLOB detection seems not consistent, ie. some of the columns are sometimes escaped via QUOTE(a81) and sometimes not. This might depend on the first few bytes of the value.
Export SQL: fix quoting of BLOB values and non-printable strings
mgrojo commentedon Dec 12, 2020
@stefan123t could you test with the next nightly build? The export of BLOB values was not very accurate. Only values with an ASCII NUL (\0) in the first 2KiB were using the QUOTE style. There are also cases where SQLite reports a value as TEXT and then the data is binary and not printable. Both cases (BLOB and "binary" TEXT) use now the QUOTE style and the export is now accurate and more consistent.
stefan123t commentedon Dec 13, 2020
@mgrojo, thanks Manuel that worked a breeze for the export.
Dunno if you are going to apply the same heuristics for the display in the UI. Actually the certificates in cert9.db do contain some valid ASCII content which is readable, so instead of BLOB the text default is okay and one can switch to binary or back to text display anyway.
That the export works was the main issue for me.
Thanks time and again.
Kind regards, Stefan
stefan123t commentedon Dec 14, 2020
@mgrojo, I had a closer look at the changes you made for the nightly build for v3.12.99 (Dec 13 2020).
As the change only affects Export functionality this solves my immediate problem, but probably not the problem, that @Anteru and others logged this issue for. I.e. he wanted to have an option to switch between HEX and ASCII formatting in BLOB columns.
As mentioned IMHO the same heuristics should apply to determining BLOB fields or better columns from plain ASCII. Here the nightly build still shows some of the fields in the column as text and some as BLOB.
DB Browser for SQL Lite v3.12.99 (Dec 13 2020) will display "BLOB Binärdaten können in diesem Modus nicht angezeigt werden. Versuchen Sie in den Binär-Modus zu wechseln." (BLOB binary data can not be displayed in this mode. please try to switch to binary mode)
I would assume that DB4S should support a kind of string function on cells to display only printable characters in text mode. In fact it displays it somewhat readable in those cases where it hinted at being a text field. There are some minor differences because it replaces the unprintable characters with some nice looking [ACK], [EOT], etc. glyphs in my Database Cell Editor but not the row display on the left.
But I cannot switch from BLOB to text cell display for those detected as BLOB.
and
mgrojo commentedon Dec 14, 2020
@stefan123t, yes, the problem that you reported was different, and I consider it a bug, because it prevented an accurate export of the table binary data in SQL. But the original feature request was not "to have an option to switch between HEX and ASCII formatting in BLOB columns", but being able to show the BLOB columns in the browser using a HEX decoding, which more or less can be obtained with the display format. The remaining possible enhancements would be whether that display should be the default for BLOB columns and if input of that binary data in
X'HEX'format should also be allowed.In fact, in the current development version, one can use
quote()as display format, and then enter also the data using theX'HEX'format using theEvaluationmode of the cell editor. That might be near to fulfil the necessity.Regarding BLOB values being shown as ASCII or BLOB in the browser. The current behaviour is to not assume that any BLOB or TEXT value is printable text per se, but a check is done in order to decide. In your screenshot, there is something I don't like: some clearly binary data is shown as text just because the sequence of bytes is correct UTF-8, but it includes non-printable control characters, which should not be treated as text but as binary. In fact, I think you could have problems if you edited that value in the editor.
Do you find any usefulness in having those binary values shown as text? In fact, you can use the hexadecimal editor to see the ASCII part of messages, so that output for me is buggy and unuseful. I have a fix for that, so I'm thinking in committing it, but I'd like to hear first your comments about my explanations.
stefan123t commentedon Dec 16, 2020
Hi Manuel, thanks for your reply and considering feedback very thoroughly. This is very much appreciated. You are right normally there are little to no ASCII text content in binary BLOBs. That is the default should probably be hex.
But I have always been ineterested what you can glean from binary data in ASCII mode, there are usually Debug Symbols, Constants etc. left in human readable form. On UNIX I tend to use strings on unknown binaries just to get a general idea of error messages and usage instructions compiled in.
In my specific example given the certs Database these are the human readable CN of the certificate. The output of eg. "xxd /bin/true | less" in three columns (offset, hex bytes, printable ascii) is something like a gold standard for me since years.
But having the option to switch between printable ascii and hex on a per column basis for display might be sufficient to get a quick overview. I think you would only need to add text as a column option, isn't it ?
For the Editor you are right editing hex data in ASCII has always been dangerous to say the least, given the unnoticeable differences for non-printable characters. Eg. how to distinguish 0x00 (null) and 0x20 (space). But somehow these actually are represented in DB4S edit window with some [NUL] symbols anyhow. I do not have checked whether this is a special font on my system or something in DB4S, my only objection could be that symbols are non-proportional and pretty bold/black but well recognizable.
That said - In your position and for the sake of this issue, I would allow text display format on BLOB columns, but defaulting to BLOB/hex display. If someone needs to edit the hex BLOBs they would probably better need a way to download and edit the data outside of DB4S using a common hexeditor of their choice.
Thanks for listening.
mgrojo commentedon Dec 20, 2020
This is not needed as DB4S includes a fully-working hex-editor in the Edit Database Cell pane (select the Binary mode). This is also the way to see the ASCII part of BLOBs.
So the only use for having a textual representation of binary data inside the Data Browser would be to get a quick glimpse of the content. In order to do that, properly and according to the DB4S design, we would need an SQLite function that converts a BLOB to an ASCII representation (converting non-printable bytes to any desired representation, like dots as done by the hex cell editor). But I haven't found anything useful among the buil-in functions. Given that, it would be a good addition to our extension-formats, in case someone wants to contribute it.
mgrojo commentedon Dec 20, 2020
@sensboston
This is included in our latest release v3.12.1. Take a look at #2342.
Data Browser: avoid showing BLOBs containing unprintable characters a…
SQL Execution Editor: appropriate helping message in reload dialog
Binary detection: end-of-line characters where not treated as text