Re: Sql injection attacks

Geoff Caplan <geoff(at)variosoft(dot)com> wrote:

> Hi folks,
>
> I'm new to Postgres and trying to get up to speed on the security
> issues. There seems to be remarkably little Postgres specific stuff on
> preventing 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.

Huh?

To protect yourself from SQL injections, just pass all your data through
PQescapeString() (or the appropriate function for the language you're
developing in). That will escape any character that could be used to
initiate an SQL injection, thus foiling the attempt.

That "consensus" you speak of doesn't make much sense to me. Besides,
who cares about "detecting" the SQL injection, as long as it's unable
to work.

> 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.
>
> 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, and I can't see how it will protect in MS SQL server either? They'll
just learn that they have to escape out of the command that calls the
stored function.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com