Re: Difference between PreparedStatement and StoredProcedure ?

lorenzo (lorenzo.el.sol_at_ifrance.com) wrote:
: Hi,

: Well.. I'm trying to understand differences between the 3 ways of a
: web application to call a database (in a security and sql injection
: risks overview) :

: - Dynamic SQL Statement

User input typically used to build sql statements. Tricky input leads to unexpected sql statements = unexpected results = very bad security.

: - PreparedStatement (with bind variable)

User values can never be misinterpreted = good security.

However, the sql used for the PreparedStatement statement is itself just a _string_, typically it is _dynamically_ built at runtime by something like a java program, even if it does use bind variables for the user input. Programmer errors means that the string may _accidently_ include user input so you may get injection anyway, and not even know you need to be checking for it = not as good security as you think.

Because these strings are spread around many programs outside of the database there is no way for a code reviewer to be sure they've verified them all = not so good security.

Also, if you allow external programs to run sql, and if a hacker gets into your application server, then they too can run SQL against your database = security only as good as the weakest link.

: - Stored procedures (or callable statement)

Assuming you don't use dynamic sql, all sql statements are forced to use bind variables = good security.

Assuming you don't use dynamic sql, all sql statements are predetermined at compile time, so programmer error cannot accidently allow sql injection = good security.

Assuming you force all external access to be through stored procedures, then if a hacker gets into your application server then they still can't run SQL against the database = best security.

ALSO, all the SQL resides in a _single_ location - the database. A code review can be certain that all the SQL has been examined = better security. Received on Fri Mar 16 2007 - 00:22:42 CDT