Skip to content

SQL Logic Error when attempting to change SQLCipher password #1690

Closed

Description

@mtissington

Opened an encrypted database and then clicked on Set Encryption to change the password

set encryption

When I clicked OK I got the following error

logic error

Windows 10 latest
DB4S - 3.11.99 (SQLCipher)

Activity

  1. added
    bugConfirmed bugs or reports that are very likely to be bugs.
    on Dec 26, 2018
  2. justinclift commented on Dec 26, 2018

    @justinclift
    Member

    Thanks @mtissington. That'll definitely need looking into. 😄

  3. mtissington commented on Dec 27, 2018

    @mtissington
    Author

    Why is sqlcipher_export being used to change the password?

    According to sqlcipher api
    PRAGMA rekey is all that is required.

  4. chrisjlocke commented on Dec 27, 2018

    @chrisjlocke
    Member

    Related to this (but I guess should ideally be a separate issue) if 'Tools' and 'Set Encryption' is successfully run (using an older nightly) there is nothing logged in the application 'log' window.

    PRAGMA foreign_keys = '1';
    PRAGMA database_list;
    SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
    PRAGMA encoding
    

    After opening a database then encrypting it, that was all that was in the sql log.

  5. chrisjlocke commented on Dec 27, 2018

    @chrisjlocke
    Member

    Why is sqlcipher_export being used to change the password?

    Very much like the 'Modify Structure' dialog, the 'Set Encryption' (and change password, which uses the same code) creates a new database using the new settings, then copies all the data, renames the databases, then deletes the old database.

    The error in question is happening here but I don't know enough to know why its suddenly doing it. Looking back to a working version, that code hasn't changed.

  6. MKleusberg commented on Dec 27, 2018

    @MKleusberg
    Member

    That's weird - it's working fine for me. But I'm still using SQLCipher 3.4 instead of 4.0. Can somebody check with 3.4 and with 4.0?

    The reason we use sqlcipher_export is mainly for simplicity because we need to support the current approach either way while the rekey approach is an optional optimisation. That said, we should probably add that some time 😉 Can you open a separate issue for that? 😄

  7. mtissington commented on Dec 27, 2018

    @mtissington
    Author

    I get the same error when I start with a NON encrypted database and then try to encrypt it (either with 3.4 or 4)

  8. chrisjlocke commented on Dec 27, 2018

    @chrisjlocke
    Member

    That's the process I tried initially. Theoretically its doing the same thing - creating an encrypted database, copying the data, then deleting the original.

  9. justinclift commented on Dec 28, 2018

    @justinclift
    Member

    @MKleusberg Looking at the code that error message comes from:

    if(ok)
    ok = db.executeSQL(QString("ATTACH DATABASE '%1' AS sqlitebrowser_edit_encryption KEY %2;").arg(db.currentFile() + ".enctemp").arg(cipherSettings.getPassword()),
    false, false);
    qApp->processEvents();
    if(ok)
    ok = db.executeSQL(QString("PRAGMA sqlitebrowser_edit_encryption.cipher_page_size = %1").arg(cipherSettings.getPageSize()), false, false);
    if(ok)
    ok = db.executeSQL(QString("PRAGMA sqlitebrowser_edit_encryption.kdf_iter = %1").arg(cipherSettings.getKdfIterations()), false, false);
    if(ok)
    ok = db.executeSQL(QString("PRAGMA sqlitebrowser_edit_encryption.cipher_hmac_algorithm = %1").arg(cipherSettings.getHmacAlgorithm()), false, false);
    if(ok)
    ok = db.executeSQL(QString("PRAGMA sqlitebrowser_edit_encryption.cipher_kdf_algorithm = %1").arg(cipherSettings.getKdfAlgorithm()), false, false);
    // Export the current database to the new one
    qApp->processEvents();
    if(ok)
    ok = db.executeSQL("SELECT sqlcipher_export('sqlitebrowser_edit_encryption');", false, false);
    // Set user version of the new database
    qApp->processEvents();
    if (ok)
    ok = db.executeSQL(QString("PRAGMA sqlitebrowser_edit_encryption.user_version = %1;").arg(db.getPragma("user_version").toInt()), false, false);

    The specific string shown in the dialog isn't inside a QString() call, unlike the sibling SQL statement around it. Is there any way that could be leading things astray? 😄

    The other possibility that comes to mind, is that since it's a SELECT it'll be returning a value. The other statements around it are ones which don't always return a value, so maybe it's a case of executeSQL() not being the right function call for running it?

  10. MKleusberg commented on Dec 29, 2018

    @MKleusberg
    Member

    @justinclift The QString() bit makes no difference. You can tell from the fact that the SQL statement is printed in the error message, so it definitely get passed correctly to the executeSQL() function. The reason why we add QString() to the other lines is to be able to call the QString().tr() function which we don't need for the other line.
    The SELECT should be fine too. executeSQL() discards the returned value which is why we usually don't use it for SELECT statements. But in this case we're actually not interested in the returned values (if indeed there is any) because the SELECT is only a syntactic thing to call the sqlcipher_export extension function.

    What's weird here is that @mtissington says that it happens with SQLCipher 3.4 as well as with 4.0. Not sure what to make of this because it definitely worked before. I have found this issue though which says:

    On c19c72f I cannot perform any operation on encrypted databases. As soon as PRAGMA key = 'some_key'; is executed, then next SQL operation fails with "Error: SQL logic error or missing database". This error also occurs after performing an ATTACH with a KEY specified.

    That said, we've seen similar errors to what you described in the past due to mismatched OpenSSL headers. For instance, if you include OpenSSL headers for one version but then another version is dynamically loaded at runtime you can see this behavior. Can you please make sure that you don't have any OpenSSL headers in the include path other than the ones in /usr/include/openssl?

    So maybe it is after all impossible to just drop-in replace the SQLCipher DLL?

  11. mtissington commented on Dec 29, 2018

    @mtissington
    Author

    I just replaced sqlcipher.dll with a version I had build against OpenSSL 1.1.1a (also need libcrypto-1_1)
    and I get exactly the same issue. But everything else seems to still work.

  12. mtissington commented on Dec 31, 2018

    @mtissington
    Author

    I don't know if this is related but when I try the following in DB4S the ATTACH fails saying it can't find the database instead of creating a new one. When I do the same in sqlcipher.exe it works correctly.

    First I create a new empty database then in a SQL window

    ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
    SELECT sqlcipher_export('encrypted');
    DETACH DATABASE encrypted;
    
  13. justinclift commented on Dec 31, 2018

    @justinclift
    Member

    This one sounds like it'll need some special effort to debug. Probably need to replicate the problem, and attach a debugger to it in order to step through the code as the bug occurs.

    • Setup a working dev environment (Qt Creator) on Windows, with Qt 5.11.3, SQLCipher 4.0.1.
    • Build DB4S in debug mode
    • Step through the problem code

    Technically I could do it, however I'm presently knocking myself out (not literally) getting new online infrastructure + monitoring set up for it. That's probably more important than this, at least for the moment.

  14. chrisjlocke commented on Dec 31, 2018

    @chrisjlocke
    Member

    @mtissington - what version of DB4S? I'm using the nightly from 28 Dec, and that works fine.

    image

    I initially thought the 'key' part might be confusing DB4S, as it obviously doesn't support that, but it seemed to work.

    image

  15. 19 remaining items

  16. mtissington commented on Feb 12, 2019

    @mtissington
    Author

    Hmm, so I installed 1732v1 and now when I attempt to install v2 it says a later version is already installed.

  17. chrisjlocke commented on Feb 12, 2019

    @chrisjlocke
    Member

    The version numbers will be out of bonk as they're one off builds, so the same (or lower) version numbers.
    I'd recommend downloading the .ZIPs, extracting to a folder, and running the .exe from that folder. No need to install.

  18. justinclift commented on Feb 12, 2019

    @justinclift
    Member

    @mtissington Yeah, I dumbly made the v1 builds based off of our master branch (effectively 3.12.x development code). For the v2, realised it would be better built from the same code as the 3.11.1 release, so did that. Thus the complaining from it that v1 (3.12.x) is newer. 😉

    @chrisjlocke Good idea. 😄

  19. mtissington commented on Feb 12, 2019

    @mtissington
    Author

    how do this differ to the nightly?

  20. justinclift commented on Feb 12, 2019

    @justinclift
    Member

    The nightly is also built from the master branch. The v1 was (quite literally) nightly + experimental fix applied.

    The v2 was an updated (slightly tweaked for future compatibility) fix, but applied to the v3.11.x code base instead.

  21. MKleusberg commented on Aug 10, 2019

    @MKleusberg
    Member

    I think we fixed this issue some time ago. Can you give the last nightly build a try, @mtissington? 😄

  22. mtissington commented on Aug 10, 2019

    @mtissington
    Author

    fixed a while back and confirmed still fixed 😄

  23. MKleusberg commented on Aug 11, 2019

    @MKleusberg
    Member

    Awesome! Thank you for confirming 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    SQLCipherbugConfirmed bugs or reports that are very likely to be bugs.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions