perl - 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.

question from:https://stackoverflow.com/questions/65899956/dbdsqlcipher-prepare-nor-pragma-is-working

Answer 1

Found the answer!

Steps:

1. Compile openssl (I use 1.1.1) - keep libcrypto.so
2. Compile sqlcipher (I use 4.3.0) with the libcrypto you just created.
3. Download DBD-SQLite (Latest right now is 1.66) and unzip
4. Copy sqlite3.c sqlite3.h sqlite3ext.h from sqlcipher folder to DBD-SQLite
5. Copy libcrypto.so to DBD-SQLite
6. 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!