Re: Escape handling in COPY, strings, psql

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > I do support gradually phasing out backslash escapes in standard string
> > literals in the interest of portability. Most of the current escape
> > sequences are of limited value anyway. Let's think about ways to get
> > there:
>
> I really don't think there is any way to get there without creating
> gaping security holes in all kinds of client code :-(. If we change
> the escaping rules, then a client that is expecting some other rule
> than happens to be in force will be subject to trivial SQL-injection
> attacks. This will make the autocommit fiasco pale by comparison ...

I looked at PQescapeString() and fortunately it escapes single quotes
by doing double-single quotes, not by using a backslash. This was
probably chosen for standards compliance.

Basically, I think our current behavior is not sustainable. I think we
are going to need to do something, and I think we should consider a
solution now rather than later. I don't think we can be as serious a
contender for portability without some kind of solution.

I am thinking we should first tell people in 8.1 that they should start
using only double-single quotes, and perhaps support the ESCAPE phrase
as a no-op, and then consider some kind of solution in 8.2 or later.

I don't think fixing this is going to be a huge security problem, but it
might be a small one. The good thing is that double-single quotes work,
so if people use only that for quote escaping, if you forget the ESCAPE
clause, you just get literal backslashes, not a security problem.

I ran the following test:

test=> select $$\$$;
?column?
----------
\
(1 row)

test=> create table test (x TEXT);
CREATE TABLE
test=> INSERT INTO test VALUES ($$\$$);
INSERT 0 1
test=> SELECT * FROM test;
x
---
\
(1 row)

and the good news is that output of backslashes is fine --- it is just
input that is the issue, and the security problem is only using \',
which we would have to tell people to avoid and start using only ''.

I think we can tell people in 8.1 that they should modify their
applications to only use '', and that \' might be a security problem in
the future. If we get to that then using ESC or not only affects input
of values and literal backslashes being entered, and my guess is that
90% of the backslash entries that want escaping are literal in the
application and not supplied by program variables. In fact, if we
disable backslash by default then strings coming in only have to deal
with single quotes (like other databases) and the system is more secure
because there is no special backslash handling by default.

> > For COPY, we would probably have to use a flag in the COPY command
> > itself either way (like already done for NULL AS).
>
> The spec-compatibility argument for removing escapes does not apply to
> COPY at all, so I see no need to fool with the COPY definition in any
> case.

Agreed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073