Re: Protection from SQL injection

Hi,

For PostgreSQL the 'disable literals' feature would be great
publicity: PostgreSQL would be the first only major database that has
a good story regarding SQL injection. Yes it's not the magic silver
bullet, but databases like MS SQL Server, Oracle or MySQL would look
really bad.

> [literals...] a permission that would default to on, but be REVOKE-able.

Exactly.

> Forbidding literals will break absolutely every SQL-using application on the planet

Well, it's optional. If a developer or admin wants to use it, he will
know that it could mean some work. Even if the feature is not enabled,
it's still good to have it. And using constants will help document the
application.

> CREATE CONSTANT state_active TEXT VALUE 'active'; ? ;)

Not necessarily. The database knows that 'active' is a text, no need
to repeat that. Auto-detecting data types already works: CREATE TABLE
TEST AS SELECT 1 AS ID FROM DUAL will result in an int4. That's enough
for constants. But I don't mind using explicit data types.

> Note that using parameters even for things which are actually constants is not really very desirable. If you have a query like: SELECT * FROM users WHERE userid = ? AND status = 'active'

Using 'active' anyway is bad: Think about typos. The constant concept
(that exists in every language except SQL) would be good in any case:
SELECT * FROM users WHERE userid = ? AND status = STATUS_ACTIVE (or
CONST.STATUS_ACTIVE if it's in the CONST schema).

> libdejector

It's a good tool, but it's more work for the developer than disabling
literals (because for each query you need to add a exemplar).

> dynamic search screens
> $criteria = "WHERE $var1 = '$var2'"

In Java (sorry about that ;-) I would write:
PreparedStatement prep = conn.prepareStatement("SELECT * FROM ITEMS
WHERE " + var1 + " = ?");
prep.setString(1, var2);

Regards,
Thomas