Perl/SQLite - How do I select / update a row with the prepare method?

Question

I have the following code

my $db = DBI->connect(
     "dbi:SQLite:data.db", "", "",
     { RaiseError => 1, AutoCommit => 1, PrintError => 0 }
);
my $row = $db->selectall_arrayref(
     "SELECT * FROM something WHERE name=\'$hash->{name}\'");
print Dumper $row;

How do I do the same with my $sql = $db->prepare("......"); $sql->execute($hash->{name}); so that it's escaped correctly and I have the selected data in $row?

Answer 1

You seem to be looking for information on bind values:

my $row = $db->selectall_arrayref(
    "SELECT * FROM something WHERE name=?",
    {},
    $hash->{name}
);

This prepares and executes in one go.

You can also prepare and execute separately:

my $sth = $db->prepare("SELECT * FROM something WHERE name=?");

later:

$sth->execute($hash->{name});
my $rows_ref = $sth->fetchall_arrayref;

You should avoid using SELECT * and read the section on Statement Handle Methods in perldoc DBI.