[thelist] Website Hacked?

[Chris Anderson]

> This is a 100% fool proof way of avoiding SQL injection attack if you
> use it everywhere, because unless the API programmer made a mistake
> writing their SQL parser, you're not vulnerable. And my bet is that
> DBMS programmers write much better SQL parsers than most of us ever
> will.

+1 (except to get 100% foolproof, you have to ensure you aren't
constructing and executing SQL in the stored proc itself)

Eg if your sp was something like:

CREATE PROC spSearchTable(@descriptionCriteria VARCHAR(1000))
AS
	DECLARE @sql VARCHAR(4000)
	SET @sql = "SELECT stuff FROM tableA WHERE desc LIKE '" +
@descriptionCriteria + "'"
	EXEC @sql


Then it doesn't matter if you are using parameterised queries to send
the value of the @descriptionCriteria parameter. It is still susceptible
to someone entering a search string of:

	';TRUNCATE TABLE tableA;--
because the stored proc will create the following statement:

	SELECT stuff FROM tableA WHERE desc LIKE '';TRUNCATE TABLE
tableA;--'

This would occur even if you were using parameterised queries!
It's a rare case that requires a stored proc like this, but I think it's
worth highlighting.

Chris