execute parameters and quoting

am 29.06.2004 14:38:43 von Jan Eden

Hi all,

as far as I know, parameters used with execute() will be quoted correctly. =
Now I have the following code:

foreach (@search_fields) {
my ($value) =3D $q->param($_) =3D~ /([\S ]*)/;
$value =3D '%' . $value . '%';
push @search_values, $value;
}

my $query =3D (...);
=20
my $sth =3D $dbh->prepare($query);
$sth->execute(@search_values);

(...) stands for a lenghty query string, which contains placeholders in the=
order of @search_fields/@search_values.

This works fine, as long as the strings to be searched do not contain singl=
e quotes. Searching for "O'Reilly", for example, returns nothing, while the=
re are several O'Reilly titles in the database.

I tried to include an additional call to quote() in the forearch loop, but =
it did not solve the problem.

Any suggestions? Thanks.

- Jan

--=20
The day Microsoft makes something that doesn't suck is the day they start s=
elling vacuum cleaners.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: execute parameters and quoting

am 29.06.2004 21:33:56 von Christopher Pryce

On Jun 29, 2004, at 7:38 AM, Jan Eden wrote:
>
> as far as I know, parameters used with execute() will be quoted
> correctly.

Correct, AFAIK

> Now I have the following code:
>
> foreach (@search_fields) {
> my ($value) = $q->param($_) =~ /([\S ]*)/;
> $value = '%' . $value . '%';
> push @search_values, $value;
> }
>
> my $query = (...);
>
> my $sth = $dbh->prepare($query);
> $sth->execute(@search_values);
>
> (...) stands for a lenghty query string, which contains placeholders
> in the order of @search_fields/@search_values.
> This works fine, as long as the strings to be searched do not contain
> single quotes. Searching for "O'Reilly", for example, returns nothing,
> while there are several O'Reilly titles in the database.

Maybe you should include ... in your next post. The example I posted
below works fine with single quotes for me using placeholders and
execute().

> I tried to include an additional call to quote() in the forearch loop,
> but it did not solve the problem.

I'm not sure what the regexp in your foreach loop testing for. Are you
trying to make sure that blank fields in your HTML form don't get
included?

Try this :

#!/usr/bin/perl

use strict;
use warnings;

use CGI qw(param);
use DBI;

my $dbh = DBI->connect('dbi:mysql:test;host=localhost', '','');
my @search_fields = qw(title publisher author);

# simulate your HTML query...
param(-name=>'title', -value=>qq(O'Clock) );
param(-name=>'publisher', -value=>qq(O'Reilly) );
# end simulate query

my %search;
foreach ( @search_fields ) {
next unless param( $_ ); # no blank values
$search{ $_ } = '%' . param($_) . '%' ;
}

# usually a radio button that is All Keywords (AND) or Each Keyword (OR)
# Defaults to AND
my $conj = param('conjunction') || 'And';

my $sql = 'Select * from qtest Where ' . ( join(" $conj ", map{ "$_
Like ?" } keys %search ) );

my $sth = $dbh->prepare( $sql );
print $sql, "\n"; # make sure that we have what we think we have.

$sth->execute( values %search );
my $results = $sth->fetchall_arrayref();

print join("\t", @search_fields ), "\n";
foreach (@$results) {
print join("\t", @$_ ), "\n";
}

__END__
CREATE TABLE qtest(
publisher varchar(20),
author varchar(20),
title varchar(20)
);

Insert Into qtest (publisher, author, title) Values('O\'Reilly',
'Thomas O\'Malley', 'Ten O\'Clock Scholar');
Insert Into qtest (publisher, author, title) Values('o\'reilly',
'Larry, Tom and Randal', 'Proramming Perl');

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: execute parameters and quoting

am 29.06.2004 21:33:56 von Christopher Pryce

Re: execute parameters and quoting

am 29.06.2004 23:30:42 von Jan Eden

Hi Christopher,

Christopher Pryce wrote on 29.06.2004:

>On Jun 29, 2004, at 7:38 AM, Jan Eden wrote:
>>
>>as far as I know, parameters used with execute() will be quoted
>>correctly.
>
>Correct, AFAIK
>
>>Now I have the following code:
>>
>>foreach (@search_fields) { my ($value) =3D $q->param($_) =3D~ /([\S
>>]*)/; $value =3D '%' . $value . '%'; push @search_values, $value;
>>}
>>
>>my $query =3D (...);
>>
>>my $sth =3D $dbh->prepare($query); $sth->execute(@search_values);
>>
>>(...) stands for a lenghty query string, which contains
>>placeholders in the order of @search_fields/@search_values. This
>>works fine, as long as the strings to be searched do not contain
>>single quotes. Searching for "O'Reilly", for example, returns
>>nothing, while there are several O'Reilly titles in the database.
>
>Maybe you should include ... in your next post. The example I posted
>below works fine with single quotes for me using placeholders and
>execute().
>
That's really embarrassing: I finally found that the entries in the databas=
e do not contain single quotes, but real apostrophes (=B9). It all works no=
w.

I am really sorry for wasting your time.

>
>I'm not sure what the regexp in your foreach loop testing for. Are
>you trying to make sure that blank fields in your HTML form don't
>get included?

That's something I picked up in "CGI Programming with Perl": User input sho=
uld be considered potentially dangerous, so one should untaint data coming =
from the user before passing it to external programs. Even though the scrip=
t has only read access to the database, it will accept only non-whitespace =
and simple spaces in search strings.

Thanks for your help,

Jan

--=20
Remember: use logout to logout.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: execute parameters and quoting

am 29.06.2004 23:30:42 von Jan Eden