Thread

1. PG do not accept quoted names for tables/columns

   Yaniv Hamo <hamo@cs.technion.ac.il> — 2003-02-06T09:26:44Z

     Hi,
   I noticed that Postgres issues a fatal error when given a quoted name of
   table or column. This is a problem in secured cgi scripts, which quote
   everything they get from the user, to avoid malicious users from trying to
   execute SQL commands using some engineered input.
   
   
   shared# select version();
                                  version
   ---------------------------------------------------------------------
    PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
   
   
   shared# CREATE TABLE 'testtable' ('test' INT);
   ERROR:  parser: parse error at or near "'testtable'" at character 14
   
   
   Thanks and have a nice day,
   Yaniv
   
   
   

2. Re: PG do not accept quoted names for tables/columns

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-02-06T19:36:12Z

   On Thu, 6 Feb 2003, Yaniv Hamo wrote:
   
   > I noticed that Postgres issues a fatal error when given a quoted name of
   > table or column. This is a problem in secured cgi scripts, which quote
   > everything they get from the user, to avoid malicious users from trying to
   > execute SQL commands using some engineered input.
   >
   >
   > shared# select version();
   >                                version
   > ---------------------------------------------------------------------
   >  PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
   >
   >
   > shared# CREATE TABLE 'testtable' ('test' INT);
   > ERROR:  parser: parse error at or near "'testtable'" at character 14
   
   I don't believe that's a valid query. For delimiting identifieres I think
   you want double quotes not single quotes.
   
   
   

3. Re: PG do not accept quoted names for tables/columns

   Andrew McMillan <andrew@catalyst.net.nz> — 2003-02-06T19:42:41Z

   On Thu, 2003-02-06 at 22:26, Yaniv Hamo wrote:
   >   Hi,
   > I noticed that Postgres issues a fatal error when given a quoted name of
   > table or column. This is a problem in secured cgi scripts, which quote
   > everything they get from the user, to avoid malicious users from trying to
   > execute SQL commands using some engineered input.
   
   The SQL specification states that you should quote identifiers with
   double quotes.  Single quotes are used for quoting values:
   
   CREATE TABLE "testtable" ( "test" INT );
   
   PostgreSQL is SQL compliant on this issue.
   
   Regards,
   					Andrew.
   -- 
   ---------------------------------------------------------------------
   Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
   WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
   DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
              Survey for nothing with http://survey.net.nz/ 
   ---------------------------------------------------------------------
   
   
   

4. Re: PG do not accept quoted names for tables/columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-06T20:45:43Z

   Yaniv Hamo <hamo@cs.Technion.AC.IL> writes:
   > shared# CREATE TABLE 'testtable' ('test' INT);
   > ERROR:  parser: parse error at or near "'testtable'" at character 14
   
   This is not a bug.  (Perhaps you were looking for double quotes "..."
   not single quotes '...' ?)
   
   			regards, tom lane