Re: DBD::SQLite select fails

Howdy bulrush! Could you be so kind and post complete, working code that demonstrates the problem? Yours not only doesn't compile (writeerr() is not included), it also doesn't do anything. Furthermore, it'd be nice if you could use proper <code> tags for your code, since that'll allow us to download code rather than having to copy and paste it.

Once we've got some code that demonstrates the issue you're experiencing, we'll be in a much better position to help you.

EDIT: to expand on the above, the following test script IS working for me:

#!/usr/bin/perl

use strict;
use warnings;
use feature qw/say/;
use DBD::SQLite;
use Data::Dumper;

our $dbfile = "test.db";

my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
    AutoCommit => 1,
    RaiseError => 1
});

# create table, if necessary
$dbh->do("CREATE TABLE IF NOT EXISTS data (skey TEXT PRIMARY KEY, sval
+ue TEXT);");

# insert data
$dbh->do("INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', 
+'Ge 1:1-more \&stuff\& here ');");

# fetch data
my $sth = $dbh->prepare("SELECT skey, svalue FROM data WHERE skey = 'S
+tuff';");
$sth->execute();

my $results = $sth->fetchall_hashref("skey");
say Dumper $results;
[download]

This correctly prints:

$VAR1 = {
          'Stuff' => {
                       'svalue' => 'Ge 1:1-more &stuff& here ',
                       'skey' => 'Stuff'
                     }
        };
[download]

Comment on Re: DBD::SQLite select fails Select or Download Code

Replies are listed 'Best First'.
Re^2: DBD::SQLite select fails by bulrush (Scribe) on Aug 12, 2014 at 11:32 UTC
Thanks. You are actually not using my code (because you escape the ampersands), but your answer does help me. Because my real questions were: Is there a problem with my connect() statement? And, what characters do I have to escape in the SQL statement and how do I escape them? So, I will escape those ampersands. After searching Google and the SQLite docs, I could not find which characters to escape nor how to escape them. Do you know which I have to escape in the SQL statement? Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)	[reply]
Re^3: DBD::SQLite select fails by AppleFritter (Vicar) on Aug 12, 2014 at 12:31 UTC
You are actually not using my code (because you escape the ampersands), It's a habit of mine; I tend to escape special characters in double-quoted strings. After all, you said you wanted to execute the following SQL statement: `INSERT INTO data (skey, svalue) VALUES ('Stuff', 'Ge 1:1-more &stuff& +here ');` [download] without any interpolation possibly happening in Perl. However, in this case it's not actually necessary to do so: remove the backslashes from the `INSERT OR REPLACE` statement, and the script will still work. Don't be afraid to experiment; give it a try. So, I will escape those ampersands. After searching Google and the SQLite docs, I could not find which characters to escape nor how to escape them. Do you know which I have to escape in the SQL statement? It depends. On Perl's side, you will have to escape everything that Perl might otherwise interpret/interpolate. For the database side, take a look at SQLite's syntax diagrams, and also the reference page for expressions, which has this to say: A string constant is formed by enclosing the string in single quotes (`'`). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. Then again, the only thing that you'll have to escape on the DB is the single quote character itself. To do that, put in two single quotes. Replacing the `INSERT OR REPLACE` line in the above script with the following (note I also unescaped the ampersands here): `$dbh->do("INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', +'Ge 1:1-more &stuff& here ''');");` [download] results in: `$VAR1 = { 'Stuff' => { 'svalue' => 'Ge 1:1-more &stuff& here \'', 'skey' => 'Stuff' } };` [download] The backslash in the output is due to Data::Dumper, BTW, so don't let that confuse you. All in all, I'd recommend two things: Use single-quoted strings for your query strings so that Perl won't interpolate into them. Use the the `q//` operator (see Quote and Quote like Operators) with a terminator other than `'` so you won't have to escape single quotes. For instance (linebreaks added for clarity), for statements that don't change: `$dbh->do( q/INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', 'lit +eral dollar sign: $, literal single quote: '', literal ampersand: &') +;/ );` [download] For statements that DO change, use `->prepare()` and `->bind_param()` instead: `my $sth = $dbh->prepare( 'INSERT OR REPLACE INTO data (skey, svalue) VALUES (?, ?);' ); $sth->bind_param(1, $skey); $sth->bind_param(2, $svalue); $sth->execute();` [download] Don't assemble query strings in Perl using interpolation or string concatenation, this will lead to bugs and/or security risks. ("Little Bobby Tables" and all that.)	[reply] [d/l] [select]