A question about DBI

Discussion in 'Perl Misc' started by Martin Lee, Apr 20, 2014.

  1. Martin Lee

    Martin Lee Guest

    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!
     
    Martin Lee, Apr 20, 2014
    #1
    1. Advertisements

  2. 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
     
    Janek Schleicher, Apr 20, 2014
    #2
    1. Advertisements

  3. Martin Lee

    HASM Guest

    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
     
    HASM, Apr 20, 2014
    #3
  4. ^
    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));
     
    Jonathan N. Little, Apr 20, 2014
    #4
  5. Martin Lee

    John Bokma Guest

    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, Apr 20, 2014
    #5
  6. 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').
     
    Rainer Weikusat, Apr 20, 2014
    #6
  7. Martin Lee

    John Bokma Guest

    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, Apr 21, 2014
    #7
  8. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶37分11秒,Janek Schleicher写é“:
    Thanks !
     
    Martin Lee, Apr 21, 2014
    #8
  9. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶38分46秒,HASM写é“:
    Thank you very much!
     
    Martin Lee, Apr 21, 2014
    #9
  10. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月21日星期一UTC+8上åˆ1æ—¶08分38秒,John Bokma写é“:
    Thank you! your way is look like
    print <<END;
    ....
    ....
    END
    It's more convenient~
     
    Martin Lee, Apr 21, 2014
    #10
  11. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月21日星期一UTC+8上åˆ3æ—¶06分02秒,Rainer Weikusat写é“:
    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, Apr 21, 2014
    #11
  12. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月21日星期一UTC+8上åˆ3æ—¶29分04秒,Henry Law写é“:
    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, Apr 21, 2014
    #12
  13. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月21日星期一UTC+8上åˆ8æ—¶08分31秒,John Bokma写é“:
    Sorry, I can do better in next time : ) thank you !
     
    Martin Lee, Apr 21, 2014
    #13
  14. Martin Lee

    Martin Lee Guest

    在 2014å¹´4月20日星期日UTC+8下åˆ11æ—¶45分05秒,Jonathan N. Little写é“:
    Thank you! I try qq() that run successful :)
     
    Martin Lee, Apr 21, 2014
    #14
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.