Re: SQL Injection Basics

[Dirk Gomez <dirk () dirkgomez de>]

(I think this was only accidently just sent to me.)

> For ASP/SQL Server use the ADO command object with parameterised stored
> procedures to execute *all* SQL. This ensures that each variable is locked
> into a compartment that it cannot 'overflow'. Of course if you use dynamic
> SQL in your procs you may also be vulnerable. eg the name of the table is a
> parameter of the proc:
>
> CREATE PROCEDURE NeverUserDynamicSQL
> @Table VarChar(100)
> AS
>
> DECLARE @SQL VarChar(2000)
>
> SET @SQL = 'SELECT * FROM ' + @Table
> EXEC(@SQL)

Woohoo! This works in Oracle too. But - it is excruciatingly ugly and you want
to make sure to 

a) restrict it to a few people

b) don't paste @TABLE into the SQL statement. What I mean - in metacode:

  switch @Table
    foo : access foo with 'SELECT * FROM foo'
    bar:  access bar with 'SELECT * FROM bar'
    no_match: holler! 

The bottom line is: 

  Don't ever let user input make it into a SQL statement. 

If you have a compelling case that you need to construct a SQL statement on
the fly then make sure that you "preconstruct" the query _depending_ on the
user input.

-- Dirk