Обсуждение: PG do not accept quoted names for tables/columns

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

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.

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

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