SQLCipher 4 and REAL data type retrieval

@sjlombardo @developernotes
In my C++ program, because the SQLCipher 3 database configuration is not the default, I upgraded SQLCipher 3 to SQLCipher 4 using sqlcipher_export(…). But found a problem, SQLCipher 4 REAL type data is all garbled. Later, it was found that SQLCipher 3 saved the data when it was stored. SQLCipher 3 can be read correctly, but SQLCipher 4 cannot be read. (For example: 35.9 is represented as 3.0168844263318e+19, and SQLCipher 3 is read as 35.9. However, after migration, SQLCipher 4 reads 3.0168844263318e+19). Is there a problem? Is there any way to convert? In addition, the REAL type without decimals is normal. For example, 35.0 can be read correctly before and after migration, and I see that the table data is stored in 35 instead of a pile of scientific counts.

1 Like

Hello @weiyan - @developernotes and I both tried this but were unable to reproduce the behavior you described. Therefore I have a few questions:

  1. If you open the database with the SQLCipher command line shell or a program like DB Browser for SQLite, what value to do see when you retrieve the row and column in question?
  2. What is the code you are using in your C++ program to retrieve the value from the column?
  3. Are you able to provide a sanitized copy of the database that you are seeing this behavior with, or a reproducible test program that demonstrates this issue?

1、Open the database with the SQLCipher command line shell or SQLiteStudio the value display is the same as in my program. The index sees the index, and the normal value still shows the normal value.
2、In my C++ program,I use the interface “sqlite3_column_double(…)” to retrieve the value from the column.
3、The attachment is a deprecated version of the database used in my program, and the two tables inside are not modified. The password is “newkey”, “kdf_iter=10”.
4、The old version “SQLITE_VERSION” is 3.15.2 and the new version is 3.26.0. Our old application uses a static library, while the new program uses a dynamic library. When I tried the dynamic libraries from 3.15.2 to 3.26.0, the data migration was no problem.

Since my project was released soon, and because of this problem, the upgrade function was cut off by us, and the new database was used directly. So the old data is lost. When I release the version, I will continue to come back to study this issue.It takes about two weeks.RoundDatabase_1.0-bak.zip (23.2 KB)

Hello @weiyan. Thank you for this additional information, it is helpful. Here are a couple more questions:

  1. What platform are you running this conversion on (e.g. Android, iOS, Windows, and architecture)?
  2. Do you know what version of SQLCipher you were using previously, and which one you are using now? This value is returned from PRAGMA cipher_version;
  3. Could you to provide a copy of the source database from prior to upgrade and export? We would like to use this for comparison and reproduction of the issue.

1、Our program runs on an embedded ubuntu linux system.
2、Previously used is 3.4.1, now using version 4.0.1.
3、The attachment database that was last sent is the original database without upgrades and export operations. I just modified the password and deleted several tables.
4、I think the key to this problem is to use sqlcipher’s static library or dynamic library, because the same version I use dynamic library and static library storage floating point number are exponential garbled, but after upgrading to 4.0.1, static library compiled After the program is migrated, it cannot be displayed normally, and the dynamic library mode can be displayed normally.

Hello @weiyan. Thank you for clarifying, as I thought the database you sent was post-export.

In this case, I have opened the database you referenced on multiple platforms. The database seems to be consistent, and integrity_check reports no errors. On all platforms the values reported from record tb_primary_record_info columns is 3.0168844263318e+19, e.g.

1552654784|3.01688442633188e+19
1552654954|3.01688442633188e+19

I do not see any difference when I open the database between SQLCipher 3 and SQLCipher 4 either. The same values are reported in either case. For does migration of the database does affect the data stored or returned. Completely decrypting the database (i.e. removing SQLCipher from the mix entirely) doesn’t modify the behavior either.

As a result, it seems likely that this problem is introduced in your environment or with your build of SQLCipher. I don’t know what difference static vs dynamic linking would make in the general sense. I suspect there might be something else at play (e.g. how the specific libraries are compiled or the runtime environment). Since you are building SQLCipher yourself for an embedded platform I’m not sure there is much else we can do to help via this public forum.