Re^3: DBD::SQLite select fails

[AppleFritter]

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

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

results in:

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

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:

1. Use single-quoted strings for your query strings so that Perl won't interpolate into them.
2. 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: &')
+;/
);

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

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