I have a badly corrupted Sqlite file The usual trick of dumping everything into a sql file and loading it into a fresh file doesn't work, but with a hex editor I can see that the data I need to restore is there

I came across this mode

Can vim display ASCII characters only, and treat other bytes as binary data?

for vi to display non printable characters as hex

Which is great, and will shows me

‹14›‹07› Mr Testy McTesterson me@example.com

but is there anyway to write that to file as it is displayed?

So take the hex value that vi displays as ‹14› in it's buffer and actually changed it to be those characters in a text file

I can do a regex search replace in vi to do it, but then I have to do it for each non printable character one at a time, and it's a pretty large file

Later, I plan to process ‹14›‹07› into the 16 bit integer it should represent, but first I need to be able to get those as real characters into a text file

Many thanks in advance


You might look at xxd which comes with vim and dumps data in hex and printable characters in columns. If you edit the hex you can push the data back through xxd -r to convert it back to binary.

However, looking at your final goal, you will probably need something more powerful like perl, at which I am no expert, but you might find the following useful:

# https://unix.stackexchange.com/a/452784/119298
use strict;
sub fn{ 
    my ($ch,$ch2,$rest) = @_;
    return sprintf("%5u",(ord($ch)<<8)|ord($ch2)).$rest;
my $data = join("",<>);
$data =~ s/(.)(.)([a-zA-Z][ -~]{10,})/fn($1,$2,$3)/ge;
print $data;

It reads in all the data from stdin into the variable $data, then does a global substitute (s/.../.../g) for the pattern consisting of any 2 bytes followed by an alphabetic character (the range a-z and A-Z), followed by 10 or more printable characters (in the range space to tilde, and assuming a C locale). These parts are captured by using () into 3 separate parts, and replaced by a call of the function fn. This is what the e at the very end means.

The function simply returns a string print of the 2 bytes converted to an integer, concatenated with the 3rd unchanged parameter.

To help, here's a simpler version that only does what you wanted, replacing the non-printing characters by <..>.

my $data = join("",<>);
$data =~ s/([^ -~\n])/sprintf("<%02x>",ord($1))/ge;
print $data;

Here the pattern is simpler, namely the range of non-printable characters (and newline), with ^ meaning not. When looking at a simple sqlite file, I found the character immediately before the text data was often a printable character. This is why I used a pattern that tests for an alphabetic starting character, but you will probably need to use better heuristics.

  • Thank you so much, all of that combined has definetly gotten me much closer to retrieving something useable. Will still need to do a fair amout of post processing, but it's looking like most of it is retrievable which will be a massive relief – user28226 Jun 30 '18 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.