[32920] in Perl-Users-Digest
Perl-Users Digest, Issue: 4198 Volume: 11
daemon@ATHENA.MIT.EDU (Perl-Users Digest)
Sun Apr 20 14:09:33 2014
Date: Sun, 20 Apr 2014 11:09:04 -0700 (PDT)
From: Perl-Users Digest <Perl-Users-Request@ruby.OCE.ORST.EDU>
To: Perl-Users@ruby.OCE.ORST.EDU (Perl-Users Digest)
Perl-Users Digest Sun, 20 Apr 2014 Volume: 11 Number: 4198
Today's topics:
A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <janek_schleicher@yahoo.de>
Re: A question about DBI <netnews@invalid.com>
Re: A question about DBI <lws4art@gmail.com>
Re: A question about DBI <john@castleamber.com>
Digest Administrivia (Last modified: 6 Apr 01) (Perl-Users-Digest Admin)
----------------------------------------------------------------------
Date: Sun, 20 Apr 2014 08:20:15 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: A question about DBI
Message-Id: <0aa23e93-d1e8-4ba5-90d1-4fbc1c4ec608@googlegroups.com>
Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
In a pl file, I had assign variable $ny = 2011 , I wanna get something from MySQL by DBI
$sth = $dbh->prepare('select site,ip,time,files from sj where time like "2011%" order by id desc');
but I wanna use $ny instead of 2011, I have try :
$sth = $dbh->prepare('select site,ip,time,files from sj where time like "$ny%" order by id desc');
but it's doesn't work :(
How can I do ?
Thanks!
------------------------------
Date: Sun, 20 Apr 2014 17:37:11 +0200
From: Janek Schleicher <janek_schleicher@yahoo.de>
Subject: Re: A question about DBI
Message-Id: <bri7tpFhjhiU1@mid.individual.net>
Am 20.04.2014 17:20, schrieb Martin Lee:
> Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>
> In a pl file, I had assign variable $ny = 2011 , I wanna get something from MySQL by DBI
>
> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "2011%" order by id desc');
>
> but I wanna use $ny instead of 2011, I have try :
>
> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "$ny%" order by id desc');
In singlequoted strings variables are not interpolated.
Something like
$sth = $dbh->prepare(qq{select site,ip,time,files from sj where time
like "$ny%" order by id desc})
will fix it shorttime.
See perldoc perlop for details in case.
In the long run, you will always get trouble when you try to interpolate
variables into SQL queries.
So I would suggest to work with placeholders:
my $ny = '2011%'; # note that the wildcard characters like % need inside
the variable now
$sth = $dbh->prepare(qq{select site,ip,time,files from sj where time
like ? order by id desc})
# and later then e.g.
$sth->execute($ny);
So DBI does all the interpolation, escaping and hopefully also security
stuff for you and in addition it can caches simiilar query structures
that might increases performance later.
See perldoc DBI if you have questions about placeholders in DBI.
Greetings,
Janek Schleicher
------------------------------
Date: Sun, 20 Apr 2014 08:38:46 -0700
From: HASM <netnews@invalid.com>
Subject: Re: A question about DBI
Message-Id: <87oazwko3d.fsf@127.0.0.1>
Martin Lee <liyaoxinchifan@gmail.com> writes:
> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "$ny%" order by id desc');
> but it's doesn't work :(
Like this:
$sth = $dbh->prepare('select site,ip,time,files from sj where time like "'.$ny.'%" order by id desc');
or, probably "better":
$sth = $dbh->prepare(qq{select site,ip,time,files from sj where time like "$ny%" order by id desc});
(or maybe you should bind the value instead, if $ny comes in unchecked.)
-- HASM
------------------------------
Date: Sun, 20 Apr 2014 11:45:05 -0400
From: "Jonathan N. Little" <lws4art@gmail.com>
Subject: Re: A question about DBI
Message-Id: <lj0q23$ej1$1@dont-email.me>
Martin Lee wrote:
> Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>
> In a pl file, I had assign variable $ny = 2011 , I wanna get something from MySQL by DBI
>
> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "2011%" order by id desc');
>
> but I wanna use $ny instead of 2011, I have try :
>
> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "$ny%" order by id desc');
^
Single quoted strings will not expand variables you need double quotes.
one option is to use qq()
$sth = $dbh->prepare(qq(select site,ip,time,files from sj where time
like "$ny%" order by id desc));
--
Take care,
Jonathan
-------------------
LITTLE WORKS STUDIO
http://www.LittleWorksStudio.com
------------------------------
Date: Sun, 20 Apr 2014 12:08:38 -0500
From: John Bokma <john@castleamber.com>
Subject: Re: A question about DBI
Message-Id: <87fvl8szc9.fsf@castleamber.com>
"Jonathan N. Little" <lws4art@gmail.com> writes:
> Martin Lee wrote:
>> Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>>
>> In a pl file, I had assign variable $ny = 2011 , I wanna get something from MySQL by DBI
>>
>> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "2011%" order by id desc');
>>
>> but I wanna use $ny instead of 2011, I have try :
>>
>> $sth = $dbh->prepare('select site,ip,time,files from sj where time like "$ny%" order by id desc');
> ^
> Single quoted strings will not expand variables you need double
> quotes. one option is to use qq()
>
> $sth = $dbh->prepare(qq(select site,ip,time,files from sj where time
> like "$ny%" order by id desc));
If (and only if) $ny can't never contain user input you could also do:
$sth = $dbh->prepare( <<"END_SQL" );
SELECT site, ip, time, files
FROM sj
WHERE time LIKE '$ny%'
ORDER BY id DEC
END_SQL
If $ny can be entered by the user /always/ use place holders:
$sth = $dbh->prepare( <<"END_SQL" );
SELECT site, ip, time, files
FROM sj
WHERE time LIKE ?
ORDER BY id DEC
END_SQL
$sth->execute( "$ny%" );
Since ny and 2011 seem to suggest a year... store numeric data as
numbers, /not/ as strings. And store dates as .... dates. It's much
easier to do queries that way.
(I now and then have to maintain code with an extremely badly designed
database that stores months numbers as string, which messes up their
order (no leading zero if < 10))
--
John Bokma j3b
Blog: http://johnbokma.com/ Perl Consultancy: http://castleamber.com/
Perl for books: http://johnbokma.com/perl/help-in-exchange-for-books.html
------------------------------
Date: 6 Apr 2001 21:33:47 GMT (Last modified)
From: Perl-Users-Request@ruby.oce.orst.edu (Perl-Users-Digest Admin)
Subject: Digest Administrivia (Last modified: 6 Apr 01)
Message-Id: <null>
Administrivia:
To submit articles to comp.lang.perl.announce, send your article to
clpa@perl.com.
Back issues are available via anonymous ftp from
ftp://cil-www.oce.orst.edu/pub/perl/old-digests.
#For other requests pertaining to the digest, send mail to
#perl-users-request@ruby.oce.orst.edu. Do not waste your time or mine
#sending perl questions to the -request address, I don't have time to
#answer them even if I did know the answer.
------------------------------
End of Perl-Users Digest V11 Issue 4198
***************************************
