Re: Protection from SQL injection - Mailing list pgsql-hackers

Hi,

> Meredith's libdejector

1) The last activity was 2005-12-17 :-(
2) From the docs: "the techniques used ... are ... being explored for
patentability".
3) The tool validates the SQL statement. This is not required when
using parameterized queries.
4) An 'exemplar' query is required for each query.
It's an interesting idea, and can even find the ORDER BY injection
that 'disabling literals' can't find. However there are problems: 2) +
4).

> zero developer pain

Actually it's not zero pain, but the main problem is: there is no way
to enforce using it.

> [SQL injection] is the main security problem of applications

Yes and no. Is buffer overflow an application or language problem? In
C / C++ buffer overflow is a problem. Java enforces array bounds
checking. What I suggest is to enforce using parameterized statements.
This is like having a painless, enforcible 'array bounds checking
mode' in C / C++.

> hasn't this been discussed to death already?

Yes, but no good solution has been found so far.

> II have to do things like: WHERE a.f = 'lit' AND b.h = $1;

In C the best practice is to use #define for constants. In C++ you
have 'const', in Java 'static final'. Unfortunately the 'named
constant' concept doesn't exist in SQL. I think that's a mistake. I
suggest to support CREATE CONSTANT ... VALUE ... and DROP CONSTANT
..., example: CREATE CONSTANT STATE_ACTIVE VALUE 'active'.

> any literal (i.e. not just strings) can be quoted, think of dates in queries.

The problem is not only quotes. The problem is all kinds of user
input. For example: sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " +
orderId; This is not a problem if orderId is a number. But what if
it's a String? For example "1 AND (SELECT * FROM USERS WHERE
NAME='admin' AND PASSWORD LIKE 'm%')". An attacker could then retrieve
the admin password quite quickly.

> "tainting" of variables

See Meredith's libdejector: regular expression checking doesn't always
work. Also, programming languages such as Java don't support tainting.
And it's again in the hand of the developer to use it, not use it, or
use it in the wrong way. There should be a way for an admin to enforce
using it, and using it correctly.

> Microsoft's approach of integrating SQL into the language

Yes, LINQ is a good approach. For Java there is a project called
'Quaere' that provides something similar (however only when using the
'Alias' syntax, I wrote this part, see
http://svn.codehaus.org/quaere/trunk/Quaere/src/test/java/org/quaere/alias/test/SamplesOrderByTest.java).
However it will take a long time until all applications are converted.
With 'disabling literals', applications can be converted step-by-step.
'Disabling literals' can be used as a development tool, and it can be
enabled or disabled at runtime. With LINQ / Quaere / HaskellDB
migration will be harder and slower because you need to re-write the
application.

>  HaskellDB

The query syntax seems to be quite 'different'. I would prefer if the
syntax is as close as possible to SQL to simplify migration.

Regards,
Thomas