SEO

Hi,

As you know, "SQL injection" is the main security problem of databases today.

I think I have a solution: 'disabling literals'. Or you may call it
'enforcing the use of parameterized statements'. This means that SQL
statements with embedded user input are rejected at runtime. My
solution goes beyond saying "developers ~should~ use parameterized
statements". That is not a solution because developers are lazy. My
solution is: "developers MUST use parameterized statements". It goes
like this: Literals are disabled using the SQL statement:

SET ALLOW_LITERALS NONE;

Afterwards, SQL statements with text are not allowed any more. That
means, SQL statement of the form "SELECT * FROM USERS WHERE
PASSWORD='qerkllkj'" will fail with the exception 'Literals are not
allowed, please use parameters'. It is like the database does not know
what ='qerkllkj' means. Only statements of the secure form are
allowed, for example "SELECT * FROM USERS WHERE PASSWORD=?". This
solves the problem because SQL injection is almost impossible if user
input is not directly embedded in SQL statements.

The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or
by an administrator. It is still possible to generate SQL statements
dynamically, and use the same APIs as before, as long as SQL
statements don't include literals. Literals can still be used when
using query tools, or in applications considered 'safe'. To ease
converting the application to use parameterized queries, there should
be a second mode where number literals are allowed: SET ALLOW_LITERALS
NUMBERS. To allow all literals, execute SET ALLOW_LITERALS ALL (this
is the default setting).

So far this feature is implemented in my little database H2. More
information about this feature is described here:
http://www.h2database.com/html/advan...#sql_injection

What do you think about it? Do you think it makes sense to implement
this security feature in PostgreSQL as well? If not why not? Does
PostgreSQL have another solution or plan to solve the SQL injection
problem?

Regards,
Thomas

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

"Thomas Mueller" <thomas.tom.mueller@gmail.com> writes:
> SET ALLOW_LITERALS NONE;

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

This is just silly, as it makes life impossibly painful for users
(constants are hardly a useless part of SQL) and it doesn't really
plug any holes. As an example:

select * from tab where intcol = intcol; delete from tab;

contains no literals and yet the delete is very probably injected.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, Apr 26, 2008 at 11:32 AM, Thomas Mueller
<thomas.tom.mueller@gmail.com> wrote:
>
> The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or
> by an administrator.


then it solves nothing...
what if the developer never SET ALLOW_LITERALS NONE or
maybe i can inject "select * from tab where intcol = intcol; set
allow_literals all; add any query you want"

--
regards,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Hi,

> > The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or
> > by an administrator.
> then it solves nothing...
> what if the developer never SET ALLOW_LITERALS NONE

As I have said, the 'ALLOW_LITERALS NONE' mode is enabled by the
developer itself, or by an administrator. The developer may be lazy,
but the administrator can enforce this policy.

> maybe i can inject "select * from tab where intcol = intcol; set
> allow_literals all; add any query you want"

How do you inject this? How would the application looks like where
this can be injected?

Regards,
Thomas

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, Apr 26, 2008 at 3:32 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Thomas Mueller wrote on 26.04.2008 18:32:
>
> > Literals can still be used when using query tools, or in applications
> considered 'safe'.
> >
> I fail to see how the backend could distinguish between a query sent by a
> query tool and a query sent by an "application".

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Wouldn't it be much simpler to have a version of the libpq client lib
> that only understands prepared queries?

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).

This is really a client problem and only client-side solutions will
provide meaningful traction for it. In perl, for instance, the "taint"
mechanism is a good way to notice whether any insecure strings are
getting into database queries.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, Apr 26, 2008 at 9:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > Wouldn't it be much simpler to have a version of the libpq client lib
> > that only understands prepared queries?
>
> 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).
>
> This is really a client problem and only client-side solutions will
> provide meaningful traction for it. In perl, for instance, the "taint"
> mechanism is a good way to notice whether any insecure strings are
> getting into database queries.

Agreed. My point was that to do what the OP wants, wouldn't it make
more sense to just lobotomize libpq so it doesn't understand anything
but prepared queries. Doesn't obviate the need for a client side
language based solution. Just seems to make WAY more sense than
trying to make the change at the server level in pgsql.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Agreed. My point was that to do what the OP wants, wouldn't it make
> more sense to just lobotomize libpq so it doesn't understand anything
> but prepared queries.

I doubt that that particular lobotomization accomplishes much in
comparison to the penalties.

IIRC there was some discussion recently of providing a mode in which
the server would reject PQexec strings containing more than one query.
I didn't care for it much at the time, but I think it would provide
most of the benefit of these suggestions with far less compatibility
or performance hit.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, Apr 26, 2008 at 9:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> IIRC there was some discussion recently of providing a mode in which
> the server would reject PQexec strings containing more than one query.
> I didn't care for it much at the time, but I think it would provide
> most of the benefit of these suggestions with far less compatibility
> or performance hit.

agreed.

And I trust (SQL) code review more than tying the hands of the programmers.

But I've always had the luxury of working with developers who liked me
as a DBA and were willing to do things my way, as far as the DB was
concerned anyway...

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql