A question about DBI

[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');

but it's doesn't work

How can I do ?

Thanks!

 

[Janek Schleicher]

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

 

[HASM]

$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

 

[Jonathan N. Little]

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));

 

[John Bokma]

^
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))

 

[Rainer Weikusat]

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').

 

[John Bokma]

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.

 

[Martin Lee]

在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶37分11秒，Janek Schleicher写é“：

Am 20.04.2014 17:20, schrieb Martin Lee:




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

Thanks !

 

[Martin Lee]

在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶38分46秒，HASM写é“：

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

Thank you very much!

 

[Martin Lee]

在 2014å¹´4月21日星期一UTC+8上åˆ1æ—¶08分38秒，John Bokma写é“：

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

Thank you! your way is look like
print <<END;
....
....
END
It's more convenient~

 

[Martin Lee]

在 2014å¹´4月21日星期一UTC+8上åˆ3æ—¶06分02秒，Rainer Weikusat写é“：

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').

Thank you !
I have try
$sth = $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

 

[Martin Lee]

在 2014å¹´4月21日星期一UTC+8上åˆ3æ—¶29分04秒，Henry Law写é“：

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

Sorry about that, the pl file can run successfully in my wrong way, but MySQL return nothing, my english is poor, and I'm worried at that time, so I described my question very crude, today I found many people help me to solvemy question, I feel very happy， I think I can do better in next time

 

[Martin Lee]

在 2014å¹´4月21日星期一UTC+8上åˆ8æ—¶08分31秒，John Bokma写é“：

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

Sorry, I can do better in next time : ) thank you !

 

[Martin Lee]

在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶45分05秒，Jonathan N. Little写é“：

^

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

Thank you! I try qq() that run successful