Horton

Thread

  1. BUG #1350: Backslash ecape charcter violates ISO/ANSI spec and is hazardous

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-12-17T02:39:52Z

    The following bug has been logged online:

Bug reference:      1350
Logged by:          Ken Johanson

Email address:      ken@kensystem.com

PostgreSQL version: 8.0 Beta

Operating system:   Any

Description:        Backslash ecape charcter violates ISO/ANSI spec and is 
hazardous 

Details: 

I can't find any option to run the server in a more standard mode for string 
escaping rules, of only needing to escape single quotes. The current 
backslash-escape behavior is a huge problem for SQL statement portability, a 
spec violation, and blindsides developers coming from Oracle, Sybase, MS, 
etc.

  2. Re: BUG #1350: Backslash ecape charcter violates ISO/ANSI spec

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-12-17T03:25:28Z

    PostgreSQL Bugs List wrote:
> Description:        Backslash ecape charcter violates ISO/ANSI spec and is 
> hazardous 
> 
> Details: 
> 
> I can't find any option to run the server in a more standard mode for string 
> escaping rules, of only needing to escape single quotes. The current 
> backslash-escape behavior is a huge problem for SQL statement portability, a 
> spec violation, and blindsides developers coming from Oracle, Sybase, MS, 
> etc. 

We have a 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.

Unfortunately that's all we have.  :-)

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

  3. Re: BUG #1350: Backslash ecape charcter violates ISO/ANSI

    Ken Johanson <ken@kensystem.com> — 2004-12-17T15:21:24Z

    >We have a 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.
>
>Unfortunately that's all we have.  :-)
>
>  
>
Thanks, glad to hear it's on the radar. From what I can tell it the 
broadest reaching standards incompatibility in the server. And, it's 
preventing some project managers from adopting the server as an 
alternative to the commercial ones (Its always been an easy-to-cite, and 
well justified concern that the backslash behavior is incompatible with 
other DBs and specs).

Thx,
ken

  4. Re: BUG #1350: Backslash ecape charcter violates ISO/ANSI

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-12-18T03:51:14Z

    Ken Johanson wrote:
> 
> >We have a 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.
> >
> >Unfortunately that's all we have.  :-)
> >
> >  
> >
> Thanks, glad to hear it's on the radar. From what I can tell it the 
> broadest reaching standards incompatibility in the server. And, it's 

That is probably true.

> preventing some project managers from adopting the server as an 
> alternative to the commercial ones (Its always been an easy-to-cite, and 
> well justified concern that the backslash behavior is incompatible with 
> other DBs and specs).

We don't hear it very often, perhaps once every four months.  You have
to double single quotes from user data anyway so most of our interfaces
have a function that does this and handles backslashes too.

Our TODO list probably has even more items you could cite as reasons
_not_ to use PostgreSQL.  :-)  When it becomes a key issue for someone I
suppose they will code a fix for it.

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

  5. Re: BUG #1350: Backslash ecape charcter violates ISO/ANSI

    Ken Johanson <ken@kensystem.com> — 2004-12-18T04:14:26Z

    >We don't hear it very often, perhaps once every four months.  You have
>to double single quotes from user data anyway so most of our interfaces
>have a function that does this and handles backslashes too.
>
>  
>
True, but users also need (or already use) a generic, predictable 
SQL-escape function (mere apostrophe doubling) from their API, that 
needs to work for any database..., but when they try to use it with pg, 
they are blindsided when they realize backslash characters are lost (I 
know of one company that committed to PG and had to back out after they 
eventually realized the backslash issue (porting issue) was too 
burdensome for their large codebase - sad).

Unfortunately many APIs dont have the prepared statement style automatic 
string escaping available; more importantly, prepared statements dont 
work well some highly complex, programaticly generated SQL statements, 
and a generic Sql escape function is far easier to use (in my experience).

>Our TODO list probably has even more items you could cite as reasons
>_not_ to use PostgreSQL.  :-)  When it becomes a key issue for someone I
>suppose they will code a fix for it.
>
>  
>
I may have this key issue - user share.... the other large open source 
DB is adding this compliance-mode at the request of SAP, so this will 
leave postgres as the last one standing... so to speak. ;-) Since 
incorrect SQL escaping has been a key reservation about (both) these DBs 
for users in transition (from commercial DBs), this last mile of 
compliance (of this magnitude) will benefit its benefactor with the 
market share of those awaiting masses :-)