Re: [PATCHES] Escape handling in strings

2005-06-16 Thread Tom Lane
Pavel Stehule [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Escape handling in strings

2005-06-16 Thread Tom Lane
Rod Taylor [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Escape handling in strings

2005-06-16 Thread Bruce Momjian
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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Escape handling in strings

2005-06-16 Thread Bruce Momjian
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Christopher Kings-Lynne
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:

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.





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 -  
1.11
--- contrib/tsearch2/expected/tsearch2.out  16 Jun 2005 01:36:54 -
***
*** 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 [EMAIL PROTECTED] \' http://www.com/ http://aew.werc.ewr/?ad=qwedw 1aew.werc.ewr/?ad=qwedw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwedw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?  ad=qwedw 6aew.werc.ewr:8100/?ad=qwedw 7aew.werc.ewr:8100/?ad=qwedw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 [EMAIL PROTECTED] qwe-wer asdf frqwer jf sdjkwe hjwer werrwe ewr1 ewri2 a href=qweqwe
  /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 [EMAIL PROTECTED] '' http://www.com/ http://aew.werc.ewr/?ad=qwedw 1aew.werc.ewr/?ad=qwedw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwedw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?  ad=qwedw 6aew.werc.ewr:8100/?ad=qwedw 7aew.werc.ewr:8100/?ad=qwedw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 [EMAIL PROTECTED] qwe-wer asdf frqwer jf sdjkwe hjwer werrwe ewr1 

Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Bruce Momjian
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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Christopher Kings-Lynne

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 [EMAIL PROTECTED]


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Bruce Momjian
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Bruce Momjian

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 [EMAIL PROTECTED]
 

-- 
  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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Christopher Kings-Lynne

* 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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Rod Taylor
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 [EMAIL PROTECTED]
  
 
-- 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] Escape handling in strings

2005-06-15 Thread Pavel Stehule
 
 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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org