Placeholders, as frequently mentioned, are your friends:

my $sth=$dbh->prepare("INSERT INTO tbl VALUES(?, ?, ?)");
$sth->execute(param('Col1'), param('Col2'), param('Col3'));
[download]

You'll want scalar there, lest you be burned the day someone gives you none or two parameters for Col1 and friends.

my $result = $dbh->do("INSERT INTO tbl VALUES(?,?,?)", undef,
  map scalar param($_), qw(Col1 Col2 Col3));
[download]

-- Randal L. Schwartz, Perl hacker

I like to use prepare and all that gunk, even for INSERTs, but you can still use placeholders with do :

$dbh->do("INSERT INTO tbl VALUES (?,?,?)", undef, @params{qw(Col1 Col2
+ Col3)});
[download]

This gives you quoting of the values (nice from a security point of view). And the hash slice is just icing on the cake.HTH!

perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'
[download]

If you do that:

my $sth = $dbh->prepare("INSERT INTO tbl VALUES (?,?,?)");
$sth->execute(@values) or die $dbh->errstr;
[download]

you should win twice:

1. You get your values quoted.
2. You can use an array.

Hope that helps.

alex pleiner <alex@zeitform.de>
zeitform Internet Dienste

Hmm, that's particularly good in my case because when I'm not making my query with CGI params I'm making it with the output of a SELECT query, and that output is in the form of an arrayref. Using an array avoids the intermediate step of breaking out the array into its elements.

§ George Sherston

my %field;
for ( param() ) {
  $field{$_} = param($_);
}
[download]

cLive ;-)

And just to be on the safe side

my %field;
for ( qw/foo bar baz/ )
{
  $field{ $_ } = param( $_ );
}
[download]

This way we are explicitly allowing the things we want, and can even build in error handling if the things we need aren't there.

I have one comment that hasn't been addressed yet, is slightly OT, but still important.

In your SQL query you may want to explicitly specify the columns you are inserting into. This has a large benefit: if you add a column to the table or the column ordering changes, your code is less likely to break. A good rule of thumb I use is to:

• never assume the order of columns in an SQL query
• always explicitly define the columns

I've found this to be especially helpful in SELECT and INSERT -type statements.

Here's an example that demonstrates what I mean and answers your question:

$dbh->do(
  'INSERT INTO table (Col1, Col2, Col3) VALUES (?, ?, ?)',
  {}, 
  map scalar param($_), qw(Col1 Col2 Col3),
);
[download]

I began doing this after thinking about the issues raised in Topics in Perl Programming: Table-Mutation Tolerant Database Fetches with DBI, and realizing the way I was doing SQL does not lend itself to change very well.

use CGI;
my $cgi = new CGI;
my %MYDATA = $cgi->Vars;
[download]