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.
Found the answer!
Steps:
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!