SEO

Hi Martijn,

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

The attack goes as follows: WHERE ORDER_ID = 1 yields 1 rows. WHERE
ORDER_ID = 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD
LIKE 'a%') yields 0 rows. OK that means that the admin password
doesn't start with an 'a'. If WHERE ORDER_ID = 1 AND (SELECT * FROM
USERS WHERE NAME='admin' AND PASSWORD LIKE 'b%') yields 1 row we know
the admin password starts with 'b'. For an average password length of
6 it takes 6 * 64 queries to get the password, plus some to get the
user name, plus maybe a few to get the table name and column name
correct.

> But as far as I'm concerned, the real killer is that it would make
> using any interactive query interface impossible.

No. Literals is an access right, and the interactive query tool may
have that access right. Let's say we have a APP_ROLE (for the
application itself) and a QUERY_ROLE. The default is literals are
enabled, that means the query tool can use literals. For the
application, the administrator may chooses to revoke the right to use
text and number literals using REVOKE LITERAL_TEXT, LITERAL_NUMBER
FROM APP_ROLE. Or the developer himself may want to try out if his
application is safe, and temporarily disables LITERAL_TEXT first. He
then runs the test cases and fixes the problems. Afterwards, he may
disable even LITERAL_NUMBER and try again. For production, maybe
literals are enabled.

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

* Gregory Stark <stark@enterprisedb.com> [080429 14:20]:
> "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.

These are all things to consider. I haven't (yet) needed a dynamic
query like that in my published apps because I would have a prepared
statement for the various status options, and my choice was to have a
couple prepared statements around instead of having a dynamic statement
thats re-planned on every query.

Most of my published applications *are* simple, and I tend to
consolidate as much of my "business logic" in the database as possible
and a "known" set of queries shared by all the related apps, relying
heavily on view, triggers, and functions, so the queries in my web-side
and C-side applications really are very simple and straight forward.

I purposely choose to have "simple static queries" in my apps. So a
mode which "rejects" queries with literals/constants in them would catch
"bugs" in my code. Those "bugs" really could be cosmetic, and still
"valid SQL" queries, but one of them could be a valid one which could be
an injection vector.

And so far the statistic/plan selection problems haven't made any of my
queries yet become performance problems...

Again, everything is relative.

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)

iD8DBQFIF36ouVxNPsxNPScRAhHJAJ9LTYNj9U2JjRmIXmVhrH ItJag6OQCcCKUJ
KzlRgOOtB4mfqcdfdbOsaYA=
=pFsk
-----END PGP SIGNATURE-----

Thomas Mueller wrote:
>> 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.
>
>
>

What is not optional is the probably maintenance complexity of this scheme.

Moreover, it seems unlikely that it will even cover the field. A partial
cloak might indeed be worse than none, in that it will give some
developers an illusion of having security.

Before we embarked on such an enterprise, I would personally want to see
fairly loud clamor from our user base for it.

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

On Tue, Apr 29, 2008 at 04:33:01PM -0400, Andrew Dunstan wrote:

> Moreover, it seems unlikely that it will even cover the field. A partial
> cloak might indeed be worse than none, in that it will give some developers
> an illusion of having security.

I think this is a really important point, and one that isn't getting
enough attention in this discussion. Half a security measure is
almost always worse than none at all, exactly because people stop
thinking they have to worry about that area of security at all. I
think without a convincing argument that the proposal will even come
close to covering most SQL injection cases, it's a bad idea.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

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

Thomas,

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

Please don't let the debate over this break your enthusiasm for improving
PostgreSQL security. We really care about security, which is why we want
to run your proposal throught the gauntlet.

You said you've done this for H2. Isn't H2 only accessable through Java,
though? How many people are using literals in Java?

And, as of this week MSSQL already looks really bad. 300,000 worm-infected
servers, and counting!

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

[I know, I know, bad form]

On Tue, Apr 29, 2008 at 04:55:21PM -0400, Andrew Sullivan wrote:
> thinking they have to worry about that area of security at all. I
> think without a convincing argument that the proposal will even come
> close to covering most SQL injection cases, it's a bad idea.

To be perfectly clear, I also think that the reverse is true: if a
fairly complete design was demonstrated to be possible such that it
covered just about every case, I'd be all for it. (I sort of like the
suggestion up-thread, myself, which is to have a GUC that disables
multi-statement commands. That'd probably cover a huge number of
cases, and combined with some sensible quoting rules in client
libraries, would quite possibly be enough.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

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

> (I sort of like the
> suggestion up-thread, myself, which is to have a GUC that disables
> multi-statement commands. *That'd probably cover a huge number of
> cases, and combined with some sensible quoting rules in client
> libraries, would quite possibly be enough.)

MySQL did this already.

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

On Tue, 29 Apr 2008 22:18:48 +0200 Thomas Mueller wrote:

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

I don't think so.
Given the fact that enabling this feature by default would break almost
all applications, you have to disable this by default. No use here
because almost nobody will know about it. Oh, and i can see the
headlines: "New PostgreSQL feature breaks 99% applications".


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

The developers and admins who know about this feature and want to use
it are also the developers and admins who know about SQL injections.
Eventually the code quality produced by this ppl is higher than
average and less likely to have such basic faults.


> Even if the feature is not enabled, it's still good to have it.

Huh? How this?
Just because one can say "We have a feature against SQL injections"
which will not be used by literally anyone?


Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

On Wed, Apr 30, 2008 at 1:48 AM, Thomas Mueller <
thomas.tom.mueller@gmail.com> wrote:

> Hi,
>
> For PostgreSQL the 'disable literals' feature would be great
> publicity:
>
>
'publicity' is something this community does not crave for, at least not
feature wise. If that were the case we would have had a million half-baked
features in Postgres by now.

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

"Josh Berkus" <josh@agliodbs.com> writes:

>> (I sort of like the
>> suggestion up-thread, myself, which is to have a GUC that disables
>> multi-statement commands. *That'd probably cover a huge number of
>> cases, and combined with some sensible quoting rules in client
>> libraries, would quite possibly be enough.)
>
> MySQL did this already.

Did you guys miss Tom's comment up-thread? Postgres already does this if you
use PQExecParams().

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

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