DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=uYmtsZCqiuAM+O4fRDlIktZ9nqsk2Dz3ZXWJh+2zU0iPOd6OBOFYNTbVp2XwkW/rgSNEEkmnV+cEQXB0LVyXun68Bw0MvQB8hJ4nfsvWF8D7IdyaeVwSh1jxb+4CL3aIufaMVQ3rZNS25S4b66y4TXxWN94p2zYOwT0D1pBn71Y=
Message-ID: <5f211bd50804261116q5cdc9a74xa345b43704441d2a@mail.gmail.com>
Date: Sat, 26 Apr 2008 20:16:56 +0200
From: "Thomas Mueller" <thomas.tom.mueller@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Fwd: Protection from SQL injection
In-Reply-To: <5f211bd50804261112id0fe835r3163baf87cd2cb7d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <5f211bd50804260932l7a63618jca64858271ff269f@mail.gmail.com>
	<12890.1209229514@sss.pgh.pa.us>
	<5f211bd50804261112id0fe835r3163baf87cd2cb7d@mail.gmail.com>

Hi,

>  I think you missed April Fool's Day...

This is absolutely not an April Fool idea :-) I must have made a bad
job explaining my idea.

>  This is just silly, as it makes life impossibly painful for users

You mean developers? No, developers should use parameterized queries anyway.

>  (constants are hardly a useless part of SQL)

Your are right. But it depends how you define 'constant'. In other
programming languages, constants are not always literals. Instead,
constants are defined like this:

#define ACTIVE 1  // C
final static int ACTIVE=1; // Java

As far as I know, this concept doesn't exist in the SQL. I suggest to
add this concept as well to the database engine:

CREATE CONSTANT [IF NOT EXISTS] newConstantName VALUE expression;
DROP CONSTANT [IF EXISTS] constantName;

Example:

CREATE CONSTANT PI VALUE 3.1415926535;
CREATE CONSTANT ACTIVE VALUE 1;
CREATE CONSTANT INACTIVE VALUE 0;

This also improves the program because you don't need to 'hard code'
numbers in the application:

SELECT * FROM USERS WHERE STATE=ACTIVE AND PASSWORD=?

> it doesn't really plug any holes.

Sure it does.

> As an example:
> select * from tab where intcol = intcol; delete from tab;

How would the application that executed this statement would look like? In Java:

ResultSet rs = stat.executeQuery("select * from tab where " + userInput);

Such a program wouldn't make any sense, right? Do you mean this?

ResultSet rs = stat.executeQuery("select * from tab where state = " +
userInput);

If literals are disabled, the database would reject any number. The
program wouldn't work in the normal case any longer if literals are
disabled. So the developer would have to change it to (otherwise his
application doesn't work) to:

PreparedStatement prep = conn.prepareStatement("select * from tab
where state = ?");
prep.setInt(1, userInput);
 ...

This is save. There is no way to inject SQL here.

Regards,
Thomas