DBD::SQLcipher prepare nor PRAGMA is working

Question

I'm not being able to select from SQLcipher database with DBD::SQLcipher.

I tried various ways, and it still think the file i'm providing is not sqlcipher db (which i'm sure it is)

The first function tries to connect regular SQLite3 database, and on failure connects to SQLcipher (this does happen).

This is my code:

sub connectSQLite() {
    my $DBH = undef;
    eval {
        local $SIG{__WARN__} = sub {};
        my $dsn = "DBI:SQLite:dbname=dbfile";
        my $userid = "";
        my $password = "";
        $DBH = DBI->connect($dsn, $userid, $password, { RaiseError => 1 });
        $DBH->do('select * from properties;');
    };

    if ($@) {
        eval {
            my $dsn = "DBI:SQLcipher:dbname=dbfile";
            my $userid = "";
            my $password = "";
            my $dbKey = "123";
            $DBH = DBI->connect($dsn, $userid, $dbKey, { RaiseError => 1});
        };
    }

    return $DBH;
}

sub getCurrentSensorsList() {
    my $dbHandler = connectSQLite() || return 0;

    $dbHandler->do("PRAGMA key=\"123\";");
    my $sqlStatment = "SELECT id FROM sensors;";
    my $statementHandler = $dbHandler->prepare($sqlStatment); #### this is line 46

    print Dumper $dbHandler;
    print Dumper $statementHandler;

    return $statementHandler->fetchall_arrayref();
}

my $sensorsData = getCurrentSensorsList();
print Dumper $sensorsData;

This is the output (I marked the code with line 46):

DBD::SQLcipher::db prepare failed: file is encrypted or is not a database at /talm/Repos/devops/dependencies/agent/linux//Linux/UpgradeHandler.pm line 46.
DBD::SQLcipher::db prepare failed: file is encrypted or is not a database at /talm/Repos/devops/dependencies/agent/linux//Linux/UpgradeHandler.pm line 46.

Tried also:

my $sqlStatment = "PRAGMA key=\"123\"; SELECT " . join(", ", @$columns) . " FROM sensors;";

And the output of it is:

$VAR1 = bless( {}, 'DBI::db' );
$VAR1 = bless( {}, 'DBI::st' );
$VAR1 = [];

Which means it works- but prepare probably cannot retrive the data from the select statment because there are 2 commands there.

In general, DBI doesn't like multiple statements in one call. — simbabque, Jan 26 '21 at 11:14
I took a quick look at DBD::SQLcipher and I think this is going to be tricky. It looks like it was a one-off by someone forking DBD::SQLite, replacing a couple of things in the code, and then abandoning it. It might just be broken. Good luck! :-/ — simbabque, Jan 26 '21 at 11:21
The test results are not encouraging :-/ http://matrix.cpantesters.org/?dist=DBD-SQLcipher+0.02 — Dave Cross, Jan 26 '21 at 12:20
fml, tnx. i'll try to compile DBD::SQLite with SQLCipher code myself and update. Probably alternative solution for me is to write c++/python code that does the same and include it, but i tried to avoid it. — Tal Mishaan, Jan 26 '21 at 13:09

score 0 · Accepted Answer · answered Jan 26 '21 at 14:54

Found the answer!

Steps:

Compile openssl (I use 1.1.1) - keep libcrypto.so
Compile sqlcipher (I use 4.3.0) with the libcrypto you just created.
Download DBD-SQLite (Latest right now is 1.66) and unzip
Copy sqlite3.c sqlite3.h sqlite3ext.h from sqlcipher folder to DBD-SQLite
Copy libcrypto.so to DBD-SQLite
Edit LDFLAGS, LDDLFLAGS, CFLAGS add this after all defenition:

push(@CCFLAGS, "-I/usr/include/"); ## or the include folder of openssl
push(@CCFLAGS, "-L.");
push(@CCFLAGS, "-ldl");
push(@CCFLAGS, "-lpthread");
push(@CCFLAGS, "-lcrypto");

push(@LDFLAGS, "-L.");
push(@LDFLAGS, "-ldl");
push(@LDFLAGS, "-lpthread");
push(@LDFLAGS, "-lcrypto");

push(@LDDLFLAGS, "-L.");
push(@LDDLFLAGS, "-ldl");
push(@LDDLFLAGS, "-lpthread");
push(@LDDLFLAGS, "-lcrypto");

then:

perl Makeperl.PL
make
sudo make install

And my code works!

DBD::SQLcipher prepare nor PRAGMA is working

1 Answers1