28 November 2006

Bind Your Variables or Die

NOTE: I'm re-pasting this from an internal wiki page I wrote up for our new developers who may not be aware of such a thing as bind variables and how they can greatly improve performance and scalability.

Most know that using bind variables help greatly with performance when dealing with an often-used query, such as a customer lookup. Read Mark Rittman's article, which does a great job of explaining all there is to be explained in that regard.

A quick summary that Oracle has to re-parse literal queries everytime, generating new query plans every time and storing that processed query in memory. With bind variables, only one such process, parse and memory storage takes place and that information gets re-used when that bind query is called again with different bound values. This saves on the CPU having to re-parse and re-plan the query, and saves on memory by not having to store yet another query plan.

To get an idea of how important DBAs consider the use of bind variables, read Tom Kyte's thrashing of a user who was unlucky enough to incur his wrath.

Read about Using Bind Variables to Guard against SQL injection. SQL injection exploits are one of the most common attacks on database-driven websites, as attackers would exploit the fact that a SQL query such as user validation is built with literals, and they could turn this query:

SELECT * FROM users
WHERE username='dts'
and password='foo'

into

SELECT * FROM users
WHERE username='dts'
and password='foo' or 1=1

Using bind variables prevents such exploits from happening due to how the values are substituted.

No comments:

Post a Comment