Re: massive quotes?

Jon Jensen <jon(at)endpoint(dot)com> writes:

> On Thu, 10 Sep 2003, Doug McNaught wrote:
>
> > But Perl/DBI does escaping for you, so all you'd have to do is:

Only because the FE protocol is new and the DBD driver hasn't switched to
using it.

> > $sth = $dbh->prepare
> > ("CREATE FUNCTION foo(x text) RETURNS text AS ? LANGUAGE 'plpgsql'");
> > $sth->execute($function_body);
> >
> > where $function_body is the unescaped form of the function. So
> > there's no need for a COPY-style mechanism, you can use the current
> > CREATE FUNCTION syntax without having to escape everything yourself.

Well that will only work for as long as DBD actually does do the quoting and
interpolating. Presumably soon the driver will be converted to the new FE
protocol and ship the parameter out-of-band. Will the CREATE FUNCTION handle a
string argument shipping separately like this?

If so then all that has to happen is psql has to have a syntax that allows the
user to specify parameters.

Something like

CREATE FUNCTION foo(text) RETURNS text as ? LANGUAGE 'plpgsql'
$1<<EOF
.
.
.
EOF

then plsql would be able to read in the parameters into buffers without having
to dig inside looking for quotes. And execute the query passing the
parameters.

It could even support alternate sources of data for parameters:

CREATE FUNCTION foo(text) RETURNS text as ? LANGUAGE 'plpgsql'
$1<'foo.func'
;

Another nice thing about this is that it would help not just psql, but any
front-end using any driver that supports the new FE protocol.

It would also help any other query you want to do with big text parameters.
For example:

INSERT INTO message (header,body) values (?,?)
$1<<EOF
From: foo(at)bar(dot)baz
EOF

$2<<EOF
Big long message
EOF
;

I don't see any advantage to inventing a new quoting syntax for sql. In fact
doing it in sql would only increase the amount of parsing psql and other
front-ends would have to do and limit future options. They would still have to
parse to find the end of the statement which is the same parsing they have to
do to pass the arguments as separate parameters.

There's a security issue here too. If the data is already available in
alternate storage, such as in an external file or in a separate variable then
the last thing you want to have to do is interpolate the data into the sql
query only to have the backend parse it out all over again. One bug in the
interpolation or the parsing and you have a security hole.

Consider what happens if you do the above query but somebody passes a text of:

"
foo
EOF
;
DELETE FROM message
;
"

If the front-end is shipping it over to the backend whole the backend will
parse it and execute the DELETE statement. If the front-end is shipping it
over as parameters and receives this from a file or from a separate variable,
then it will be inserted as text into the table.

--
greg