shanta:

DBI has all the SQL quoting rules handled, so using placeholders would turn it into:

select foo, bar from my_table 
where col < '''drop table my_table; --' 
  or (col > 'def_val' and col3 < fgh_val)
[download]

Here, the drop table command isn't exposed as an SQL statement, it's just a column value checked in the where clause. If you were really careful all the time and made sure you handled all the quoting and comments properly, you could ignore placeholders and not run into trouble. But I don't know anyone careful enough to *always* get it right. Instead DBI does it for you, and you don't need to remember the rules for whichever database you use.

Some database back ends understand placeholders and can give you a performance boost when you use them, so using placeholders gives you the chance to squeeze a little more performance out of your database for the ones that understand it (such as Sybase, MSSQL, Oracle, Pg).

For example, suppose you wanted to do something like this:

insert into my_table
select ? as foo, ? as bar, c.id as product_id, d.price as product_pric
+e
from my_stuff c where c.name=?
join friend_prices d where d.name=? and c.class=d.class
[download]

When the SQL back-end parses this, it writes a little program to do the table joins and lookups for the insert. If you just substitute the values into the SQL and prepare then execute it each time, it has to repeat all the work to generate the query plan each time. But if you use placeholders, it only needs to do generate the query plan once, because you can re-use the statement:

my $ST = db->prepare(q{
    insert into my_table
    select ? as foo, ? as bar, c.id as product_id, d.price as product_
+price
    from my_stuff c where c.name=?
    join friend_prices d where d.name=? and c.class=d.class
});

$ST->execute(123, 456, 'Mouse', 'Joe');
$ST->execute(149, 357, 'Mousepad', 'Joe');
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

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

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