comment on

If you have not already read Use Placeholders for SECURITY, read that first, it is a more important point. But if you do serious database work, you should probably consider my point below *after* fully implementing the SECURITY stuff.

Many (but not all) RDBMSs support pre-parsing and/or pre-optimizing of SQL statements. That means that the RDBMS has to do a certain amount of work every time it prepares a statement. In situations where you are going to execute the same statement with varying values, you can (sometimes immensely) increase performance with a prepare-once, execute-many approach. In all cases, read the DBD and RDBMS docs on placeholders and performance for your DBD/RDBMS, but in general here's an example of how to use placeholders for performance:

RIGHT:
my $sth = $dbh->prepare(
    "UPDATE foo SET bar=7 WHERE baz=?"
);
for my $val(@values) {
    $sth->execute($val);
}

WRONG:
for my $val(@values) {
    $val = $dbh->quote($val);
    my $sth = $dbh->do(
       "UPDATE foo SET bar=7 WHERE baz=$val"
    );
}
[download]

The WRONG code does a prepare() and an execute() i.e. do() every time through the loop. The RIGHT code does the prepare() once at the top of the loop, and then does an execute() each time through the loop.

In reply to Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by jZed

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.


Come for the quick hacks, stay for the epiphanies.
	PerlMonks