Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Quick DBI do question

by Anonymous Monk
on Feb 13, 2009 at 15:54 UTC ( #743619=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone

Are there differences between the following two ways of inserting MySQL data?
# 1a $dbh->do(qq~INSERT INTO scores VALUES($userid, $score, NOW())~); #1b $dbh->do(qq~INSERT INTO scores VALUES(?, ?, NOW())~, undef, $userid, $ +score); #2a $dbh->do(qq~UPDATE scores SET score=? WHERE userid=?)~, undef, $new_sc +ore, $userid); #2b $dbh->do(qq~UPDATE scores SET score=$new_score WHERE userid=$userid)~) +;

Is one method preferrable to the other?

TIA :)

Comment on Quick DBI do question
Download Code
Re: Quick DBI do question
by ForgotPasswordAgain (Deacon) on Feb 13, 2009 at 15:56 UTC
    Using placeholders (?) is absolutely preferable.
Re: Quick DBI do question
by kennethk (Monsignor) on Feb 13, 2009 at 16:00 UTC

    Yes. The methods you use in 1a and 2b are potentially very dangerous - see SQL_injection. It's better (particularly if you plan to perform a task repeatedly) to use place holders, both from a security and efficiency stand point. For more details on placeholder technology, check out placeholders.

    As well, in place of a single do statement, it's probably a good idea to parse it out into a prepare and execute set, possibly with prepare statements, as described in DBI.

      As well, in place of a single do statement, it's probably a good idea to parse it out into a prepare and execute set, possibly with prepare statements...

      I thought the point of do was to avoid having to explicitly prepare and execute a statement handle. If you're performing an action where there are no rows returned, there is no need for that.

        It is, but given the nature of the question, it's reasonable to think that the OP hasn't considered which is correct for [his|her] situation. But yes, I should have said "good idea to consider parsing it".

        I thought the point of do was to avoid having to explicitly prepare and execute a statement handle. If you're performing an action where there are no rows returned, there is no need for that.
        The point of the prepare/execute idiom is to give the database a chance to parse the SQL just once, and re-use some work it's done already. This is often a huge efficiency gain, though obviously, only in the case where you're going to be running the SQL statement multiple times. Myself, I always do a prepare and execute out of habit... it's an extra line of code, but it makes refactoring it easier if the needs change.

        (By the way, if you do need to do a lot of multi-row inserts, you should consider avoiding the INSERT statement at all, and look into using a bulk-loader instead -- in Postgresql it's COPY.)

      Do you need to use "quote" if placeholders are used? i.e.
      $dbh->quote($username);
Re: Quick DBI do question
by Anonymous Monk on Feb 14, 2009 at 01:16 UTC
    Great thanks to all the replies. I had no idea using placeholders helps in preventing SQL-injection. Will always use that method from now on.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://743619]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (12)
As of 2014-10-23 13:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (125 votes), past polls