in reply to Re: Creating variables while using 'strict'
in thread Creating variables while using 'strict'

Always remember to quote data or else use placeholders! ;) 

#### added call to $dbh->quote

my $col_spec = join ',', sort keys %data ;
my $val_spec = join ',',
               map  { $dbh->quote( $data{$_} ) }   ### here
               sort keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

#### or with placeholders:

my $col_spec = join ',', sort keys %data;
my $val_spec = join ',', ('?') x keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

$dbh->prepare($sql)
    ->execute( map { $data{$_} } sort keys %data );

[download]
Also it might be worth mentioning that the keys of %data should be validated before they are trusted in this code. Cheers.

blokhead

Replies are listed 'Best First'.
Re: Re: Re: Creating variables while using 'strict'
by bart (Canon) on Jan 06, 2003 at 11:29 UTC
    Actually, there's no real need to sort the keys. And there's definitely no need to sort them more than once: just create an array with them. A hash slice makes the map unnecessary. Here's a modified version of your code: 
    #### or with placeholders:
my $col_spec = join ',', keys %data;
my $val_spec = join ',', ('?') x keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

$dbh->prepare($sql)->execute(@data{keys %data});

    [download]
    In case you want to use the perepared statement more than once, there's no ganratee that keys() will return the keys in the same order over different hashes even though they have the same keys: 
    #### or with placeholders:
my(@col_names, $sth);
{
    local $" = ',';
    @col_names   = keys %data;
    my $val_spec = join ',', ('?') x @col_names;
    my $sql      = "insert into my_table (@col_names) values ($val_spe
+c)";
    $sth = $dbh->prepare($sql);
}

    [download]
    And much later (just make sure $sth and @col_names are still in scope): 
    $sth->execute(@data{@col_names});

    [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom