SPUG: BD::mysql::st execute failed:

Peter Darley pdarley at kinesis-cem.com
Wed Sep 27 08:20:39 PDT 2006


Jacinta,

	I don't agree.  Anything that hides the actual SQL statement is bad bad
bad.  If there wasn't an alternative it would be worth putting up with the
place holders, but since you can get the same protection without hiding the
exact query, I don't think it's a very good practice.  Things like
SQL::Abstract make me cringe. :)

Thanks,
Peter

-----Original Message-----
From: spug-list-bounces+pdarley=kinesis-cem.com at pm.org
[mailto:spug-list-bounces+pdarley=kinesis-cem.com at pm.org]On Behalf Of
Jacinta Richardson
Sent: Saturday, September 23, 2006 6:45 PM
To: spug-list at pm.org
Cc: luis medrano
Subject: Re: SPUG: BD::mysql::st execute failed:


Peter Darley wrote:

> $Query = "INSERT INTO HTML_Pages (Page_ID, Type, Entity, Name, Title,
> Content) VALUES (" . Quote($Args{PageID}) . ", " . Quote($Args{Type}) . ",
"
> . Quote($Args{Entity}) . ", " . Quote($Args{Name}) . ", " .
> Quote($Args{Title}) . ", " . Quote($Args{Content}) . ")";
>
> $Neo::DB::Shopper->do($Query) || die "Bad Query!\n\t$Query";

It's still a better solution to use placeholders:

my @values = ($post_author, $post_date, $post_date_gmt, "@post_content",
	$post_title, $post_status, $comment_status, $ping_status, $post_name,
	$post_modified, $post_modified_gmt, $guid);

my $sth1 = $dbh->prepare("INSERT INTO wp_posts (post_author, post_date,
 		post_date_gmt, post_content, post_title, post_status,
 		comment_status, ping_status, post_name, post_modified,
		post_modified_gmt, guid)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
	or die $dbh->errstr;

my $rv1 = $sth->execute(@values);


As it says in the documentation, using place holders allows the database
engine
to be more efficient as well as ensuring that your values are properly
quoted
for whichever database engine you are using (and if your underlying database
engine changes, then that's handled too!

You may also find it useful to look into SQL::Abstract as it makes these
large
inserts and updates much easier.  Of course there's always
Class::DBI/DBIx::Class if you want to take it a step further than that too.

	my %fieldvars = (
		post_author => $post_author,
		post_date   => $post_date,
		...
		guid        => $guid,
	);

	use SQL::Abstract;

	my $sql = SQL::Abstract->new;
	my($stmt, @bind) = $sql->insert("wp_posts", \%fieldvals);

	my $sth = $dbh->prepare($stmt);
   	$sth->execute(@bind);

All the best,

	Jacinta

--
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
 (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |
_____________________________________________________________
Seattle Perl Users Group Mailing List
     POST TO: spug-list at pm.org
SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
    MEETINGS: 3rd Tuesdays
    WEB PAGE: http://seattleperl.org/



More information about the spug-list mailing list