Thread: Escaping quotes and double-quotes problem

Escaping quotes and double-quotes problem

From

Jean-Michel POURE

Date:

24 February 2002, 13:05:06

Dear Dave,

I discovered quote escape problems in pgAdmin2:

1) View definition
In pgAdmin2, try create a view "abc_def" with definition SELECT 'abc' ||
'def'. If you alter the view several times it will grow like:

SELECT ''abc'' || ''def'',
SELECT '''abc''' || '''def''',
SELECT ''''abc'''' || ''''def'''',
SELECT '''''abc''''' || '''''def'''''.
None of these queries work.

Single and double quotes should not be escaped.
CREATE VIEW "abc" AS SELECT 'abc' works
CREATE VIEW "abc" AS SELECT ''abc'' does not.

This is because definition is stored using OIDs and special mechanisms.

2) Functions source (PLpgSQL)
var := 'abc' || 'def' works
var := ''abc'' || ''def'' works
var := '''abc''' || '''def''' doesn't

Quotes and double quotes should be escaped using \.
single quote -> \'
double quote -> \"

There is no need to replace ' with '' and so on. This is because function
source is stored as text in PostgreSQL.

3) In both cases, dbSZ seems to be buggy.
I fixed the problem for function source. Not for view definition. Are there
cases in views where we should escape some characters? What is your opinion?

Cheers,
Jean-Michel

Re: Escaping quotes and double-quotes problem

From

Dave Page

Date:

24 February 2002, 18:01:58


> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 24 February 2002 15:05
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Escaping quotes and double-quotes problem
>
>
> Dear Dave,
>
> I discovered quote escape problems in pgAdmin2:
>
> 1) View definition
> In pgAdmin2, try create a view "abc_def" with definition
> SELECT 'abc' ||
> 'def'. If you alter the view several times it will grow like:
>
> SELECT ''abc'' || ''def'',
> SELECT '''abc''' || '''def''',
> SELECT ''''abc'''' || ''''def'''',
> SELECT '''''abc''''' || '''''def'''''.
> None of these queries work.
>
> Single and double quotes should not be escaped.
> CREATE VIEW "abc" AS SELECT 'abc' works
> CREATE VIEW "abc" AS SELECT ''abc'' does not.

This does not happen on my system. Have you broken something on yours? ;-)

> Quotes and double quotes should be escaped using \.
> single quote -> \'
> double quote -> \"

No, this doesn't always work. I've experimented with this extensively in the
past. here's a quick example:

helpdesk=# create table "Fred's \"Stuff\" Table" (id int4);
ERROR:  parser: parse error at or near "stuff"
ERROR:  parser: parse error at or near "stuff"
helpdesk=# create table "Fred's ""Stuff"" Table" (id int4);
CREATE
helpdesk=# \d
                  List of relations
              Name              |   Type   |  Owner
--------------------------------+----------+----------
 Fred's "Stuff" Table           | table    | postgres


> There is no need to replace ' with '' and so on. This is
> because function
> source is stored as text in PostgreSQL.

There is a need to do this with functions, because when you define them, the
quotes are doubled up because they function source is also within single
quotes. When PostgreSQL stores the source in pg_function.prosrc, it strips
out the additional quotes, so when we reconstruct the original SQL we must
put them back.

Having said that, the function update code doesn't do that (Add does) so
I've comitted a fix for that. *** Please remove your fix ***, it's not in
the right place and will probably cause excessive quoting in some cases.

> 3) In both cases, dbSZ seems to be buggy.
> I fixed the problem for function source. Not for view
> definition. Are there
> cases in views where we should escape some characters? What
> is your opinion?

As I said, views are fine here.

Cheers, Dave.

Re: Escaping quotes and double-quotes problem

From

Jean-Michel POURE

Date:

24 February 2002, 18:59:59

Le Dimanche 24 Février 2002 21:05, Dave Page a écrit :
> Having said that, the function update code doesn't do that (Add does) so
> I've comitted a fix for that.
Thanks. I checked-out from CVS to get your fix.

*** Please remove your fix ***, it's not in
> the right place and will probably cause excessive quoting in some cases.
Do you mean I should also remove my fix in Functions.cls?

Cheers,
Jean-Michel

Re: Escaping quotes and double-quotes problem

From

Dave Page

Date:

24 February 2002, 20:06:03


> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 24 February 2002 21:00
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Escaping quotes and
> double-quotes problem
>
>
> Le Dimanche 24 Février 2002 21:05, Dave Page a écrit :
> > Having said that, the function update code doesn't do that
> (Add does)
> > so I've comitted a fix for that.
> Thanks. I checked-out from CVS to get your fix.
>
> *** Please remove your fix ***, it's not in
> > the right place and will probably cause excessive quoting in some
> > cases.
> Do you mean I should also remove my fix in Functions.cls?

Yes please. The Source property should return the code that the user typed
in in the first place (i.e. with the doubled quotes). By keeping the Source
correct, then the SQL property will work with no modification, as will
frmFunction in pgAdmin which displays/updates the Source property, not the
SQL. If the fix is in the SQL property, then the quotes will be lost when
the user edits or displays the function.

Regards, Dave.