|
kiat has asked for the wisdom of the Perl Monks concerning the following question:
Greetings to all monks,
Just a quick question: Which of the following ways of executing UPDATE is the preferred method?
# clean sub not shown
my $nick = clean(param('nick'));
# Method 1 ( Use prepare )
--------------------------
$dbh = open_dbi();
my $sth = $dbh->prepare(qq{ UPDATE
$table{'logins'} SET online='N' WHERE nick=? });
$sth->execute($nick)
or bail_out("Cannot execute UPDATE");
# Close connection
#Method 2 ( Use do() )
----------------------
$dbh = open_dbi();
my $sql = qq{ UPDATE
$table{'logins'} SET online='N' WHERE nick=$nick });
$dbh->do($sql)
or bail_out("Cannot execute UPDATE");
# Close connection
Pretty mundane stuff but any advice will be greatly appreciated.
20040115 Edit by BazB: Changed title from 'perl dbi question...' |
Re: DBI prepare() vs do() usage
by edan (Curate) on Dec 21, 2003 at 14:08 UTC
|
It depends. If it's a one-shot deal, I (personally) would prefer do() since it's more straightforward, and that's pretty much what it's for. If, however, you plan to do this in a loop, for many updates, so then you'd want to prepare() once, then execute() many times. But, in any case, you should still use placeholders in your do:
#Method 2 ( Use do() )
----------------------
$dbh = open_dbi();
my $sql = qq{ UPDATE
$table{'logins'} SET online='N' WHERE nick=? });
$dbh->do($sql, undef, $nick)
or bail_out("Cannot execute UPDATE");
# Close connection
| [reply]
[d/l] |
|
|
Thanks, edan!
I didn't know I could use a placeholder with do(). I'll definitely use do() for a one-off execution now that I now it's possible to use a placeholder.
Btw, why do you need the 'undef' there? Is it compulsory?
| [reply] |
|
|
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);
Since @bind_values is the 3rd arg and you aren't specifying \%attr, you use
either undef or {} in it's place:
$rv = $dbh->do($statement, undef, @bind_values);
$rv = $dbh->do($statement, {}, @bind_values);
So, then what is \%attr used for? Well, search for that string in the
docs and you will be lead to:
Notation and Conventions
The following conventions are used in this document:
$dbh Database handle object
$sth Statement handle object
... yadda yadda ...
undef NULL values are represented by undefined values in Perl
\%attr Reference to a hash of attribute values passed to methods
Do a little more research first, then ask us. :)
| [reply]
[d/l]
[select] |
Re: DBI prepare() vs do() usage
by liz (Monsignor) on Dec 21, 2003 at 14:11 UTC
|
In this example, method 1, for one reason. Suppose the following assignment is made to $nick:
$nick = "'something'; DELETE FROM existingtablename";
and execute using method 2.
Always make sure that the values you insert are quoted properly. Method 2 doesn't ensure that and therefore creates an SQL injection vulnerability.
Of course, if you can be sure that $nick contains what you expect, you can always directly quote the value in the SQL expression, or use the $dbh->quote() method.
Liz | [reply]
[d/l]
[select] |
|
|
Liz,
Actually I was concerned about this exact issue recently after reading an article on SQL injection tricks. I did some research and found that DBI (by default) does not support multiple SQL statements in a single statement. This can be overridden by the DBI driver though. Here is a quote from the DBI documentation:
(In the "General Interface Rules & Caveats" section on this page)
Multiple SQL statements may not be combined in a single statement handle ($sth),
although some databases and drivers do support this (notably Sybase and SQL
Server).
Of course this is not excuse for not being a good paranoid programmer, but its nice to know anyway.
-stvn
| [reply] |
Re: DBI prepare() vs do() usage
by CountZero (Bishop) on Dec 21, 2003 at 14:09 UTC
|
I would say the first one, but with a caveat: not all Database engines/drivers support "prepare". I don't mean it gives an error if you use it, simply it doesn't do anything with it and just waits for the "execute" to start looking at it.I always tend to use the "prepare" method if only because the parameters passing automatically takes care of all quoting issues (so I almost never use qq{ }).
CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law
| [reply]
[d/l] |
Re: DBI prepare() vs do() usage
by bradcathey (Prior) on Dec 21, 2003 at 14:34 UTC
|
| [reply]
[d/l]
[select] |
Re: DBI prepare() vs do() usage
by jZed (Prior) on Dec 21, 2003 at 23:33 UTC
|
do() just runs prepare() and execute() under the hood, it's only syntactic sugar on top of them. So a single do() performs exactly the same actions as a single prepre()+execute() combo. The reason it's usually bad for loops is that it does prepare()+execute() each time through the loop when what you usually want is one prepare() at the top and multiple execute()s inside the loop.
The other difference is what's returned. For example if you want to know the names of the columns, you need to use prepare() to get an $sth to use with $sth->{NAME}.
As others have said, do() supports placeholders, so that isn't a reason to avoid it.
| [reply] |
Re: DBI prepare() vs do() usage
by perlygapes (Sexton) on Aug 01, 2020 at 01:02 UTC
|
I came across this thread because I am looking for an answer to a problem I am having with the following code:
# update using the dbh handle
my ($query, @bind) = $sqla->update(
'mytable',
{ field2 => $field2_new2 },
{ field2 => $field2_new1 },
);
$dbh->do($query, undef, @bind);
Everytime I try to debug this in eclipse EPIC it keeps throwing this error:
Can't call method "do" on an undefined value at C:/Users/user/eclipse-
+workspace/myproject/example.pl line 73, <__ANONIO__> line 3.
Given the use of undef is supposedly required, does anyone have any idea as to why I see this error?
Grats | [reply]
[d/l]
[select] |
|
|
| [reply]
[d/l] |
|
|
