In Perl, how do I properly quote string fragments I am using to build an SQL query?

Question

I've a set of elements in an array which is read from a file.Now i want to use this array value in sql statement in in Claus!for that i need to enclose these string by '(single quote).so I've tried with for loop to do this! is there any way to do this same opp? like any in build functions like qw... code:

open FILE, "<tab_name.txt" or die $!;
my @tab=<FILE>;
chomp(@tab);
@tab=split(",",$tab[0]);#set of like eg:$tab[0]=asc,cdf,sad,casd,aea,aee,asdf 
my @sql_str=();
foreach my $item(@tab){

        $item="'".$item."'";
        push(@sql_str,$item);#add comma
         push(@sql_str,",");
}
pop(@sql_str);#remove lase unwanted comma

i got the desired output like 'asc','cdf','sad','casd','aea','aee','asdf' but is there any way to do this?

You shouldn't use quotation in values for SQL queries. Use placeholders instead. — Oleg V. Volkov, Apr 15 '13 at 10:29
It looks like you are trying to create a dynamic query using an IN clause. If the number of values is fixed, create a template. If the number of variables is not ocnstant, you will have to use the prepare statement and create and execute dynamic sql statment. What db? — jim mcnamara, Apr 15 '13 at 10:32
@jim:oracle db actually,'m trying to access this db via perl using sql plus — Thiyagu ATR, Apr 15 '13 at 10:35
So, you end up with a full sql statement string like "select * from table where fld1 in ('foo', 'bar', 'junk');" ? — jim mcnamara, Apr 15 '13 at 11:04
See also Andy Lester's excellent resource, [Bobby-Tables.com](http://bobby-tables.com/). — Sinan Ünür, Apr 15 '13 at 11:43

TrueY · Answer 1 · 2013-04-15T12:25:01.610

3

Do You need an array, or a string fr the in clasue? I assume a string is proper, so what about this?

my $sql_str = join ",", map { $dbh->quote $_ } split ",", $tab[0];

But Miguel Prz is right, You should use parameter binding instead! This will save Your Oracle server for parsing over and over again the same SQL statements.

Other minor issue. If You use my @tab=<FILE>; it will read the whole file. But in the code only the first line is used. So You could use my $tab = <FILE>; to read only the first line.

edited Apr 15 '13 at 12:25

answered Apr 15 '13 at 11:16

TrueY

7,360
1
41
46

1

If you have a database handle, `map { $dbh->quote($_) }` instead of `map { "'$_'" }` to properly handle quotes inside `$_`. – Stefan Majewsky Apr 15 '13 at 11:19
1

@Stefan Majewsky: Thanks, corrected. – TrueY Apr 15 '13 at 11:30

score 3 · Answer 2 · answered Apr 15 '13 at 14:10

To do this with placeholders (i.e., properly), you need to first gather all the values for the IN clause into a single array (@values) so that you know how many of them there are, then:

my $in_clause = join(', ', ('?') x scalar @values);
my $sql_str = "select field1, field2 from my_table where id in ( $in_clause )";
my $sth = $dbh->prepare($sql_str);
$sth->execute(@values);

(scalar isn't strictly necessary here, but included to make it a little more obvious what's going on for the OP.)

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

This may works:

my $sql_str = join ',' => map { $_ = qq|'$_'|; } @tab;

But, manually building your SQL this way is a bad idea. Parameter binding is the proper way to solve this problem.

edited May 23 '17 at 11:50

Community

1
1

answered Apr 15 '13 at 11:17

Miguel Prz

13,718
29
42

If You use `$_ =` in `map` then it is edited inplace, so the `@tab =` is not needed. – TrueY Apr 15 '13 at 11:22
@TrueY, thanks. Updated my answer with your suggestion – Miguel Prz Apr 15 '13 at 11:23
This `=>` is a kind of tricky, but `,` is enough. The `$_ = ` is not really needed (except You really want to modify `@tab` as well) – TrueY Apr 15 '13 at 12:23
@Miguel:good job dude – Thiyagu ATR Apr 17 '13 at 05:40

In Perl, how do I properly quote string fragments I am using to build an SQL query?

3 Answers3