[32921] in Perl-Users-Digest
Perl-Users Digest, Issue: 4199 Volume: 11
daemon@ATHENA.MIT.EDU (Perl-Users Digest)
Tue Apr 22 00:09:30 2014
Date: Mon, 21 Apr 2014 21:09:03 -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 Mon, 21 Apr 2014 Volume: 11 Number: 4199
Today's topics:
Re: A question about DBI <rweikusat@mobileactivedefense.com>
Re: A question about DBI <news@lawshouse.org>
Re: A question about DBI <john@castleamber.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <liyaoxinchifan@gmail.com>
Re: A question about DBI <news@lawshouse.org>
Digest Administrivia (Last modified: 6 Apr 01) (Perl-Users-Digest Admin)
----------------------------------------------------------------------
Date: Sun, 20 Apr 2014 20:06:02 +0100
From: Rainer Weikusat <rweikusat@mobileactivedefense.com>
Subject: Re: A question about DBI
Message-Id: <87d2gb2545.fsf@sable.mobileactivedefense.com>
Martin Lee <liyaoxinchifan@gmail.com> writes:
> 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');
Do not ever do that except if there is no other choice but to use string
interpolation. Always use proper parametrized statements instead, ie,
(as already posted, although with less emphasis):
$sth = $dbh->prepare('select site,ip,time,files from sj where time like ? order by id desc');
$sth->execute($ny);
Why do you want to worry about the correct way to quote something like
this when the database driver already does that for you? At best, that's
more work than necessary (and likely, also less efficient), at worst,
some subtle or not-so-subtle mistakes enables someone who can supply
input data which ends up in your queries to run arbitrary SQL-statements
(so-called 'SQL injection').
------------------------------
Date: Sun, 20 Apr 2014 20:29:04 +0100
From: Henry Law <news@lawshouse.org>
Subject: Re: A question about DBI
Message-Id: <6eOdnVFY94eVvcnOnZ2dnUVZ8mqdnZ2d@giganews.com>
On 20/04/14 16:20, Martin Lee wrote:
> 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
Others have given you good answers to your question. It remains for me
to ask you, please, double please, _don't_ say "it doesn't work". It
tells us nothing about what your problem actually is. In this case, of
course, the experienced members of the group could work it out, but
often it's not possible.
Say something like "I expected to get 'foo' but instead I got 'bar'; or
"it fails with error message 'mumble'" ... you get the idea.
--
Henry Law Manchester, England
------------------------------
Date: Sun, 20 Apr 2014 19:08:31 -0500
From: John Bokma <john@castleamber.com>
Subject: Re: A question about DBI
Message-Id: <871twrwnls.fsf@castleamber.com>
Henry Law <news@lawshouse.org> writes:
> On 20/04/14 16:20, Martin Lee wrote:
>> 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
>
> Others have given you good answers to your question. It remains for me
> to ask you, please, double please, _don't_ say "it doesn't work". It
> tells us nothing about what your problem actually is. In this case,
> of course, the experienced members of the group could work it out, but
> often it's not possible.
>
> Say something like "I expected to get 'foo' but instead I got 'bar';
> or "it fails with error message 'mumble'" ... you get the idea.
Additionally, printing the query would've made clear what was going
on. "It doesn't work" can often be fixed by adding a few print (or say)
statements.
--
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: Sun, 20 Apr 2014 17:49:22 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <9d72446c-d7b5-40a4-a0e1-abc33d8eea26@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8820=E6=97=A5=E6=98=9F=E6=9C=9F=E6=97=A5UTC+=
8=E4=B8=8B=E5=8D=8811=E6=97=B637=E5=88=8611=E7=A7=92=EF=BC=8CJanek Schleich=
er=E5=86=99=E9=81=93=EF=BC=9A
> Am 20.04.2014 17:20, schrieb Martin Lee:
>=20
> > Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>=20
> >
>=20
> > In a pl file, I had assign variable $ny =3D 2011 , I wanna get somethin=
g from MySQL by DBI
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "2011%" order by id desc');
>=20
> >
>=20
> > but I wanna use $ny instead of 2011, I have try :
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc');
>=20
>=20
>=20
> In singlequoted strings variables are not interpolated.
>=20
>=20
>=20
> Something like
>=20
> $sth =3D $dbh->prepare(qq{select site,ip,time,files from sj where time=20
>=20
> like "$ny%" order by id desc})
>=20
> will fix it shorttime.
>=20
> See perldoc perlop for details in case.
>=20
>=20
>=20
> In the long run, you will always get trouble when you try to interpolate=
=20
>=20
> variables into SQL queries.
>=20
> So I would suggest to work with placeholders:
>=20
>=20
>=20
> my $ny =3D '2011%'; # note that the wildcard characters like % need insid=
e=20
>=20
> the variable now
>=20
> $sth =3D $dbh->prepare(qq{select site,ip,time,files from sj where time=20
>=20
> like ? order by id desc})
>=20
>=20
>=20
> # and later then e.g.
>=20
> $sth->execute($ny);
>=20
>=20
>=20
> So DBI does all the interpolation, escaping and hopefully also security=
=20
>=20
> stuff for you and in addition it can caches simiilar query structures=20
>=20
> that might increases performance later.
>=20
> See perldoc DBI if you have questions about placeholders in DBI.
>=20
>=20
>=20
>=20
>=20
> Greetings,
>=20
> Janek Schleicher
Thanks !
------------------------------
Date: Sun, 20 Apr 2014 17:51:50 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <35ea7d73-9390-40d1-bc63-37595bbcb4e5@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8820=E6=97=A5=E6=98=9F=E6=9C=9F=E6=97=A5UTC+=
8=E4=B8=8B=E5=8D=8811=E6=97=B638=E5=88=8646=E7=A7=92=EF=BC=8CHASM=E5=86=99=
=E9=81=93=EF=BC=9A
> Martin Lee <liyaoxinchifan@gmail.com> writes:
>=20
>=20
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc');
>=20
> > but it's doesn't work :(=20
>=20
>=20
>=20
> Like this:
>=20
>=20
>=20
> $sth =3D $dbh->prepare('select site,ip,time,files from sj where time like=
"'.$ny.'%" order by id desc');
>=20
>=20
>=20
> or, probably "better":
>=20
>=20
>=20
> $sth =3D $dbh->prepare(qq{select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc});
>=20
>=20
>=20
> (or maybe you should bind the value instead, if $ny comes in unchecked.)
>=20
>=20
>=20
> -- HASM
Thank you very much!
------------------------------
Date: Sun, 20 Apr 2014 17:57:29 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <16881c6e-6a6b-4e4b-b698-559bb51a9d1f@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8821=E6=97=A5=E6=98=9F=E6=9C=9F=E4=B8=80UTC+=
8=E4=B8=8A=E5=8D=881=E6=97=B608=E5=88=8638=E7=A7=92=EF=BC=8CJohn Bokma=E5=
=86=99=E9=81=93=EF=BC=9A
> "Jonathan N. Little" <lws4art@gmail.com> writes:
>=20
>=20
>=20
> > Martin Lee wrote:
>=20
> >> Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>=20
> >>
>=20
> >> In a pl file, I had assign variable $ny =3D 2011 , I wanna get somethi=
ng from MySQL by DBI
>=20
> >>
>=20
> >> $sth =3D $dbh->prepare('select site,ip,time,files from sj where time l=
ike "2011%" order by id desc');
>=20
> >>
>=20
> >> but I wanna use $ny instead of 2011, I have try :
>=20
> >>
>=20
> >> $sth =3D $dbh->prepare('select site,ip,time,files from sj where time l=
ike "$ny%" order by id desc');
>=20
> > ^
>=20
> > Single quoted strings will not expand variables you need double
>=20
> > quotes. one option is to use qq()
>=20
> >
>=20
> > $sth =3D $dbh->prepare(qq(select site,ip,time,files from sj where time
>=20
> > like "$ny%" order by id desc));
>=20
>=20
>=20
> If (and only if) $ny can't never contain user input you could also do:
>=20
>=20
>=20
> $sth =3D $dbh->prepare( <<"END_SQL" );
>=20
> SELECT site, ip, time, files
>=20
> FROM sj
>=20
> WHERE time LIKE '$ny%'
>=20
> ORDER BY id DEC
>=20
> END_SQL
>=20
>=20
>=20
> If $ny can be entered by the user /always/ use place holders:
>=20
>=20
>=20
> $sth =3D $dbh->prepare( <<"END_SQL" );
>=20
> SELECT site, ip, time, files
>=20
> FROM sj
>=20
> WHERE time LIKE ?
>=20
> ORDER BY id DEC
>=20
> END_SQL
>=20
>=20
>=20
> $sth->execute( "$ny%" );
>=20
>=20
>=20
>=20
>=20
> Since ny and 2011 seem to suggest a year... store numeric data as
>=20
> numbers, /not/ as strings. And store dates as .... dates. It's much
>=20
> easier to do queries that way.
>=20
>=20
>=20
> (I now and then have to maintain code with an extremely badly designed
>=20
> database that stores months numbers as string, which messes up their
>=20
> order (no leading zero if < 10))
>=20
>=20
>=20
>=20
>=20
> --=20
>=20
> John Bokma =
j3b
>=20
>=20
>=20
> Blog: http://johnbokma.com/ Perl Consultancy: http://castleamber.c=
om/
>=20
> Perl for books: http://johnbokma.com/perl/help-in-exchange-for-books.h=
tml
Thank you! your way is look like=20
print <<END;
...
...
END
It's more convenient~
------------------------------
Date: Sun, 20 Apr 2014 18:05:34 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <ab96d19a-28cb-4964-a99c-394e7f409bd6@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8821=E6=97=A5=E6=98=9F=E6=9C=9F=E4=B8=80UTC+=
8=E4=B8=8A=E5=8D=883=E6=97=B606=E5=88=8602=E7=A7=92=EF=BC=8CRainer Weikusat=
=E5=86=99=E9=81=93=EF=BC=9A
> Martin Lee <liyaoxinchifan@gmail.com> writes:
>=20
> > Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>=20
> >
>=20
> > In a pl file, I had assign variable $ny =3D 2011 , I wanna get somethin=
g from MySQL by DBI
>=20
> > =20
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "2011%" order by id desc');
>=20
> >
>=20
> > but I wanna use $ny instead of 2011, I have try :
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc');
>=20
>=20
>=20
> Do not ever do that except if there is no other choice but to use string
>=20
> interpolation. Always use proper parametrized statements instead, ie,
>=20
> (as already posted, although with less emphasis):
>=20
>=20
>=20
> $sth =3D $dbh->prepare('select site,ip,time,files from sj where time like=
? order by id desc');
>=20
> $sth->execute($ny);
>=20
>=20
>=20
> Why do you want to worry about the correct way to quote something like
>=20
> this when the database driver already does that for you? At best, that's
>=20
> more work than necessary (and likely, also less efficient), at worst,
>=20
> some subtle or not-so-subtle mistakes enables someone who can supply
>=20
> input data which ends up in your queries to run arbitrary SQL-statements
>=20
> (so-called 'SQL injection').
Thank you !=20
I have try=20
$sth =3D $dbh->prepare('select site,ip,time,files from sj where time like ?=
order by id desc');
$sth->bind_param(1, $ny);
it's a wrong way too, Thank you to teach me the right way :)
------------------------------
Date: Sun, 20 Apr 2014 18:16:44 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <ba105fc4-6b6f-4c27-9686-8b238319caef@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8821=E6=97=A5=E6=98=9F=E6=9C=9F=E4=B8=80UTC+=
8=E4=B8=8A=E5=8D=883=E6=97=B629=E5=88=8604=E7=A7=92=EF=BC=8CHenry Law=E5=86=
=99=E9=81=93=EF=BC=9A
> On 20/04/14 16:20, Martin Lee wrote:
>=20
> > I have try :
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc');
>=20
> >
>=20
> > but it's doesn't work
>=20
>=20
>=20
> Others have given you good answers to your question. It remains for me=20
>=20
> to ask you, please, double please, _don't_ say "it doesn't work". It=20
>=20
> tells us nothing about what your problem actually is. In this case, of=
=20
>=20
> course, the experienced members of the group could work it out, but=20
>=20
> often it's not possible.
>=20
>=20
>=20
> Say something like "I expected to get 'foo' but instead I got 'bar'; or=
=20
>=20
> "it fails with error message 'mumble'" ... you get the idea.
>=20
>=20
>=20
> --=20
>=20
>=20
>=20
> Henry Law Manchester, England
Sorry about that, the pl file can run successfully in my wrong way, but MyS=
QL return nothing, my english is poor, and I'm worried at that time, so I d=
escribed my question very crude, today I found many people help me to solve=
my question, I feel very happy=EF=BC=8C I think I can do better in next ti=
me :)
------------------------------
Date: Sun, 20 Apr 2014 18:20:46 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <7135e344-5444-477e-a1b0-e627827bf2fe@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8821=E6=97=A5=E6=98=9F=E6=9C=9F=E4=B8=80UTC+=
8=E4=B8=8A=E5=8D=888=E6=97=B608=E5=88=8631=E7=A7=92=EF=BC=8CJohn Bokma=E5=
=86=99=E9=81=93=EF=BC=9A
> Henry Law <news@lawshouse.org> writes:
>=20
>=20
>=20
> > On 20/04/14 16:20, Martin Lee wrote:
>=20
> >> I have try :
>=20
> >>
>=20
> >> $sth =3D $dbh->prepare('select site,ip,time,files from sj where time l=
ike "$ny%" order by id desc');
>=20
> >>
>=20
> >> but it's doesn't work
>=20
> >
>=20
> > Others have given you good answers to your question. It remains for me
>=20
> > to ask you, please, double please, _don't_ say "it doesn't work". It
>=20
> > tells us nothing about what your problem actually is. In this case,
>=20
> > of course, the experienced members of the group could work it out, but
>=20
> > often it's not possible.
>=20
> >
>=20
> > Say something like "I expected to get 'foo' but instead I got 'bar';
>=20
> > or "it fails with error message 'mumble'" ... you get the idea.
>=20
>=20
>=20
> Additionally, printing the query would've made clear what was going
>=20
> on. "It doesn't work" can often be fixed by adding a few print (or say)
>=20
> statements.
>=20
>=20
>=20
> --=20
>=20
> John Bokma =
j3b
>=20
>=20
>=20
> Blog: http://johnbokma.com/ Perl Consultancy: http://castleamber.c=
om/
>=20
> Perl for books: http://johnbokma.com/perl/help-in-exchange-for-books.h=
tml
Sorry, I can do better in next time : ) thank you !
------------------------------
Date: Sun, 20 Apr 2014 18:38:22 -0700 (PDT)
From: Martin Lee <liyaoxinchifan@gmail.com>
Subject: Re: A question about DBI
Message-Id: <d4617fd8-e0db-496b-8bc1-8e395d1ccc52@googlegroups.com>
=E5=9C=A8 2014=E5=B9=B44=E6=9C=8820=E6=97=A5=E6=98=9F=E6=9C=9F=E6=97=A5UTC+=
8=E4=B8=8B=E5=8D=8811=E6=97=B645=E5=88=8605=E7=A7=92=EF=BC=8CJonathan N. Li=
ttle=E5=86=99=E9=81=93=EF=BC=9A
> Martin Lee wrote:
>=20
> > Hello everyone, I'm a tiro in Perl. Today I have a question about DBI:
>=20
> >
>=20
> > In a pl file, I had assign variable $ny =3D 2011 , I wanna get somethin=
g from MySQL by DBI
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "2011%" order by id desc');
>=20
> >
>=20
> > but I wanna use $ny instead of 2011, I have try :
>=20
> >
>=20
> > $sth =3D $dbh->prepare('select site,ip,time,files from sj where time li=
ke "$ny%" order by id desc');
>=20
> ^
>=20
> Single quoted strings will not expand variables you need double quotes.=
=20
>=20
> one option is to use qq()
>=20
>=20
>=20
> $sth =3D $dbh->prepare(qq(select site,ip,time,files from sj where time=20
>=20
> like "$ny%" order by id desc));
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> --=20
>=20
> Take care,
>=20
>=20
>=20
> Jonathan
>=20
> -------------------
>=20
> LITTLE WORKS STUDIO
>=20
> http://www.LittleWorksStudio.com
Thank you! I try qq() that run successful :)
------------------------------
Date: Mon, 21 Apr 2014 11:31:41 +0100
From: Henry Law <news@lawshouse.org>
Subject: Re: A question about DBI
Message-Id: <j8idnS1QuocLbsnOnZ2dnUVZ8jmdnZ2d@giganews.com>
On 21/04/14 02:16, Martin Lee wrote:
> my english is poor,
Your English is good enough. I can't speak a single word of your
language ...
today I found many people help me to solve my question, I feel very happy，
Excellent! Come again.
--
Henry Law Manchester, England
------------------------------
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 4199
***************************************
