Re: perl and postgresql

On Tue, 18 Mar 2003, Ville Jungman wrote:

> > The point is that he and you need to use placeholders to avoid sql
> > injection. Just consider example below: what if variable prod is set to
> > $prod = '10; DROP DATABASE x'
>
> Doesn't work if $prod is checked elsewhere.
>
> It's easier if you can call sql-commands just in the same way that you do
> with sql-prompt. For example

Yep, of course. But not safest. There's additional reasons, why one would
consider using DBI. And actually it's easier to use DBI with plholders
than without.

Some helpers for DBI: (for DBI you need DBI and DBD::Pg which are
available from CPAN).

use DBI;

# returns a reference to array (the rows) of references to hashes (the
# cols).
sub my_select {
    my $dbh = shift;
    my $stmt = shift;

    return $dbh->selectall_arrayref($stmt, { Slice => { } }, @_);
}

# returns number of affected rows, undef on error, -1 on unknown.
sub my_do {
    my $dbh = shift;
    my $stmt = shift;

    return $dbh->do($stmt, undef, @_);
}

my $dbh = DBI->connect(
    "dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty",
    "$username", "$password");

my $retval = my_do($dbh,
    'delete from users where username = ?', $user);

print "Total of $retval rows deleted\n";

$retval = my_select($dbh, q{
    select username,realname
        from users
        where domain = ?
}, $domain);

# result like
# $retval = [
#    { "username" => "bill", "realname" =>"BillG" },
#    { "username" => "linus", "realname" => "Linus T." }
# ];

print "Users for domain $domain:\n";
foreach (@$retval) {
    print $_->{username} . ' '. $_->{realname} . "\n";
}

print "Total of ", scalar(@$retval), " users\n";


--
Antti Haapala