Hi,
I’m involved in a project that compiles SQLCipher for PHP. We have working scripts that have suited us just fine for many years.
I know that Zetetic doesn’t officially support SQLCipher for PHP and that’s ok.
I’m currently looking at adapting those scripts for SQLCipher 4.7.0. I have managed to adjust the compile flags to get it to compile.
I then run a battery of tests to make sure I can read and write a SQLCipher database using PDO with the sqlite driver in PHP (which is using a SQLCipher-modified version).
I observed some subtle differences in 4.7.0 vs 4.5.7.
My PHP script is simple: it created a new, empty SQLite (SQLcipher) database and creates a table:
<?php
try {
$dbh = new PDO("sqlite:/tmp/test.pds");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query("PRAGMA key = 'testing123'");
$dbh->exec("PRAGMA cipher_migrate;");
$create = "CREATE TABLE IF NOT EXISTS users (name TEXT NOT NULL)";
$dbh->exec($create);
$insert = "INSERT INTO users(name) VALUES(:name)";
$stmt = $dbh->prepare($insert);
$stmt->bindValue(":name", "miguel");
$stmt->execute();
echo $dbh->lastInsertId();
} catch (PDOException $e) {
echo $e->getMessage();
exit(1);
}
Note the PRAGMA cipher_migrate. This was thrown in just to test that the function works, as we had to deal with some older SQLCipher databases from < SQLCipher 4.0 days.
On SQLCipher 4.7.0, I notice that the PRAGMA cipher_migrate throws an error:
root@sqlcipher-4-7-0:/usr/local/src/sqlcipher-tests# php test_sqlcipher_write.php
2025-04-03 03:34:48.896: WARN CORE sqlcipher_codec_ctx_migrate: DETACH DATABASE migrate failed: 1
2025-04-03 03:34:48.897: ERROR CORE sqlcipher_codec_ctx_migrate: an error occurred attempting to migrate the database - last error 1
2025-04-03 03:34:48.897: ERROR CORE sqlcipher_codec_pragma: error occurred during cipher_migrate: 1
2025-04-03 03:34:48.905: ERROR CORE sqlite3Codec: identified deferred error condition: 0
2025-04-03 03:34:48.905: ERROR CORE sqlcipher_codec_ctx_set_error 1
SQLSTATE[HY000]: General error: 7 out of memory
I am surprised by the ‘out of memory’ as the machine has 4GB of RAM and is only using about 270MB.
If I omit the PRAGMA cipher_migrate; entirely, everything works great - I can create my table, the database looks encrypted, and I can read it back with this:
<?php
try {
$dbh = new PDO("sqlite:/tmp/test.pds");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query("PRAGMA key = 'testing123'");
$dbh->exec("PRAGMA cipher_migrate;");
$result = $dbh->query("SELECT name FROM users;");
foreach ($result as $row) {
print_r($row);
}
$result = $dbh->query("PRAGMA cipher_integrity_check;");
foreach ($result as $row) {
print_r($row);
}
$result = $dbh->query("PRAGMA cipher_version;");
foreach ($result as $row) {
print_r($row);
}
$result = $dbh->query("PRAGMA compile_options;");
foreach ($result as $row) {
print_r($row);
}
} catch (PDOException $e) {
echo $e->getMessage();
exit(1);
}
My question is simply this: is it expected that PRAGMA cipher_migrate now throws an error on a database that doesn’t need to be migrated, e.g which is brand new? If so, that’s fine, I can rest assured 
I noted in the release notes you said " * Improved error handling in sqlcipher_export() and PRAGMA cipher_migrate", and you said a similar thing in version 4.6.1’s changelog (" * Improves error logging in PRAGMA cipher_migrate"), which I hadn’t tried upgrading to yet, and I was wondering if that was one of the implications and is expected.
As a separate topic, I noted that if I created a new file (with the same sort of script as above) but set PRAGMA cipher_compatibility = 2, I got no errors writing the database, but if I read the database with PRAGMA cipher_compatibility = 2, I got these errors (yet, I was able to read the SQLcipher database just fine nonetheless):
2025-04-03 03:42:55.542: ERROR CORE sqlcipher_page_cipher: hmac check failed for pgno=1
2025-04-03 03:42:55.542: ERROR CORE sqlite3Codec: error decrypting page 1 data: 1
2025-04-03 03:42:55.542: ERROR CORE sqlcipher_codec_ctx_set_error 1
2025-04-03 03:42:55.629: ERROR CORE sqlcipher_page_cipher: hmac check failed for pgno=1
2025-04-03 03:42:55.629: ERROR CORE sqlite3Codec: error decrypting page 1 data: 1
2025-04-03 03:42:55.629: ERROR CORE sqlcipher_codec_ctx_set_error 1
I also note that I am able to run PRAGMA cipher_migrate; on such a database that’s been opened in compatibility_mode = 2; (but only once - if I try to run it again, I get the original error. To me that does seem to make sense if, indeed, it’s actually explicitly intended that now PRAGMA cipher_migrate explicitly fails if there is nothing to migrate?)
We have a use case where we need to support auto-upgrading old SQLCipher 2 databases to 4, and I had not seen these ERRORs before in the past when opening a database in compatability_mode = 2, but I don’t know if it’s just because I am cheating by having made that database using the latest version And again, since I seem to still be able to both read and upgrade (migrate) that database, that’s great.
Hope that makes sense! Just seeking reassurance that the changed behaviour in PRAGMA cipher_migrate is intended (hard errors if there is nothing to actually upgrade). We can and do already code around that based on a metadata version comparison in our real code (if not our tests), I just wanted to be sure.
Thanks again!