Re: Sql injection attacks

> Most of the online literature is on MS SQL Server. There, the
> consensus seems to be that the range of potential attacks is
> so wide that attempting to spot attack signatures in posted
> data is a doomed enterprise, and that the safest general
> approach for any dynamically built query is to execute it as
> a stored procedure.

This recommendation is one of the main reasons there are *soooo* many
SQL-injection vulnerable sites built on the ASP/SQLServer combination by
people that *think* they are safe. It used to be in the MS
documentation, but it's supposed to be all gone now. But it's still in a
lot of other guides out there.

Using a stored procedure is just as unsafe as using a direct SQL
statement, *unless* you use the parametrised (eh, is it really called
that? Wel..) format. And the other way around, using direct SQL is just
as safe *iff* you use parametetrised format.

"SELECT * FROM foo WHERE bar=" & request("bar")
is no more dangerous than
"sp_foo " & request("bar")

If you use the parametrised formats, e.g.
"SELECT * FROM foo WHERE bar=(at)bar"
or just "sp_foo" in the case of the stored procedure (the driver will
pick up parameter names automatically)

Then it's the responsibility of the driver to handle any escaping
needed. And AFAIK it does a good job of it.

Similar methods are of course available in pretty much all other DB
interfaces, depending on language/framework.

> In SQL Server, this reportedly works because the syntax of
> the query is pre-compiled, and the variables passed in are
> treated strictly as data and cannot alter the syntax. So any
> malicious use of "AND", "UNION", ";" etc in submitted data will fail.

Yes, this will fail. However, injecting something like:
"sp_foo 'bar' ; DROP TABLE foo"
is still very possible.

> Can anyone confirm that this would also apply to Postgres
> Query Language (SQL) functions? The effectiveness of moving
> the queries into the database against SQL injection attack
> would seem to depend on the query engine internals. Will
> using the SQL functions provide the robust protection I am
> looking for?

No. Parametrised queries will.

There are other reasons for moving the querys to the backend (isolation
and performance depending on your situation), but it won't provide the
protection you're asking for here.

//Magnus