Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
821 views
in Technique[技术] by (71.8m points)

perl - DBD::SQLcipher prepare nor PRAGMA is working

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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!


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...