SEO

Hi,

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

I think I found 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 for this
session. 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

I know about the Perl taint mode, but this is only for Perl. I also
know about disabling multi-statement commands (only solves part of the
problem). PostgreSQL should also support database level 'constants'
that are similar to constants in other programming languages,
otherwise application level constants (such as 'active') can't be used
in queries directly (I propose to add new SQL statements CREATE
CONSTANT ... VALUE ... and DROP CONSTANT ..., example: CREATE CONSTANT
STATE_ACTIVE VALUE 'active'). I also know the 'disabling literals'
feature does not solve SQL injection completely: for example 'ORDER BY
injection' where an application dynamically adds the column to sort on
based on a hidden 'sort column' field in a web app. To solve that I
suggest to support parameterized ORDER BY: ORDER BY ? where ? is an
integer. Then, instead of using SET ALLOW_LITERALS NONE the use of
literals should probably be two access right (REVOKE LITERAL_TEXT,
LITERAL_NUMBER FROM APP_ROLE). Those are details that still need to be
discussed.

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

P.S. I have send this proposal to pgsql-sql@postgresql.org first and
got replies, but I would like to get some feedback from the PostgreSQL
developers as well.

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

Thomas,

> 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?

Have you seen Meredith's libdejector?
http://sourceforge.net/projects/libdejector

--Josh Berkus

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

* Thomas Mueller (thomas.tom.mueller@gmail.com) wrote:
> As you know, "SQL injection" is the main security problem of databases today.

I think there's a fallacy there- it's the main security problem of
applications (particularly those on the web) today. It hasn't got much
at all to do with the database's security.

Also, hasn't this been discussed to death already?

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIFiLPrzgMPqB3kigRAmbcAJ46vWF3rw/ZesX+IpLyRILI6/anYwCdEGCA
W8HkfVSnAJ1II/ZULubf5pU=
=/Z6d
-----END PGP SIGNATURE-----

> As you know, "SQL injection" is the main security problem of databases
> today.
>
> I think I found 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:

I have found that the little bit of code posted afterwards did eliminate
SQL holes in my PHP applications with zero developer pain, actually it is
MORE convenient to use than randomly pasting strings into queries.

You just call
db_query( "SELECT * FROM table WHERE column1=%s AND column2=%s",
array( $var1, $var2 ));

It is inspired from the Python interface which performs the same (but
slightly more elegantly).
I have removed the logging features for clarity.

function db_quote_query( $sql, $params=false )
{
// if no params, send query raw
if( $params === false ) return $sql;
if( !is_array( $params )) $params = array( $params );

// quote params
foreach( $params as $key => $val )
{
if( is_array( $val )) $params[$key] = implode( ', ', array_map( intval,
$val ));
else $params[$key] =
is_null($val)?'NULL'"'".pg_escape_string($val)." '");;
}
return vsprintf( $sql, $params );
}

function db_query( $sql, $params=false )
{
// it's already a query
if( is_resource( $sql ))
return $sql;

$sql = db_quote_query( $sql, $params );

$r = pg_query( $sql );
if( !$r )
{
echo "<div class=bigerror><b>Erreur PostgreSQL :</b><br
/>".htmlspecialchars(pg_last_error())."<br /><br /><b>Requête</b> :<br
/>".$sql."<br /><br /><b>Traceback </b>:<pre>";
foreach( debug_backtrace() as $t ) xdump( $t );
echo "</pre></div>";
die();
}
return $r;
}






>
> SET ALLOW_LITERALS NONE;
>
> Afterwards, SQL statements with text are not allowed any more for this
> session. 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
>
> I know about the Perl taint mode, but this is only for Perl. I also
> know about disabling multi-statement commands (only solves part of the
> problem). PostgreSQL should also support database level 'constants'
> that are similar to constants in other programming languages,
> otherwise application level constants (such as 'active') can't be used
> in queries directly (I propose to add new SQL statements CREATE
> CONSTANT ... VALUE ... and DROP CONSTANT ..., example: CREATE CONSTANT
> STATE_ACTIVE VALUE 'active'). I also know the 'disabling literals'
> feature does not solve SQL injection completely: for example 'ORDER BY
> injection' where an application dynamically adds the column to sort on
> based on a hidden 'sort column' field in a web app. To solve that I
> suggest to support parameterized ORDER BY: ORDER BY ? where ? is an
> integer. Then, instead of using SET ALLOW_LITERALS NONE the use of
> literals should probably be two access right (REVOKE LITERAL_TEXT,
> LITERAL_NUMBER FROM APP_ROLE). Those are details that still need to be
> discussed.
>
> 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
>
> P.S. I have send this proposal to pgsql-sql@postgresql.org first and
> got replies, but I would like to get some feedback from the PostgreSQL
> developers as well.
>



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

On Tue, Apr 29, 2008 at 7:00 AM, PFC <lists@peufeu.com> wrote:
> I have found that the little bit of code posted afterwards did eliminate
> SQL holes in my PHP applications with zero developer pain, actually it is
> MORE convenient to use than randomly pasting strings into queries.
>
> You just call
> db_query( "SELECT * FROM table WHERE column1=%s AND column2=%s", array(
> $var1, $var2 ));
>

Implementing this for yourself is crazy; PHP's Postgres extension
already does this for you since 5.1.0:

$result = pg_query_params("SELECT foo FROM bar WHERE baz = $1", array($baz));

http://www.php.net/manual/en/functio...ery-params.php

Cheers,
BJ

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

On Mon, Apr 28, 2008 at 08:55:34PM +0200, Thomas Mueller wrote:
> As you know, "SQL injection" is the main security problem of databases today.
>
> I think I found a solution: 'disabling literals'.

I personally think this is wrong, I often have schemas that mean I have
to do things like:

SELECT a.x, a.y, b.z
FROM a, b
WHERE a.a = b.a
AND a.f = 'lit'
AND b.g = 'lit'
AND b.h = $1;

So a big query, with lots of literals and only very few of them actually
come from an untrusted source. Also remember that any literal (i.e. not
just strings) can be quoted, think of dates in queries.

One option I like would be if the programming language (that you're
calling the database from) recorded "tainting" of variables, preferably
if this is done statically in the type system but languages like PHP
seem to prefer to do this sort of thing at run time.

Microsoft's approach of integrating SQL into the language would work as
well, the programmer can't get the quoting wrong then. But I prefer the
approach taken by HaskellDB as it doesn't require new syntax/semantics
to be designed/integrated. HaskellDB is a bit heavy though.


Sam

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

On Tue, 29 Apr 2008 01:03:33 +0200, Brendan Jurd <direvus@gmail.com> wrote:

> On Tue, Apr 29, 2008 at 7:00 AM, PFC <lists@peufeu.com> wrote:
>> I have found that the little bit of code posted afterwards did
>> eliminate
>> SQL holes in my PHP applications with zero developer pain, actually it
>> is
>> MORE convenient to use than randomly pasting strings into queries.
>>
>> You just call
>> db_query( "SELECT * FROM table WHERE column1=%s AND column2=%s", array(
>> $var1, $var2 ));
>>
>
> Implementing this for yourself is crazy; PHP's Postgres extension
> already does this for you since 5.1.0:
>
> $result = pg_query_params("SELECT foo FROM bar WHERE baz = $1",
> array($baz));
>
> http://www.php.net/manual/en/functio...ery-params.php
>
> Cheers,
> BJ

pg_query_params is quite slower actually...



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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...erByTest.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

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

On Tue, Apr 29, 2008 at 01:37:37PM +0200, Thomas Mueller wrote:
> > 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.

In other words, your programmer was stupid. And your example doesn't
work because no matter what the string is it can't return anything
other than rows from the orders table. If you're worried about them
using semicolons to introduce another query, prepare has prohibited
that for a long time already.

But as far as I'm concerned, the real killer is that it would make
using any interactive query interface impossible. I don't think it's
reasonable to include a complete SQL parser into psql just so I can
type normal queries.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIFx+cIB7bNG8LQkwRAjbsAJ9SrZTh5pwleKsq6eFBcz 8VQtiKFQCgkGVY
k9C1P6/1WfVK6tvokAqN2d8=
=3DjQ
-----END PGP SIGNATURE-----

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

Sure, there is no way to enforce it (apart from grepping the source for
pg_query() and flogging someone if it is found), but is it really
necessary when the right solution is easier to use than the wrong solution
? Capitalizing on developer laziness is a win IMHO, lol.

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

IMHO this is an example of what should never be done.

// very bad (especially in PHP where you never know the type of your
variables)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + orderId;

// slightly better (and safe)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + int( orderId );

// correct (PHP syntax)
pg_query_params( "SELECT * FROM ORDERS WHERE ORDER_ID = $1",
array( orderId ))
db_query( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", array( orderId ))

// correct (Python syntax)
cursor.execute( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", ( orderId, ))

The last two don't complain if orderId is a string, it will be correctly
quoted, and then postgres will complain only if it is a string which does
not contain a number. This is useful in PHP where you never know what type
you actually have.

The little function in my previous mail is also useful for mysql which has
no support for parameterized queries.


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