Re: Protection from SQL injection

Hi,

> > Wouldn't it be much simpler to have a version of the libpq client lib
> > that only understands prepared queries?

That would be possible. However the problem is not 'prepared queries'
versus 'direct queries'. It is possible to use literals in prepared
queries:

PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE PASSWORD = '" + password + "');
prep.executeQuery();

This is unsafe. So you really need to filter on literals, and not on
the API. If you mean a library where literals not are allowed: that
would solve the problem, yes. However that would mean the client
library has to parse each SQL statement. That would complicate the
client library. Parsing is already done in the database engine,
technically it's better (long term) to disable literals there.

But it is possible to create a wrapper client library (for example a
JDBC wrapper driver) for other databases where literals are not
allowed. That would work for all databases.

> You could do that, but there's still no way for it to know exactly how
> the submitted query was constructed. This would block off the types of
> injections that want to add whole SQL commands, but not ones that just
> subvert the current query (eg adding OR TRUE to see data you shouldn't).

In most cases you don't need to know how the query was constructed. If
the query uses parameters for all user input then it is (almost
always) safe. Only 'almost' because there is still a small gap: ORDER
BY injection. Example:

String order = input("order (id, name)?");
ResultSet rs = stat.executeQuery(
"SELECT ID, NAME FROM ITEMS ORDER BY " + order);

Sometimes the input comes from a web application (the user clicks on
the column header to sort on it).

See also http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/samples/SQLInjection.java

> This is really a client problem and only client-side solutions will
> provide meaningful traction for it.

This is like data validation on client side javascript. Yes, it's good
to have data validation there. But, the user could have Javascript
disabled. In any case it's better to have data validation on the
server side as well.

> In perl, for instance, the "taint" mechanism is a good way to notice whether any insecure strings are
> getting into database queries.

There is no 'taint' mechanism in programming languages like Java.
Also, it requires that the application is reviewed. In the companies I
worked so far there were almost no code reviews. A lazy programmer can
still make mistakes.

Regards,
Thomas