map for x
In Perl, the map function can simplify the code required to SQL quote a string:
#!/usr/bin/perl -w use strict; #use Data::Dumper; my @list = ( qw{abe baker}, "can't" ); my $result = sqlify_with_map( \@list ); #print Dumper \@list; print $result, "\n"; $result = sqlify_with_for( \@list ); #print Dumper \@list; print $result, "\n"; sub sqlify_with_for { my $list_ref = shift; my $result; for my $i ( 0 .. $#$list_ref ) { my $item = $list_ref->[$i]; $item =~ s/'/\\'/g; $result .= "'$item'"; if ( $i != $#$list_ref ) { $result .= ','; } } return $result; } sub sqlify_with_map { my $list_ref = shift; return join ',', map { my $item = $_; $item =~ s/'/\\'/g; "'$item'"; } @$list_ref; }
The $item variable in both cases avoids modification of the original list elements. The map allows the escaped and quoted items to be passed as a list to join, while the for loop requires more code to decide when not to add a joining comma (leading to many false checks in a loop, which is usually not a Good Thing). Another option: always add a comma, then strip off the final comma after the for loop. But why bother, with map available?
SQL placeholders for DBI are another solution, though with a list of variable size, a corresponding number of ? must be listed in the prepared statement. Here, the x operator makes the most sense:
my $sth = $dbh->prepare( $prefix . join( ',', ('?') x @list ) . $suffix ); my $rv = $sth->execute(@list);