How to detect blob data in a text field in sqlite?

I have occasionally run into a issue with one of my user’s data files has what appears to be blob data in a text field in their data file.

Here is the latest problem. I have a field where a folder reference is saved and when I try to read it and convert it to a folderitem, bam big fat error.

If I look at the field in DBBrowser or Sqlite Manager, it says that it contains blob data. It’s actually garbage and the user has no idea how it got into the field.

Any way to read this data and determine that is actually blob data and not an actual text data?

Can you read the data into a string?

If so look for bytes that should not be in the expected text for the expected encoding? Should be straightforward a file path.

Alternative (or in addition) you could check if the string size is reasonable for a file path as a first pass.

But, if your app saves the data into the DB in the first place, it sounds like somewhere in your app your code might be saving the contents of a file instead of a file path into that field in some obscure situation.


1 Like

Yes, +1 to find the root cause, even if that’s painful.

The root cause is our users sometimes import data and we believe that is how this is happening.

I am trying to read the first character of the data to see if it is a printable ASCII character as a way to determine if it is legitimate or not, but that failed the other day when the first few characters were printable ASCII and the rest was binary.

you could play with the file extension and limit it to text files as a first step, perhaps analizing the file size too to avoid upload of very(!) huge files. In a next step you can search the whole file for special characters: (in binary data you will always find something, don’t check the first character only).

Last but not least you could use RegEx to replace all invalid characters, question is if that makes sense, you probably only want to reject any try to upload the latest dmg file of whatsoever ;-). might help you as well a bit, and last but not least Joe Strout has a lot of tools in his StringUtils Module: Joe's REALbasic Page. It is Realbasic code but still working in Xojo w/o too many changes.