DBD：：DBD密码准备或PRAGMA正在工作

Question

我无法使用DBD::SQLcipher从SQLcipher数据库中进行选择。

我尝试了各种方法，它仍然认为我提供的文件不是sqlcipher (我确信它是这样的)

第一个函数尝试连接常规的SQLite3数据库，如果失败，则连接到SQLcipher (确实会发生)。

这是我的密码：

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;

这是输出(我用第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.

也曾尝试：

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

它的产出是：

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

这意味着它可以工作，但是准备可能无法从select状态中检索数据，因为那里有两个命令。

Answer 1

找到答案了！

步骤：

编译openssl (我使用1.1.1) -使用libcrypto您只需使用libcrypto.so

• Compile DBD-SQLite (最新版本为1.66)，并将sqlit3.c sqlite3.h sqlite3ext.h从sqlci文件夹解压缩到DBD-SQLite

• Copy libcrypto.so到DBD-SQLite

• Edit LDFLAGS、LDDLFLAGS、CFLAGS添加以下内容：

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");

然后：

perl Makeperl.PL
make
sudo make install

我的密码有效！