On Mar 16, 12:22 am, y..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> lorenzo (lorenzo.el...._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.
Dynamic SQL is the only simple way to get a sql statement to update or
select from a changeable table name; in other words if you don't know
at compile time if you will be selecting from table A instead of table
B.
Received on Fri Mar 16 2007 - 08:17:24 CDT
