SEO

**Andrew Sullivan wrote:
> On Thu, May 01, 2008 at 06:33:07PM +0200, PFC wrote:
>
> > But it's true that preventing multi-statements adds a layer of
> > idiot-proofness... a rather thin layer...
>
> As I already said in a previous remark in this thread, I don't really
> like partial security solutions.
>
> What the "no multi-statement SQL" switch adds is a complete protection
> against _one class_ of injection attacks. What is nice about it is
> that it completely eliminates that class of attacks, so they are no
> longer something one needs to worry about.
>
> They do not, of course, prevent every kind of injection attack. I
> think the thread has already had ample evidence that such complete
> prevention is either impractical to implement, too costly to existing
> applications, too limiting, not actually effective (i.e. not really
> complete prevention), or some combination of the above.
>
> That's not an argument that the simple change that is effective for
> only one class of attacks is a bad idea. Making the battlefield
> smaller is one thing one can do to decrease one's exposure to attack.

Andrew, Tom, please just add this feature and let those who don't
like it just send their complains to /dev/null. To be honest, I'm
surprised it has not appeared sooner...I was thinking of hacking
on postgres to implement it then I found this thread. I've already
started looking at writing a proxy for postgres with the first
important task to be converting multiple statements in the SQL TCP
data stream to a series of spaces...for those that can't upgrade
to a version where they can turn such a knob on.

Also, I think the "no-multistatement SQL" should default to on.

Why?

Because interacting with the database is always through an action
that you do and if you're being half way intelligent about it, you
are always checking that each action succeeded before going on to
the next.

I think one of the more important exercises is to examine PHP and
see if it bundles up any statements into more than one call to
prepare or execute in perl.

As with all security solutions, the end result is made up of many
layers, there is rarely just one big gate. The more layers we have,
regardless of how thick or thing they are, the better equipped we
are to deal with attacks.

Cheers,
Darren



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

Darren Reed <darrenr+postgres@fastmail.net> writes:
> Also, I think the "no-multistatement SQL" should default to on.

Not happening, for backwards-compatibility reasons.

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

Darren Reed wrote:

> Because interacting with the database is always through an action
> that you do and if you're being half way intelligent about it, you
> are always checking that each action succeeded before going on to
> the next.

Hmm, it won't be pretty for the drivers that do PQexec("COMMIT; BEGIN").
The driver will think that it's in a transaction when in fact the second
command in the string has been ignored, and so it's not ...

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Hmm, it won't be pretty for the drivers that do PQexec("COMMIT; BEGIN").
> The driver will think that it's in a transaction when in fact the second
> command in the string has been ignored, and so it's not ...

Any driver that is doing that should be shot.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805021325
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkgbTn4ACgkQvJuQZxSWSshKwwCfewZyRy/b6PvJrQn6pTlgsSDb
MeQAoM4sajlNKU17z3tVDqVTfqcyLf9N
=Fj0e
-----END PGP SIGNATURE-----



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

alvherre@commandprompt.com (Alvaro Herrera) writes:
> Darren Reed wrote:
>
>> Because interacting with the database is always through an action
>> that you do and if you're being half way intelligent about it, you
>> are always checking that each action succeeded before going on to
>> the next.
>
> Hmm, it won't be pretty for the drivers that do PQexec("COMMIT; BEGIN").
> The driver will think that it's in a transaction when in fact the second
> command in the string has been ignored, and so it's not ...

We have worked pretty hard around here to expunge use of drivers that
do this sort of thing. (Cough, cough, "dbrow"...)

Recent versions of PostgreSQL don't suffer too badly, but back in the
7.2/7.4 days, we had applications that left transactions open "<IDLE>
in transaction" for days at a time (if a user quit using the web app
without expressly logging out), with _atrocious_ results.

Andrew Sullivan recently had some choice words about the merits of
ENUM; I think the same applies to drivers that do
PQexec("COMMIT;BEGIN")...
--
output = ("cbbrowne" "@" "linuxfinances.info")
http://www3.sympatico.ca/cbbrowne/advocacy.html
:FATAL ERROR -- ILLEGAL ERROR

On Fri, May 02, 2008 at 03:58:01PM -0400, Chris Browne wrote:

> Andrew Sullivan recently had some choice words about the merits of
> ENUM; I think the same applies to drivers that do
> PQexec("COMMIT;BEGIN")...

Oh, heaven. I can at least think of ways to use ENUM such that you
can justify the trade-off. I can think of no excuse whatever for
PQexec("COMMIT; BEGIN"). That's just lazy and sloppy.

Note also that more recent releases, concurrent with the improvements
to the drivers, also reduce the impact of this sort of database misuse
slightly.

But really, people who are doing that sort of thing have no excuse for
themselves. They should be relegated to the same circle of hell as
people who think it's a good plan to write a crappy schema the first
time, because you can always optimise later.

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

Andrew Sullivan <ajs@commandprompt.com> writes:
> Oh, heaven. I can at least think of ways to use ENUM such that you
> can justify the trade-off. I can think of no excuse whatever for
> PQexec("COMMIT; BEGIN"). That's just lazy and sloppy.

> Note also that more recent releases, concurrent with the improvements
> to the drivers, also reduce the impact of this sort of database misuse
> slightly.

Actually, as of 8.3 I think the impact is zero, because of the lazy
XID allocation changes. It's still sloppy programming though.

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

This might seem sillly, but...

....are comments going to be considered statements for the purpose of
this knob?
(hoping the anwer is "yes")

Darren


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

Darren Reed <darrenr@fastmail.net> writes:
> This might seem sillly, but...
> ...are comments going to be considered statements for the purpose of
> this knob?
> (hoping the anwer is "yes")

Are you trying to say we should forbid comments? No thank you.

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

Tom Lane wrote:
> Darren Reed <darrenr@fastmail.net> writes:
>
>> This might seem sillly, but...
>> ...are comments going to be considered statements for the purpose of
>> this knob?
>> (hoping the anwer is "yes")
>>
>
> Are you trying to say we should forbid comments? No thank you.
>

No.

When psql (for example) parses this:
COMMIT;BEGIN;-- Hi
it will generate individual commands with postgres (the server) over
the connection. ie. psql sends "COMMIT;" waits, then sends 'BEGIN;",
waits, etc.

When you do this in perl:
$db->prepare("COMMIT;BEGIN;--");
one single command string (the entire string above) is sent to the server.

How often do people code comments into prepare statements in perl
or the equivalent in java, ruby, etc?

Do you put comments in your perl prepare statements?

If comments count as a statement, at the server end, then the
multi-statement
disabling also disables another attack vector - slightly: you can no
longer attack
using this as your username:
"' OR 1=1;--"

This raises another point - preventing muilti-statement attacks work so
long as the "hacker string" isn't broken up into multiple statements by the
client side. Passing said string to /bin/sh, which then passes it to psql
would successfully enable the attack even with this knob turned on. But
few people are likely to be using a design that is that slow.

Darren


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