Escape handling in strings

Started by Bruce Momjianalmost 21 years ago27 messageshackers

bruce@momjian.us

almost 21 years ago

A summary of my proposal to add a new E'' string for escape and have
non-E escapes not handle backslashes specially is at:

http://candle.pha.pa.us/cgi-bin/pgescape

Attached is a patch that emits warnings for \ and \', perhaps for 8.1.
The change to scan.l is the place this is done. The rest of the patch
is adjustments to prevent our own code from generating warnings. It
shows a good example of how users would have to change their code.

It passes all regression tests, contrib regression, and initdb runs
without warning.

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

Christopher Kings-Lynne

chriskl@familyhealth.com.au

almost 21 years ago

In reply to: Bruce Momjian (#1)

Re: Escape handling in strings

I'm still really iffy about this. I think it will really hurt pgsql due
to backward compatibility :(

(If I'm understanding how the proposed change works...)

Chris

Bruce Momjian wrote:

Show quoted text

A summary of my proposal to add a new E'' string for escape and have
non-E escapes not handle backslashes specially is at:

http://candle.pha.pa.us/cgi-bin/pgescape

It passes all regression tests, contrib regression, and initdb runs
without warning.

------------------------------------------------------------------------

Index: contrib/tsearch2/expected/tsearch2.out
===================================================================
RCS file: /cvsroot/pgsql/contrib/tsearch2/expected/tsearch2.out,v
retrieving revision 1.11
diff -c -c -r1.11 tsearch2.out
*** contrib/tsearch2/expected/tsearch2.out	14 Sep 2004 03:58:54 -0000	1.11
--- contrib/tsearch2/expected/tsearch2.out	16 Jun 2005 01:36:54 -0000
***************
*** 47,83 ****
'1' '2'
(1 row)

! SELECT '\'1 2\''::tsvector;
tsvector
----------
'1 2'
(1 row)

! SELECT '\'1 \\\'2\''::tsvector;
tsvector
----------
'1 \'2'
(1 row)

! SELECT '\'1 \\\'2\'3'::tsvector;
tsvector
-------------
'3' '1 \'2'
(1 row)

! SELECT '\'1 \\\'2\' 3'::tsvector;
tsvector
-------------
'3' '1 \'2'
(1 row)

! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
tsvector
------------------
'4' ' 3' '1 \'2'
(1 row)

! select '\'w\':4A,3B,2C,1D,5 a:8';
?column?        
-----------------------
'w':4A,3B,2C,1D,5 a:8
--- 47,83 ----
'1' '2'
(1 row)

! SELECT '''1 2'''::tsvector;
tsvector
----------
'1 2'
(1 row)

! SELECT E'''1 \\''2'''::tsvector;
tsvector
----------
'1 \'2'
(1 row)

! SELECT E'''1 \\''2''3'::tsvector;
tsvector
-------------
'3' '1 \'2'
(1 row)

! SELECT E'''1 \\''2'' 3'::tsvector;
tsvector
-------------
'3' '1 \'2'
(1 row)

! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
tsvector
------------------
'4' ' 3' '1 \'2'
(1 row)

! select '''w'':4A,3B,2C,1D,5 a:8';
?column?
-----------------------
'w':4A,3B,2C,1D,5 a:8
***************
*** 126,138 ****
'1'
(1 row)

! SELECT '\'1 2\''::tsquery;
tsquery
---------
'1 2'
(1 row)

! SELECT '\'1 \\\'2\''::tsquery;
tsquery 
---------
'1 \'2'
--- 126,138 ----
'1'
(1 row)

! SELECT '''1 2'''::tsquery;
tsquery
---------
'1 2'
(1 row)

! SELECT E'''1 \\''2'''::tsquery;
tsquery
---------
'1 \'2'
***************
*** 330,342 ****
'1' & '2' & '4' & ( '5' | !'6' )
(1 row)

! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
tsquery
------------------------------------------
'1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
(1 row)

! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';
?column?                 
------------------------------------------
'the wether':dc & ' sKies ':BC & a:d b:a
--- 330,342 ----
'1' & '2' & '4' & ( '5' | !'6' )
(1 row)

! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
tsquery
------------------------------------------
'1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
(1 row)

! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
?column?
------------------------------------------
'the wether':dc & ' sKies ':BC & a:d b:a
***************
*** 382,388 ****
23 | entity | HTML Entity
(23 rows)

! select * from parse('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?  ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 
<i <b> wow  < jqw <> qwerty');
tokid |                token                 
--- 382,388 ----
23 | entity       | HTML Entity
(23 rows)

! select * from parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty');
tokid | token
***************
*** 529,535 ****
1 | qwerty
(138 rows)

! SELECT to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?  ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 
<i <b> wow  < jqw <> qwerty');
to_tsvector                                                                                                                                                                                                                                                                                                                                                                                                                                                
--- 529,535 ----
1 | qwerty
(138 rows)

! SELECT to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty');
to_tsvector
***************
*** 543,549 ****
2
(1 row)

! SELECT length(to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?  ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 
<i <b> wow  < jqw <> qwerty'));
length 
--- 543,549 ----
2
(1 row)

! SELECT length(to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty'));
length
***************
*** 563,569 ****
'qwe' & 'skies'
(1 row)

! select to_tsquery('default', '\'the wether\':dc & \'           sKies \':BC ');
to_tsquery       
------------------------
'wether':CD & 'sky':BC
--- 563,569 ----
'qwe' & 'skies'
(1 row)

! select to_tsquery('default', '''the wether'':dc & '' sKies '':BC ');
to_tsquery
------------------------
'wether':CD & 'sky':BC
***************
*** 729,735 ****
(1 row)

drop trigger tsvectorupdate on test_tsvector;
! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
create trigger tsvectorupdate before update or insert on test_tsvector
for each row execute procedure tsearch2(a, wow, t);
insert into test_tsvector (t) values ('345 qwerty');
--- 729,735 ----
(1 row)

drop trigger tsvectorupdate on test_tsvector;
! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
create trigger tsvectorupdate before update or insert on test_tsvector
for each row execute procedure tsearch2(a, wow, t);
insert into test_tsvector (t) values ('345 qwerty');
Index: contrib/tsearch2/sql/tsearch2.sql
===================================================================
RCS file: /cvsroot/pgsql/contrib/tsearch2/sql/tsearch2.sql,v
retrieving revision 1.7
diff -c -c -r1.7 tsearch2.sql
*** contrib/tsearch2/sql/tsearch2.sql	28 Jun 2004 16:18:56 -0000	1.7
--- contrib/tsearch2/sql/tsearch2.sql	16 Jun 2005 01:36:54 -0000
***************
*** 12,23 ****
SELECT ' 1'::tsvector;
SELECT ' 1 '::tsvector;
SELECT '1 2'::tsvector;
! SELECT '\'1 2\''::tsvector;
! SELECT '\'1 \\\'2\''::tsvector;
! SELECT '\'1 \\\'2\'3'::tsvector;
! SELECT '\'1 \\\'2\' 3'::tsvector;
! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
! select '\'w\':4A,3B,2C,1D,5 a:8';
select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
--- 12,23 ----
SELECT ' 1'::tsvector;
SELECT ' 1 '::tsvector;
SELECT '1 2'::tsvector;
! SELECT '''1 2'''::tsvector;
! SELECT E'''1 \\''2'''::tsvector;
! SELECT E'''1 \\''2''3'::tsvector;
! SELECT E'''1 \\''2'' 3'::tsvector;
! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
! select '''w'':4A,3B,2C,1D,5 a:8';
select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
***************
*** 28,35 ****
SELECT '1 '::tsquery;
SELECT ' 1'::tsquery;
SELECT ' 1 '::tsquery;
! SELECT '\'1 2\''::tsquery;
! SELECT '\'1 \\\'2\''::tsquery;
SELECT '!1'::tsquery;
SELECT '1|2'::tsquery;
SELECT '1|!2'::tsquery;
--- 28,35 ----
SELECT '1 '::tsquery;
SELECT ' 1'::tsquery;
SELECT ' 1 '::tsquery;
! SELECT '''1 2'''::tsquery;
! SELECT E'''1 \\''2'''::tsquery;
SELECT '!1'::tsquery;
SELECT '1|2'::tsquery;
SELECT '1|!2'::tsquery;
***************
*** 62,92 ****
SELECT '1&2&4&5&6'::tsquery;
SELECT '1&(2&(4&(5|6)))'::tsquery;
SELECT '1&(2&(4&(5|!6)))'::tsquery;
! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';

select lexize('simple', 'ASD56 hsdkf');
select lexize('en_stem', 'SKIES Problems identity');

select * from token_type('default');
! select * from parse('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty');

! SELECT to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty');

SELECT length(to_tsvector('default', '345 qw'));

! SELECT length(to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty'));

select to_tsquery('default', 'qwe & sKies '); 
select to_tsquery('simple', 'qwe & sKies '); 
! select to_tsquery('default', '\'the wether\':dc & \'           sKies \':BC ');
select to_tsquery('default', 'asd&(and|fghj)');
select to_tsquery('default', '(asd&and)|fghj');
select to_tsquery('default', '(asd&!and)|fghj');
--- 62,92 ----
SELECT '1&2&4&5&6'::tsquery;
SELECT '1&(2&(4&(5|6)))'::tsquery;
SELECT '1&(2&(4&(5|!6)))'::tsquery;
! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';

select lexize('simple', 'ASD56 hsdkf');
select lexize('en_stem', 'SKIES Problems identity');

select * from token_type('default');
! select * from parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
 wow < jqw <> qwerty');

SELECT length(to_tsvector('default', '345 qw'));

select to_tsquery('default', 'qwe & sKies ');
select to_tsquery('simple', 'qwe & sKies ');
! select to_tsquery('default', '''the wether'':dc & '' sKies '':BC ');
select to_tsquery('default', 'asd&(and|fghj)');
select to_tsquery('default', '(asd&and)|fghj');
select to_tsquery('default', '(asd&!and)|fghj');
***************
*** 135,141 ****
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

drop trigger tsvectorupdate on test_tsvector;
! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
create trigger tsvectorupdate before update or insert on test_tsvector
for each row execute procedure tsearch2(a, wow, t);
insert into test_tsvector (t) values ('345 qwerty');
--- 135,141 ----
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

drop trigger tsvectorupdate on test_tsvector;
! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
create trigger tsvectorupdate before update or insert on test_tsvector
for each row execute procedure tsearch2(a, wow, t);
insert into test_tsvector (t) values ('345 qwerty');
Index: src/backend/parser/scan.l
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/scan.l,v
retrieving revision 1.125
diff -c -c -r1.125 scan.l
*** src/backend/parser/scan.l	15 Jun 2005 16:28:06 -0000	1.125
--- src/backend/parser/scan.l	16 Jun 2005 01:36:55 -0000
***************
*** 49,54 ****
--- 49,55 ----

static int xcdepth = 0; /* depth of nesting in slash-star comments */
static char *dolqstart; /* current $foo$ quote start string */
+ static bool warn_on_escape;

/*
* literalbuf is used to accumulate literal values when multiple rules
***************
*** 64,69 ****
--- 65,71 ----
static void addlit(char *ytext, int yleng);
static void addlitchar(unsigned char ychar);
static char *litbufdup(void);
+ static void check_escape_warning(void);

/*
* When we parse a token that requires multiple lexer rules to process,
***************
*** 185,190 ****
--- 187,196 ----
/* National character */
xnstart			[nN]{quote}

+ /* Quote string does not warn about escapes */
+ xestart			[eE]{quote}
+ xeinside		[^']*
+ 
/* Extended quote
* xqdouble implements embedded quote, ''''
*/
***************
*** 410,415 ****
--- 416,428 ----
}

{xqstart}		{
+ 					warn_on_escape = true;
+ 					token_start = yytext;
+ 					BEGIN(xq);
+ 					startlit();
+ 				}
+ {xestart}		{
+ 					warn_on_escape = false;
token_start = yytext;
BEGIN(xq);
startlit();
***************
*** 428,441 ****
--- 441,468 ----
addlit(yytext, yyleng);
}
<xq>{xqescape}  {
+ 					if (yytext[1] == '\'')
+ 					{
+ 						if (warn_on_escape)
+ 							ereport(WARNING,
+ 								(errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
+ 								 errmsg("Invalid use of \' in a normal string"),
+ 								 errhint("Use '' to place quotes in strings, or use E-type strings.")));
+ 					}
+ 					else
+ 						check_escape_warning();
addlitchar(unescape_single_char(yytext[1]));
}
<xq>{xqoctesc}  {
unsigned char c = strtoul(yytext+1, NULL, 8);
+ 
+ 					check_escape_warning();
addlitchar(c);
}
<xq>{xqhexesc}  {
unsigned char c = strtoul(yytext+2, NULL, 16);
+ 
+ 					check_escape_warning();
addlitchar(c);
}
<xq>{quotecontinue} {
***************
*** 810,812 ****
--- 837,850 ----
return c;
}
}
+ 
+ static void
+ check_escape_warning(void)
+ {
+ 	if (warn_on_escape)
+ 		ereport(WARNING,
+ 			(errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
+ 			 errmsg("Invalid use of escapes in a normal string"),
+ 			 errhint("Use E-type strings for escapes, e.g. E'\\r\\n'.")));
+ 	warn_on_escape = false;	/* warn only once per string */
+ }
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -c -c -r1.83 initdb.c
*** src/bin/initdb/initdb.c	30 Apr 2005 08:08:51 -0000	1.83
--- src/bin/initdb/initdb.c	16 Jun 2005 01:36:57 -0000
***************
*** 1688,1694 ****
char	  **priv_lines;
static char *privileges_setup[] = {
"UPDATE pg_class "
! 		"  SET relacl = '{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
"  WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
"GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
"GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
--- 1688,1694 ----
char	  **priv_lines;
static char *privileges_setup[] = {
"UPDATE pg_class "
! 		"  SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
"  WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
"GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
"GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
***************
*** 1952,1959 ****

for (i = 0, j = 0; i < len; i++)
{
! 		if (src[i] == '\'' || src[i] == '\\')
result[j++] = '\\';
result[j++] = src[i];
}
result[j] = '\0';
--- 1952,1961 ----

for (i = 0, j = 0; i < len; i++)
{
! 		if (src[i] == '\\')
result[j++] = '\\';
+ 		if (src[i] == '\'')		/* ANSI standard, '' */
+ 			result[j++] = '\'';
result[j++] = src[i];
}
result[j] = '\0';
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.59
diff -c -c -r1.59 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c	18 Apr 2005 23:47:52 -0000	1.59
--- src/bin/pg_dump/pg_dumpall.c	16 Jun 2005 01:36:57 -0000
***************
*** 538,544 ****
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl "
"FROM pg_catalog.pg_tablespace "
! 					   "WHERE spcname NOT LIKE 'pg\\_%'");

if (PQntuples(res) > 0)
printf("--\n-- Tablespaces\n--\n\n");
--- 538,544 ----
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl "
"FROM pg_catalog.pg_tablespace "
! 					   "WHERE spcname NOT LIKE E'pg\\_%'");

if (PQntuples(res) > 0)
printf("--\n-- Tablespaces\n--\n\n");
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.117
diff -c -c -r1.117 describe.c
*** src/bin/psql/describe.c	14 Jun 2005 23:59:31 -0000	1.117
--- src/bin/psql/describe.c	16 Jun 2005 01:36:58 -0000
***************
*** 1766,1772 ****
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
"       ON n.nspowner=u.usesysid\n"
! 				 "WHERE	(n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
"		 n.nspname = (pg_catalog.current_schemas(true))[1])\n");		/* temp schema is first */

processNamePattern(&buf, pattern, true, false,
--- 1766,1772 ----
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
"       ON n.nspowner=u.usesysid\n"
! 				 "WHERE	(n.nspname NOT LIKE E'pg\\\\_temp\\\\_%%' OR\n"
"		 n.nspname = (pg_catalog.current_schemas(true))[1])\n");		/* temp schema is first */

processNamePattern(&buf, pattern, true, false,
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.367
diff -c -c -r1.367 pg_proc.h
*** src/include/catalog/pg_proc.h	14 Jun 2005 21:04:41 -0000	1.367
--- src/include/catalog/pg_proc.h	16 Jun 2005 01:37:03 -0000
***************
*** 1461,1467 ****
DESCR("greater-than-or-equal");
DATA(insert OID = 1157 (  timestamptz_gt   PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_ timestamp_gt - _null_ ));
DESCR("greater-than");
! DATA(insert OID = 1158 (  to_timestamp	   PGNSP PGUID 14 f f t f i 1 1184 "701" _null_	_null_ _null_ "select (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)" - _null_ ));
DESCR("convert UNIX epoch to timestamptz");
DATA(insert OID = 1159 (  timezone		   PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_  timestamptz_zone - _null_ ));
DESCR("adjust timestamp to new time zone");
--- 1461,1467 ----
DESCR("greater-than-or-equal");
DATA(insert OID = 1157 (  timestamptz_gt   PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_ timestamp_gt - _null_ ));
DESCR("greater-than");
! DATA(insert OID = 1158 (  to_timestamp	   PGNSP PGUID 14 f f t f i 1 1184 "701" _null_	_null_ _null_ "select (''epoch''::timestamptz + $1 * ''1 second''::interval)" - _null_ ));
DESCR("convert UNIX epoch to timestamptz");
DATA(insert OID = 1159 (  timezone		   PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_  timestamptz_zone - _null_ ));
DESCR("adjust timestamp to new time zone");
***************
*** 1541,1547 ****

DATA(insert OID = 1215 ( obj_description PGNSP PGUID 14 f f t f s 2 25 "26 19" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class where relname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ ));
DESCR("get description for object id and catalog name");
! DATA(insert OID = 1216 ( col_description PGNSP PGUID 14 f f t f s 2 25 "26 23" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = \'pg_catalog.pg_class\'::regclass and objsubid = $2" - _null_ ));
DESCR("get description for table column");

DATA(insert OID = 1217 (  date_trunc	   PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_ timestamptz_trunc - _null_ ));
--- 1541,1547 ----

DATA(insert OID = 1215 ( obj_description PGNSP PGUID 14 f f t f s 2 25 "26 19" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class where relname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ ));
DESCR("get description for object id and catalog name");
! DATA(insert OID = 1216 ( col_description PGNSP PGUID 14 f f t f s 2 25 "26 23" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = ''pg_catalog.pg_class''::regclass and objsubid = $2" - _null_ ));
DESCR("get description for table column");

DATA(insert OID = 1217 (  date_trunc	   PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_ timestamptz_trunc - _null_ ));
***************
*** 2185,2193 ****
DESCR("return portion of string");
DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_	translate - _null_ ));
DESCR("map a set of character appearing in string");
! DATA(insert OID =  879 (  lpad		   PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.lpad($1, $2, \' \')" - _null_ ));
DESCR("left-pad string to length");
! DATA(insert OID =  880 (  rpad		   PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.rpad($1, $2, \' \')" - _null_ ));
DESCR("right-pad string to length");
DATA(insert OID =  881 (  ltrim		   PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_  ltrim1 - _null_ ));
DESCR("trim spaces from left end of string");
--- 2185,2193 ----
DESCR("return portion of string");
DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_	translate - _null_ ));
DESCR("map a set of character appearing in string");
! DATA(insert OID =  879 (  lpad		   PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.lpad($1, $2, '' '')" - _null_ ));
DESCR("left-pad string to length");
! DATA(insert OID =  880 (  rpad		   PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.rpad($1, $2, '' '')" - _null_ ));
DESCR("right-pad string to length");
DATA(insert OID =  881 (  ltrim		   PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_  ltrim1 - _null_ ));
DESCR("trim spaces from left end of string");
Index: src/test/regress/expected/arrays.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/arrays.out,v
retrieving revision 1.25
diff -c -c -r1.25 arrays.out
*** src/test/regress/expected/arrays.out	22 Apr 2005 21:58:32 -0000	1.25
--- src/test/regress/expected/arrays.out	16 Jun 2005 01:37:04 -0000
***************
*** 436,442 ****
ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
select '{{},{}}'::text[];
ERROR:  malformed array literal: "{{},{}}"
! select '{{1,2},\\{2,3}}'::text[];
ERROR:  malformed array literal: "{{1,2},\{2,3}}"
select '{{"1 2" x},{3}}'::text[];
ERROR:  malformed array literal: "{{"1 2" x},{3}}"
--- 436,442 ----
ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
select '{{},{}}'::text[];
ERROR:  malformed array literal: "{{},{}}"
! select E'{{1,2},\\{2,3}}'::text[];
ERROR:  malformed array literal: "{{1,2},\{2,3}}"
select '{{"1 2" x},{3}}'::text[];
ERROR:  malformed array literal: "{{"1 2" x},{3}}"
Index: src/test/regress/expected/copy2.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/copy2.out,v
retrieving revision 1.21
diff -c -c -r1.21 copy2.out
*** src/test/regress/expected/copy2.out	13 May 2005 06:33:40 -0000	1.21
--- src/test/regress/expected/copy2.out	16 Jun 2005 01:37:04 -0000
***************
*** 49,55 ****
-- various COPY options: delimiters, oids, NULL string
COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
-- check results of copy in
SELECT * FROM x;
a   | b  |     c      |   d    |          e           
--- 49,55 ----
-- various COPY options: delimiters, oids, NULL string
COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
-- check results of copy in
SELECT * FROM x;
a   | b  |     c      |   d    |          e           
***************
*** 176,183 ****
col1 text,
col2 text
);
! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
! INSERT INTO y VALUES ('It is "perfect".','\t');
INSERT INTO y VALUES ('', NULL);
COPY y TO stdout WITH CSV;
"Jackson, Sam",\h
--- 176,183 ----
col1 text,
col2 text
);
! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
! INSERT INTO y VALUES ('It is "perfect".',E'\t');
INSERT INTO y VALUES ('', NULL);
COPY y TO stdout WITH CSV;
"Jackson, Sam",\h
***************
*** 187,193 ****
Jackson, Sam|\h
It is "perfect".|	
''|
! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",
--- 187,193 ----
Jackson, Sam|\h
It is "perfect".|	
''|
! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",
Index: src/test/regress/expected/int8.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/int8.out,v
retrieving revision 1.9
diff -c -c -r1.9 int8.out
*** src/test/regress/expected/int8.out	4 Oct 2004 14:42:47 -0000	1.9
--- src/test/regress/expected/int8.out	16 Jun 2005 01:37:04 -0000
***************
*** 280,286 ****
|  -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
(5 rows)

! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
to_char_16 |                          to_char                          
------------+-----------------------------------------------------------
|       text      9999     "text between quote marks"   456
--- 280,286 ----
|  -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
(5 rows)

! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
to_char_16 |                          to_char                          
------------+-----------------------------------------------------------
|       text      9999     "text between quote marks"   456
Index: src/test/regress/expected/numeric.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/numeric.out,v
retrieving revision 1.16
diff -c -c -r1.16 numeric.out
*** src/test/regress/expected/numeric.out	28 Oct 2004 18:55:07 -0000	1.16
--- src/test/regress/expected/numeric.out	16 Jun 2005 01:37:05 -0000
***************
*** 1072,1078 ****
|          -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2         
(10 rows)

! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
to_char_20 |                          to_char                          
------------+-----------------------------------------------------------
|       text      9999     "text between quote marks"     0
--- 1072,1078 ----
|          -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2         
(10 rows)

! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
to_char_20 |                          to_char                          
------------+-----------------------------------------------------------
|       text      9999     "text between quote marks"     0
Index: src/test/regress/expected/rowtypes.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rowtypes.out,v
retrieving revision 1.2
diff -c -c -r1.2 rowtypes.out
*** src/test/regress/expected/rowtypes.out	9 Jun 2004 19:08:20 -0000	1.2
--- src/test/regress/expected/rowtypes.out	16 Jun 2005 01:37:05 -0000
***************
*** 25,31 ****
(Joe,"von Blow") | (Joe,d'Blow)
(1 row)

! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
fullname      |    fullname     
-------------------+-----------------
(Joe,"von""Blow") | (Joe,"d\\Blow")
--- 25,31 ----
(Joe,"von Blow") | (Joe,d'Blow)
(1 row)

! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
fullname      |    fullname     
-------------------+-----------------
(Joe,"von""Blow") | (Joe,"d\\Blow")
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.27
diff -c -c -r1.27 timestamp.out
*** src/test/regress/expected/timestamp.out	3 Jun 2004 02:08:06 -0000	1.27
--- src/test/regress/expected/timestamp.out	16 Jun 2005 01:37:06 -0000
***************
*** 1044,1050 ****
| 05 05 17 32 01 63121
(64 rows)

! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""') 
FROM TIMESTAMP_TBL;
to_char_6 |                     to_char                     
-----------+-------------------------------------------------
--- 1044,1050 ----
| 05 05 17 32 01 63121
(64 rows)

! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMP_TBL;
to_char_6 | to_char
-----------+-------------------------------------------------
***************
*** 1358,1364 ****
(1 row)

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45', 
!                                           'HH "\\text between quote marks\\"" YY MI SS');
to_timestamp_6 |         to_timestamp         
----------------+------------------------------
| Thu Jan 01 15:54:45 1998 PST
--- 1358,1364 ----
(1 row)

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45', 
!                                           E'HH "\\text between quote marks\\"" YY MI SS');
to_timestamp_6 |         to_timestamp         
----------------+------------------------------
| Thu Jan 01 15:54:45 1998 PST
Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.17
diff -c -c -r1.17 timestamptz.out
*** src/test/regress/expected/timestamptz.out	11 Jul 2004 04:57:20 -0000	1.17
--- src/test/regress/expected/timestamptz.out	16 Jun 2005 01:37:07 -0000
***************
*** 1041,1047 ****
| 05 05 17 32 01 63121
(64 rows)

! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""') 
FROM TIMESTAMPTZ_TBL;		
to_char_6 |                     to_char                     
-----------+-------------------------------------------------
--- 1041,1047 ----
| 05 05 17 32 01 63121
(64 rows)

! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMPTZ_TBL;
to_char_6 | to_char
-----------+-------------------------------------------------
***************
*** 1427,1433 ****
(1 row)

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45', 
! 										  'HH "\\text between quote marks\\"" YY MI SS');
to_timestamp_6 |         to_timestamp         
----------------+------------------------------
| Thu Jan 01 15:54:45 1998 PST
--- 1427,1433 ----
(1 row)

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45', 
! 										  E'HH "\\text between quote marks\\"" YY MI SS');
to_timestamp_6 |         to_timestamp         
----------------+------------------------------
| Thu Jan 01 15:54:45 1998 PST
Index: src/test/regress/expected/type_sanity.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/type_sanity.out,v
retrieving revision 1.25
diff -c -c -r1.25 type_sanity.out
*** src/test/regress/expected/type_sanity.out	30 Apr 2005 20:31:39 -0000	1.25
--- src/test/regress/expected/type_sanity.out	16 Jun 2005 01:37:07 -0000
***************
*** 59,65 ****
-- NOTE: as of 8.0, this check finds smgr and unknown.
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
--- 59,65 ----
-- NOTE: as of 8.0, this check finds smgr and unknown.
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
Index: src/test/regress/input/copy.source
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/input/copy.source,v
retrieving revision 1.12
diff -c -c -r1.12 copy.source
*** src/test/regress/input/copy.source	10 May 2005 00:16:07 -0000	1.12
--- src/test/regress/input/copy.source	16 Jun 2005 01:37:07 -0000
***************
*** 62,71 ****
test 	text,
filler	int);

! insert into copytest values('DOS','abc\r\ndef',1);
! insert into copytest values('Unix','abc\ndef',2);
! insert into copytest values('Mac','abc\rdef',3);
! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);

copy copytest to '@abs_builddir@/results/copytest.csv' csv;

--- 62,71 ----
test 	text,
filler	int);

! insert into copytest values('DOS',E'abc\r\ndef',1);
! insert into copytest values('Unix',E'abc\ndef',2);
! insert into copytest values('Mac',E'abc\rdef',3);
! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);

copy copytest to '@abs_builddir@/results/copytest.csv' csv;

***************
*** 79,87 ****

--- same test but with an escape char different from quote char

! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';

! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';

select * from copytest except select * from copytest2;

--- 79,87 ----

--- same test but with an escape char different from quote char

! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';

! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';

select * from copytest except select * from copytest2;

Index: src/test/regress/output/copy.source
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/output/copy.source,v
retrieving revision 1.10
diff -c -c -r1.10 copy.source
*** src/test/regress/output/copy.source	10 May 2005 00:16:07 -0000	1.10
--- src/test/regress/output/copy.source	16 Jun 2005 01:37:08 -0000
***************
*** 37,46 ****
style	text,
test 	text,
filler	int);
! insert into copytest values('DOS','abc\r\ndef',1);
! insert into copytest values('Unix','abc\ndef',2);
! insert into copytest values('Mac','abc\rdef',3);
! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);
copy copytest to '@abs_builddir@/results/copytest.csv' csv;
create temp table copytest2 (like copytest);
copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
--- 37,46 ----
style	text,
test 	text,
filler	int);
! insert into copytest values('DOS',E'abc\r\ndef',1);
! insert into copytest values('Unix',E'abc\ndef',2);
! insert into copytest values('Mac',E'abc\rdef',3);
! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
copy copytest to '@abs_builddir@/results/copytest.csv' csv;
create temp table copytest2 (like copytest);
copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
***************
*** 51,58 ****

truncate copytest2;
--- same test but with an escape char different from quote char
! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
select * from copytest except select * from copytest2;
style | test | filler 
-------+------+--------
--- 51,58 ----

truncate copytest2;
--- same test but with an escape char different from quote char
! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
select * from copytest except select * from copytest2;
style | test | filler 
-------+------+--------
Index: src/test/regress/sql/arrays.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/arrays.sql,v
retrieving revision 1.20
diff -c -c -r1.20 arrays.sql
*** src/test/regress/sql/arrays.sql	22 Apr 2005 21:58:32 -0000	1.20
--- src/test/regress/sql/arrays.sql	16 Jun 2005 01:37:08 -0000
***************
*** 204,210 ****
-- none of the following should be accepted
select '{{1,{2}},{2,3}}'::text[];
select '{{},{}}'::text[];
! select '{{1,2},\\{2,3}}'::text[];
select '{{"1 2" x},{3}}'::text[];
select '{}}'::text[];
select '{ }}'::text[];
--- 204,210 ----
-- none of the following should be accepted
select '{{1,{2}},{2,3}}'::text[];
select '{{},{}}'::text[];
! select E'{{1,2},\\{2,3}}'::text[];
select '{{"1 2" x},{3}}'::text[];
select '{}}'::text[];
select '{ }}'::text[];
Index: src/test/regress/sql/copy2.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/copy2.sql,v
retrieving revision 1.12
diff -c -c -r1.12 copy2.sql
*** src/test/regress/sql/copy2.sql	13 May 2005 06:33:40 -0000	1.12
--- src/test/regress/sql/copy2.sql	16 Jun 2005 01:37:08 -0000
***************
*** 83,89 ****
3000;;c;;
\.

! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
4000:\X:C:\X:\X
4001:1:empty::
4002:2:null:\X:\X
--- 83,89 ----
3000;;c;;
\.

! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
4000:\X:C:\X:\X
4001:1:empty::
4002:2:null:\X:\X
***************
*** 121,133 ****
col2 text
);

! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
! INSERT INTO y VALUES ('It is "perfect".','\t');
INSERT INTO y VALUES ('', NULL);

COPY y TO stdout WITH CSV;
COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';

--test that we read consecutive LFs properly

--- 121,133 ----
col2 text
);

! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
! INSERT INTO y VALUES ('It is "perfect".',E'\t');
INSERT INTO y VALUES ('', NULL);

COPY y TO stdout WITH CSV;
COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';

--test that we read consecutive LFs properly

Index: src/test/regress/sql/int8.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/int8.sql,v
retrieving revision 1.7
diff -c -c -r1.7 int8.sql
*** src/test/regress/sql/int8.sql	4 Oct 2004 14:42:48 -0000	1.7
--- src/test/regress/sql/int8.sql	16 Jun 2005 01:37:08 -0000
***************
*** 61,65 ****
SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;	
SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
--- 61,65 ----
SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;	
SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
Index: src/test/regress/sql/numeric.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/numeric.sql,v
retrieving revision 1.11
diff -c -c -r1.11 numeric.sql
*** src/test/regress/sql/numeric.sql	28 Oct 2004 18:55:08 -0000	1.11
--- src/test/regress/sql/numeric.sql	16 Jun 2005 01:37:08 -0000
***************
*** 742,748 ****
SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999')	FROM num_data;
SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
SELECT '' AS to_char_21, to_char(val, '999999SG9999999999')			FROM num_data;
SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999')	FROM num_data;

--- 742,748 ----
SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999')	FROM num_data;
SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
SELECT '' AS to_char_21, to_char(val, '999999SG9999999999')			FROM num_data;
SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999')	FROM num_data;

Index: src/test/regress/sql/rowtypes.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/rowtypes.sql,v
retrieving revision 1.2
diff -c -c -r1.2 rowtypes.sql
*** src/test/regress/sql/rowtypes.sql	9 Jun 2004 19:08:20 -0000	1.2
--- src/test/regress/sql/rowtypes.sql	16 Jun 2005 01:37:08 -0000
***************
*** 20,26 ****

select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;

! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;

select '(Joe,"Blow,Jr")'::fullname;

--- 20,26 ----

select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;

! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;

select '(Joe,"Blow,Jr")'::fullname;

Index: src/test/regress/sql/timestamp.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
retrieving revision 1.13
diff -c -c -r1.13 timestamp.sql
*** src/test/regress/sql/timestamp.sql	5 Mar 2004 02:41:14 -0000	1.13
--- src/test/regress/sql/timestamp.sql	16 Jun 2005 01:37:09 -0000
***************
*** 186,192 ****
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 
FROM TIMESTAMP_TBL;

! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMP_TBL;

SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
--- 186,192 ----
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 
FROM TIMESTAMP_TBL;

! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMP_TBL;

SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
***************
*** 211,217 ****
SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
! 'HH "\\text between quote marks\\"" YY MI SS');

SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');

--- 211,217 ----
SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
! E'HH "\\text between quote marks\\"" YY MI SS');

SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');

Index: src/test/regress/sql/timestamptz.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
retrieving revision 1.6
diff -c -c -r1.6 timestamptz.sql
*** src/test/regress/sql/timestamptz.sql	5 Mar 2004 02:41:14 -0000	1.6
--- src/test/regress/sql/timestamptz.sql	16 Jun 2005 01:37:09 -0000
***************
*** 179,185 ****
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 
FROM TIMESTAMPTZ_TBL;

! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMPTZ_TBL;

SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
--- 179,185 ----
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 
FROM TIMESTAMPTZ_TBL;

! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMPTZ_TBL;

SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
***************
*** 207,213 ****
SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
! 'HH "\\text between quote marks\\"" YY MI SS');

SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');

--- 207,213 ----
SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');

SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
! E'HH "\\text between quote marks\\"" YY MI SS');

SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');

Index: src/test/regress/sql/type_sanity.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/type_sanity.sql,v
retrieving revision 1.25
diff -c -c -r1.25 type_sanity.sql
*** src/test/regress/sql/type_sanity.sql	30 Apr 2005 20:31:39 -0000	1.25
--- src/test/regress/sql/type_sanity.sql	16 Jun 2005 01:37:09 -0000
***************
*** 54,60 ****

SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
--- 54,60 ----

SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);

------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Christopher Kings-Lynne (#2)

Re: Escape handling in strings

Christopher Kings-Lynne wrote:

I'm still really iffy about this. I think it will really hurt pgsql due
to backward compatibility :(

(If I'm understanding how the proposed change works...)

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

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

Christopher Kings-Lynne

chriskl@familyhealth.com.au

almost 21 years ago

In reply to: Bruce Momjian (#3)

Re: Escape handling in strings

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

It's just going to break millions of PHP scripts :(

Chris

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Christopher Kings-Lynne (#4)

Re: Escape handling in strings

Christopher Kings-Lynne wrote:

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

It's just going to break millions of PHP scripts :(

Let me give you a little longer answer. Right now we have this TODO
item:

* Allow backslash handling in quoted strings to be disabled for
portability

The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
SQL-spec compliant, so allow such handling to be disabled. However,
disabling backslashes could break many third-party applications and
tools.

Now, if we don't address it, we might as well remove the TODO item and
say we are never going to change it, because right now, we have a plan,
and I think the longer we go the harder it will be. And if we don't
change it, it makes it quite hard for people to port applications to
PostgreSQL. Fundamental queries like:

SELECT * FROM files WHERE filename = 'C:\tmp'

do not work. When a query with a single table and single WHERE clause
isn't portable, it seems like a problem. If this was isolated to CREATE
TABLE or something, it wouldn't be a big deal.

One possible idea is to have the warning in 8.1 configurable, so you can
turn it off, and see how well things go in the community. At a minimum,
the warning will flag non-portable queries to help in porting, and folks
can use E'' for non-porable string representations.

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

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Christopher Kings-Lynne (#4)

Re: Escape handling in strings

Sorry, one more thing. :-(

Let me add that I am not 100% sold on the idea either, but using the
logic I outlined, I don't see how we can continue to do nothing about
this issue, and I am afraid delay will only make an inevitable fix
harder. Maybe we will have to wait 2-3 years before we can make a non-E
string handle backslashes literally.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

It's just going to break millions of PHP scripts :(

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

Christopher Kings-Lynne

chriskl@familyhealth.com.au

almost 21 years ago

In reply to: Bruce Momjian (#5)

Re: Escape handling in strings

* Allow backslash handling in quoted strings to be disabled for
portability

The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
SQL-spec compliant, so allow such handling to be disabled. However,
disabling backslashes could break many third-party applications and
tools.

Now, if we don't address it, we might as well remove the TODO item and
say we are never going to change it, because right now, we have a plan,
and I think the longer we go the harder it will be. And if we don't
change it, it makes it quite hard for people to port applications to
PostgreSQL. Fundamental queries like:

SELECT * FROM files WHERE filename = 'C:\tmp'

do not work. When a query with a single table and single WHERE clause
isn't portable, it seems like a problem. If this was isolated to CREATE
TABLE or something, it wouldn't be a big deal.

Why not compromise? Allow ONLY \' in normal strings? That'd deal with
the majority of compatibility issues. Or, like you say, make it a GUC :(

Chris

Rod Taylor

rbt@rbt.ca

almost 21 years ago

In reply to: Bruce Momjian (#6)

Re: Escape handling in strings

On Wed, 2005-06-15 at 23:13 -0400, Bruce Momjian wrote:

Sorry, one more thing. :-(

Let me add that I am not 100% sold on the idea either, but using the
logic I outlined, I don't see how we can continue to do nothing about
this issue, and I am afraid delay will only make an inevitable fix
harder. Maybe we will have to wait 2-3 years before we can make a non-E
string handle backslashes literally.

Add the code and the warning, with a GUC for turning it off the \
parsing so '\'' would be an error.

Breaking old code isn't so bad if it's followed up with a campaign from
the advocacy folks about how to do the job properly, along with a
thorough explanation as to why the change was made (compatibility with
other DBs, SQL Spec, etc.).

It probably won't be any worse than when '' was rejected for an integer
0.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

It's just going to break millions of PHP scripts :(

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Pavel Stehule

pavel.stehule@gmail.com

almost 21 years ago

In reply to: Christopher Kings-Lynne (#7)

Re: Escape handling in strings

Why not compromise? Allow ONLY \' in normal strings? That'd deal with
the majority of compatibility issues. Or, like you say, make it a GUC :(

Chris

what is wrong on GUC?

Pavel

#10

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Rod Taylor (#8)

Re: Escape handling in strings

Rod Taylor <pg@rbt.ca> writes:

It probably won't be any worse than when '' was rejected for an integer
0.

That analogy is *SO* far off the mark that I have to object.

Fooling with quoting rules will not simply cause clean failures, which
is what you got from ''-no-longer-accepted-by-atoi. What it will cause
is formerly valid input being silently interpreted as something else.
That's bad enough, but it gets worse: formerly secure client code may
now be vulnerable to SQL-injection attacks, because it doesn't know how
to quote text properly.

What we are talking about here is an extremely significant change with
extremely serious consequences, and imagining that it is not will be
a recipe for disaster.

I also think that pgsql-patches is not the place to be discussing such
things... it needs a whole lot more visibility.

regards, tom lane

#11

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Pavel Stehule (#9)

Re: Escape handling in strings

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:

what is wrong on GUC?

The idea of a GUC that allows security violations when it's set
differently than the application is expecting fills me with fear.
This is going to look the 7.3 autocommit fiasco look like a day
at the beach.

regards, tom lane

#12

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Tom Lane (#10)

Re: Escape handling in strings

Tom Lane wrote:

What we are talking about here is an extremely significant change with
extremely serious consequences, and imagining that it is not will be
a recipe for disaster.

I also think that pgsql-patches is not the place to be discussing such
things... it needs a whole lot more visibility.

OK, let me hit general with this. I sent the first to patches so people
could see the code changes in the patch.

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

#13

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Christopher Kings-Lynne (#7)

Re: Escape handling in strings

Christopher Kings-Lynne wrote:

* Allow backslash handling in quoted strings to be disabled for
portability

The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
SQL-spec compliant, so allow such handling to be disabled. However,
disabling backslashes could break many third-party applications and
tools.

Now, if we don't address it, we might as well remove the TODO item and
say we are never going to change it, because right now, we have a plan,
and I think the longer we go the harder it will be. And if we don't
change it, it makes it quite hard for people to port applications to
PostgreSQL. Fundamental queries like:

SELECT * FROM files WHERE filename = 'C:\tmp'

do not work. When a query with a single table and single WHERE clause
isn't portable, it seems like a problem. If this was isolated to CREATE
TABLE or something, it wouldn't be a big deal.

Why not compromise? Allow ONLY \' in normal strings? That'd deal with
the majority of compatibility issues. Or, like you say, make it a GUC :(

The problem with allowing just \' is that we would then not be able to
distinguish a literal \ then ' from a \'. Seems it is all or nothing.

FYI, I added a little to the web page:

Steps:

1. Change all \' to SQL-standard ''.
2. Change use of \ in strings to use E''.
3. Finally, change '' to treat \ literally.

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

#14

Andrew Dunstan

andrew@dunslane.net

almost 21 years ago

In reply to: Tom Lane (#10)

Re: [PATCHES] Escape handling in strings

[switched to -hackers]

Tom Lane wrote:

Rod Taylor <pg@rbt.ca> writes:

It probably won't be any worse than when '' was rejected for an integer
0.

That analogy is *SO* far off the mark that I have to object.

Fooling with quoting rules will not simply cause clean failures, which
is what you got from ''-no-longer-accepted-by-atoi. What it will cause
is formerly valid input being silently interpreted as something else.
That's bad enough, but it gets worse: formerly secure client code may
now be vulnerable to SQL-injection attacks, because it doesn't know how
to quote text properly.

What we are talking about here is an extremely significant change with
extremely serious consequences, and imagining that it is not will be
a recipe for disaster.

All true. Conversely, there does need to be a path for us to get to
standard behaviour.

I think we're going to need to provide for switchable behaviour, as ugly
as that might be (looking briefly at scan.l it looks like the simplest
way would be a separate state for being inside standard strings, with
the choice of state being made conditionally in the {xqstart} rule).

We can't just break backwards compatibility overnight like this.

cheers

andrew

#15

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Andrew Dunstan (#14)

Re: [PATCHES] Escape handling in strings

Andrew Dunstan <andrew@dunslane.net> writes:

All true. Conversely, there does need to be a path for us to get to
standard behaviour.

Yes --- but the important word there is "path". I think we have to do
this in stages over a number of releases, to give people time to
migrate.

Assuming that the end result we want to get to is:
1. Plain '...' literals are per SQL spec: '' for embedded
quotes, backslashes are not special.
2. We add a construct E'...' that handles backslash escapes
the same way '...' literals do today.

I think what would be reasonable for 8.1 is to create the E'...'
construct --- which will not cause any backwards compatibility issues
that I can see --- document it and encourage people to migrate,
and start throwing warnings about use of \' in non-E literals.
(We could have a GUC variable to suppress the warnings; I'm of
the opinion that it would be better not to, though, because the point
is to get people out of that habit sooner rather than later.)

I would be inclined to leave things like that for a couple of release
cycles before we disable backslashes in regular literals. By the time
we do that, we should have at least flushed out the cases where
disabling backslashes will create security holes.

I think we're going to need to provide for switchable behaviour, as ugly
as that might be (looking briefly at scan.l it looks like the simplest
way would be a separate state for being inside standard strings, with
the choice of state being made conditionally in the {xqstart} rule).

I really really dislike that idea; it is a recipe for creating problems
not solving them.

The hard part in all this is to create apps that will survive the
transition gracefully. I think the only way for that is to implement
a reporting feature that lets the app know whether backslahes are
special in plain literals or not. We already have the mechanism for
that, ie read-only GUC variables with GUC_REPORT enabled (which we use
for integer datetimes, for instance). But I really believe it is
important that this be a *read only* thing not something that can be
flipped around at runtime. Anyway, the reporting variable is another
thing that should appear in 8.1.

regards, tom lane

#16

Andrew Dunstan

andrew@dunslane.net

almost 21 years ago

In reply to: Tom Lane (#15)

Re: [PATCHES] Escape handling in strings

Bruce Momjian said:

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message. The \'
message can easily be avoided in clients even in 8.0 by using '', but
for E'', there is no way to prepare an application before upgrading to
8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
what percentage of users are going to upgrade to that?) This is why I
think we need to add a GUC to allow the warning to be turned off. To
be clear, the GUC is to control the warning, not the query behavior.

We could go with the second warning only in 8.2, but that seems too
confusing --- we should deal with the escape issue in two stages,
rather than three.

So you don't agree with Tom's suggestion to implement E'' a full cycle
before removing backslash processing in standard strings? Or have I
misunderstood again?

cheers

andrew

Import Notes

Reply to msg id not found: 200506170141.j5H1f5F18208@candle.pha.pa.usReference msg id not found: 200506170141.j5H1f5F18208@candle.pha.pa.us | Resolved by subject fallback

#17

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Tom Lane (#15)

Re: [PATCHES] Escape handling in strings

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

All true. Conversely, there does need to be a path for us to get to
standard behaviour.

Yes --- but the important word there is "path". I think we have to do
this in stages over a number of releases, to give people time to
migrate.

Assuming that the end result we want to get to is:
1. Plain '...' literals are per SQL spec: '' for embedded
quotes, backslashes are not special.
2. We add a construct E'...' that handles backslash escapes
the same way '...' literals do today.

I think what would be reasonable for 8.1 is to create the E'...'
construct --- which will not cause any backwards compatibility issues
that I can see --- document it and encourage people to migrate,
and start throwing warnings about use of \' in non-E literals.
(We could have a GUC variable to suppress the warnings; I'm of
the opinion that it would be better not to, though, because the point
is to get people out of that habit sooner rather than later.)

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message. The \'
message can easily be avoided in clients even in 8.0 by using '', but
for E'', there is no way to prepare an application before upgrading to
8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
what percentage of users are going to upgrade to that?) This is why I
think we need to add a GUC to allow the warning to be turned off. To be
clear, the GUC is to control the warning, not the query behavior.

We could go with the second warning only in 8.2, but that seems too
confusing --- we should deal with the escape issue in two stages, rather
than three.

The hard part in all this is to create apps that will survive the
transition gracefully. I think the only way for that is to implement
a reporting feature that lets the app know whether backslahes are
special in plain literals or not. We already have the mechanism for
that, ie read-only GUC variables with GUC_REPORT enabled (which we use
for integer datetimes, for instance). But I really believe it is
important that this be a *read only* thing not something that can be
flipped around at runtime. Anyway, the reporting variable is another
thing that should appear in 8.1.

OK, adding.

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

#18

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Andrew Dunstan (#16)

Re: [PATCHES] Escape handling in strings

Andrew Dunstan wrote:

Bruce Momjian said:

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message. The \'
message can easily be avoided in clients even in 8.0 by using '', but
for E'', there is no way to prepare an application before upgrading to
8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
what percentage of users are going to upgrade to that?) This is why I
think we need to add a GUC to allow the warning to be turned off. To
be clear, the GUC is to control the warning, not the query behavior.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

We could go with the second warning only in 8.2, but that seems too
confusing --- we should deal with the escape issue in two stages,
rather than three.

So you don't agree with Tom's suggestion to implement E'' a full cycle
before removing backslash processing in standard strings? Or have I
misunderstood again?

I think you misunderstood. There is no scheduled date to change the
actual behavior. The issue is whether we delay one release before
issuing a warning for backslashes in non-E strings.

I have highlighted the sentence where I say we are talking about when to
add the warning, not when to change the behavior.

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

#19

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Bruce Momjian (#17)

Re: [PATCHES] Escape handling in strings

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message.

Those are two very different things. \' is easy to get around and
there's no very good reason not to send '' instead. But avoiding all
use of \anything is impossible (think \\) so a non-suppressable warning
for that would be quite unacceptable IMHO. I think it's much too early
to be throwing a warning for \anything anyway. 8.2 or so, OK, but not
in this cycle.

regards, tom lane

#20

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Tom Lane (#19)

Re: [PATCHES] Escape handling in strings

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message.

Those are two very different things. \' is easy to get around and
there's no very good reason not to send '' instead. But avoiding all
use of \anything is impossible (think \\) so a non-suppressable warning
for that would be quite unacceptable IMHO. I think it's much too early
to be throwing a warning for \anything anyway. 8.2 or so, OK, but not
in this cycle.

I am concerned we are going to generate confusing if we warn about one
use of backslashes in strings but not another. I am thinking we will
just add the infrastructure for E'' in 8.1 (with the warning turned
off), and state we will warn about all backslashes in non-E strings in
8.2, and maybe go for literal strings in 8.3 or 8.4 depending on user
feedback.

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

#21