Prompting for encryption password on unencrypted database

[joelworsham]

Details for the issue

I download an sql file from a site backup on WP Engine. Multiple agents ensure me the file is NOT encrypted. One specific response he had when asked was:

Positive because when I cat the file out its not encrypted at all.
It’s just plaintext SQL

I download the file and try to open it and it prompts me for the password.

I click "ok" and it reloads that same prompt.

I click "cancel" and it tells me the file is invalid:

Useful extra information

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: ___ )
• Linux: ( distro: ___ )
• Mac OS: ( _version:10.11.6)
• Other: ___

I'm using DB4S version:

• 3.9.1
• 3.9.0
• Other: ___

I have also:

• Tried out the nightly version: https://github.com/sqlitebrowser/sqlitebrowser#windows
• Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[revolter]

I think you're trying to open up an .sql file, and not a database .sqlite file. If you open it up in a text editor, does it contain plain text SQL?

[revolter]

I wonder if we can detect if the file is a database or not, and print something like "Not a database".

[chrisjlocke]

Just to elaborate this answer; a database consists of tables, fields and records, etc. A .sql file is instructions (in SQL) to recreate that database. As such, DB4S cannot 'open' that .sql file, but it can import it, so you can create a blank database, then import that .sql file. :)

[justinclift]

Ahhh, yeah. That definitely sounds like a text file with SQL statements in it, not a SQLite database file itself.

@joelworsham Not to worry though, you should be able to import it using File → Import → Database from SQL file...

Are you ok to try that out and let us know if it worked for you? 😄

[chrisjlocke]

I wonder if we can detect if the file is a database or not

Should be easy to detect if the first 100 characters or so of the file contains pure valid SQL commands .... usually only 'drop table if not exists' and all that jazz...

[justinclift]

@revolter I wonder if we can detect if the file is a database or not, and print something like "Not a database".

Yeah, we kind of do already. We first try to detect if it's a database file, and if it's not recognisable then we assume it's an encrypted file instead. So, we then pop open a dialog asking for a password (etc).

I suppose we could add a step in between those, where we try to detect if the file is standard text in SQL statement format, and automatically open the Import dialog if it is. No idea how practical that would be to implement though... 😉

[joelworsham]

Thanks guys. I thought it was a valid file, but it was indeed just a dump, plaintext. It would be nice if the program could give a better response to this though. I'm willing to bet many others have done exactly what I did. Perhaps you can somehow tell that it is a DB dump and tell the user something like:

It appears you're trying to import a database dump. Instead, import the file blah blah

Hi everybody, I just had this dialog appear on a corrupted but unencrypted database file (sqlite shell says "Error: database disk image is malformed"). So it seems like DB4S just asks for a password as soon as it is unable to read a database file, no matter the actual reason.

[justinclift]

@Nico-HA Yep, that's definitely the behaviour at the moment. We'll likely put some effort into improving that, so it tries a few other file types (csv, sql, cert) before jumping to the conclusion it's encrypted.

Not sure when, but we'll get around to it. 😄

@justinclift Thanks for the explanation! No big deal, just good to know 👍

[mgrojo]

Just for the record. This just happened to me after a crash of DB4S. It wasn't the first that I had a crash with this database, but this was the first time that the password dialog opened after opening the DB file again in a new execution. I noticed that a journal file was in the same directory as the database file. I moved it to other location and now I can open again the database.

[buckleykm]

I noticed that a journal file was in the same directory as the database file. I moved it to other location and now I can open again the database.

This was the fix for me!

[azeemhowarth]

Hi,
I am having a similar issue. Upon attempting to open a SQL lite file, I get the following screen

[justinclift]

@azeemrh Have you checked for the presence of a journal file or similar, in the same folder as the database?

[azeemhowarth]

Yes, I have a journal file there