Quick DBI do question

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)~)
+;
[download]

Is one method preferrable to the other?

TIA :)

Comment on Quick DBI do question Download Code

Replies are listed 'Best First'.
Re: Quick DBI do question by kennethk (Abbot) 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.	[reply]
Re^2: Quick DBI do question by lostjimmy (Chaplain) on Feb 13, 2009 at 16:17 UTC
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.	[reply] [d/l]
Re^3: Quick DBI do question by kennethk (Abbot) on Feb 13, 2009 at 16:21 UTC
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".	[reply]
Re^3: Quick DBI do question by doom (Deacon) on Feb 15, 2009 at 03:23 UTC
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.)	[reply]
Re^2: Quick DBI do question by Anonymous Monk on Feb 17, 2009 at 13:16 UTC
Do you need to use "quote" if placeholders are used? i.e. `$dbh->quote($username);` [download]	[reply] [d/l]
Re^3: Quick DBI do question by kennethk (Abbot) on Feb 17, 2009 at 14:27 UTC
No, the bind_param method will handle necessary escapes - see Placeholders_and_Bind_Values for more information.	[reply]
Re^4: Quick DBI do question by Anonymous Monk on Feb 17, 2009 at 16:03 UTC
Re: Quick DBI do question by ForgotPasswordAgain (Priest) on Feb 13, 2009 at 15:56 UTC
Using placeholders (`?`) is absolutely preferable.	[reply] [d/l]
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.	[reply]

Back to Seekers of Perl Wisdom