SEO

"Thomas Mueller" <thomas.tom.mueller@gmail.com> writes:

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

I bet you could do something clever with Java.

Something like making the Execute() stmt take a special kind of string object
which enforces that it can only be constructed as static final and takes a
String as a constructor argument . That would let you use literals in the
queries but bar you from including any user input at runtime. You could even
include some methods for assembling such StaticStrings in useful ways which
would let you build queries dynamically out of immutable pieces.

I think you're tilting at windmills if you want to bar *all* literals. That's
just too big of a usability hit and as you pointed out with the common use
case of dynamically choosing ORDER BY it doesn't even catch other common
cases. You need to step back and find a way to prevent user input from ending
up in the query regardless of whether it's in a literal or not.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

--
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 12:25 AM, Thomas Mueller
<thomas.tom.mueller@gmail.com> wrote:

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

Damn, am I the only person who likes the idea?

To those suggesting that it's just treating the symptom: well of
course it is. But using e.g. Exec-Shield / PIE / stack protection
weren't bad ideas just because buffer overflows are the fault of the
application developer. And who wants to grep through every module they
install on their system every time they do an update just in case some
feature that they never use has added a bad query? Assuming they have
the source. PHP apps are notorious for it, of course, but it isn't
just them.

Now, I reckon the only way to sanely do it without mucking up people's
ad-hoc queries would be to have it as a permission that would default
to on, but be REVOKE-able. Then it can be revoked from the user/role
that $untrusted application connects as, but still allow people to get
in from a trusted account and get their hands dirty when they need to.

Would it catch ALL holes? No, as we saw in the order by case, and
there are probably others (not sure if I like the proposed solution
for that, btw). Would it catch a fair number? Absolutely.

Cheers

Tom

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

Hi,

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

of course you mean:

CREATE CONSTANT state_active TEXT VALUE 'active'; ?

interesting idea, would that mean PG complaints on queries

SELECT state_active FROM sometable ... because
state_active is already defined as constant?

What about local session variables? Usefull as well...

I think this is really a big effort :-)

Greets
Tino

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

"Tom Dunstan" <pgsql@tomd.cc> writes:
> Damn, am I the only person who likes the idea?

Just about. The reason that this idea isn't going anywhere is that its
cost/benefit ratio is untenably bad. Forbidding literals will break
absolutely every SQL-using application on the planet, and in many cases
fixing one's app to obey the rule would be quite painful (consider
especially complex multi-layered apps such as are common in the Java
world). In exchange for that, you get SQL injection protection that
has got a lot of holes in it, plus it stops protecting you at all
unless you are using a not-SQL-standard database. That tradeoff is
not happening, at least not in any nontrivial application.

Analogies such as PIE just point up the difference: for 99% of
applications, you can enable PIE without doing any more work than
adding a compile switch. If people were looking at major surgery
on most of their apps to enable it, the idea would never have gone
anywhere.

If you're going to ask people to do significant revision of their
apps to gain security, they're going to want it to work no matter
what database they run their apps against. This is why you need
a client-side solution such as tainting.

regards, tom lane


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

Tino Wildenhain wrote:
> Hi,
>
>> 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'.
>
> of course you mean:
>
> CREATE CONSTANT state_active TEXT VALUE 'active'; ?

Why does he mean that? Manifest constants are not typed in plenty of
languages.

>
> interesting idea, would that mean PG complaints on queries
>
> SELECT state_active FROM sometable ... because
> state_active is already defined as constant?

Right, this would be a major can of worms. The only way it could work, I
suspect, is by segregating the identifier space to remove ambiguity
between constants and other identifiers.

cheers

andrew


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

* Tom Lane <tgl@sss.pgh.pa.us> [080429 10:59]:
> "Tom Dunstan" <pgsql@tomd.cc> writes:
> > Damn, am I the only person who likes the idea?
>
> Just about. The reason that this idea isn't going anywhere is that its
> cost/benefit ratio is untenably bad. Forbidding literals will break
> absolutely every SQL-using application on the planet, and in many cases
> fixing one's app to obey the rule would be quite painful (consider
> especially complex multi-layered apps such as are common in the Java
> world). In exchange for that, you get SQL injection protection that
> has got a lot of holes in it, plus it stops protecting you at all
> unless you are using a not-SQL-standard database. That tradeoff is
> not happening, at least not in any nontrivial application.
>
> Analogies such as PIE just point up the difference: for 99% of
> applications, you can enable PIE without doing any more work than
> adding a compile switch. If people were looking at major surgery
> on most of their apps to enable it, the idea would never have gone
> anywhere.

I guess my database apps qualify as "nontrivial". I'm pretty sure that
I *could* enable something like this in all my web-facing apps *and* my
compiled C/C++ apps and not have any troubles.

And I happen to have programs/code that fail on PIE/execshield stuff.

I guess everything is relative.

That said, though *I* like the idea (and since I develop against
PostgreSQL 1st and use params for my queries I would consider it a nice
tool to "keep me honest"), I can easily see that the cost/benefit ratio
on this could be quite low and make it not worth the code/support
necessary.

> If you're going to ask people to do significant revision of their
> apps to gain security, they're going to want it to work no matter
> what database they run their apps against. This is why you need
> a client-side solution such as tainting.

Well, just because a tool is available doesn't mean people have to use
it. I mean, we have PostgreSQL, and we think that's worth it, even
though to use it, "everybody" has to do significant revision of their
apps.

a.

--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.

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

iD8DBQFIFz1YuVxNPsxNPScRAl9WAJ9bxDwbID2NUne73kKeOk 5G6daSHACgpxYc
S2resHdVQGKyHDHJlSCIimQ=
=mmNu
-----END PGP SIGNATURE-----

"Aidan Van Dyk" <aidan@highrise.ca> writes:

> That said, though *I* like the idea (and since I develop against
> PostgreSQL 1st and use params for my queries I would consider it a nice
> tool to "keep me honest"), I can easily see that the cost/benefit ratio
> on this could be quite low and make it not worth the code/support
> necessary.

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'

a) It makes things a lot clearer to when you call Execute($userid) which
values are actually the key user-provided data. In more complex queries it
can be quite confusing to have lots of parameters especially if the query
itself only makes sense if you know what values will be passed.

b) It allows the database to take advantage of statistics on "status" that
might not otherwise be possible.

Parameters are definitely the way to go for dynamic user data but for
constants which are actually an integral part of the query and not parameters
you're passing different values for each time it's actually clearer to include
them directly in the query.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

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

> If you're going to ask people to do significant revision of their
> apps to gain security, they're going to want it to work no matter
> what database they run their apps against. Â*This is why you need
> a client-side solution such as tainting.

Or if people are going to re-write their applications anyway, we'd want at
least a theoretically robust and flexible approach like libdejector, which
lets you identify which parts of a query structure are modifiable and
which are not.

For example, some applications need to replace whole phrases:

$criteria = "WHERE $var1 = '$var2'"

This is a very common approach for dynamic search screens, and really not
covered by placeholder approaches.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

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

> For example, some applications need to replace whole phrases:
>
> $criteria = "WHERE $var1 = '$var2'"
>
> This is a very common approach for dynamic search screens, and really not
> covered by placeholder approaches.

Python, again :

params = {
'column1': 10,
'column2': "a st'ring",
}

where = " AND ".join( "%s=%%s" % (key,value) for key,value in
params.items() )
cursor.execute( "SELECT * FROM table WHERE " + where, params )

I use the same approach (albeit more complicated) in PHP.

For complex expressions you can play with arrays etc, it is not that
difficult.
Or you just do :

$criteria = db_quote_query( "WHERE $var1 = %s", array( $var2 ))

using the function I posted earlier.

This supposes of course that $var1 which is the column name, comes from a
known source, and not user input.
In that case, $var1 will probably be the form field name, which means it
is specified by the programmer a few lines prior in the code.




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

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

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