Thread: Column name validation in embedded query.

Column name validation in embedded query.

From
"Lori Pate"
Date:
<p><font face="Arial" size="2">I am using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows XP
Pro.</font><p><fontface="Arial" size="2">The following query, when executed as a stand alone query returns an error,
correctly,that the column name is not valid:</font><p><font face="Arial" size="2">Query A) Select patientorder_key from
patientorderwhere visit_key = 250314 and provider_key = 301;</font><p><font face="Arial" size="2">The correct syntax
shouldbe:</font><br /><font face="Arial" size="2">Query B) Select patientorder.key from patientorder where visit_key =
250314and provider_key = 301;</font><p><font face="Arial" size="2">However, when the query with the incorrect column
name(Query A) is embedded in a where clause, column validation does not happen, no error is displayed, and PGAdmin
apparentlyignores the where clause all together, resulting in complete data deletion, as if there were no where
clause:</font><p><fontface="Arial" size="2">Begin;</font><br /><font face="Arial" size="2">Delete from testorder where
patientorder_keyin </font><br /><font face="Arial" size="2">(Select patientorder_key from patientorder where visit_key
=250314 and provider_key = 301);</font><br /><font face="Arial" size="2">End;</font><p><font face="Arial" size="2">This
resultedin massive amounts of data being deleted erroneously.</font><p><font face="Arial" size="2">I believe this is a
bug.</font><p><b><fontface="Arial">Lori E. Pate</font></b><br /><font face="Arial"> </font><br /><font
face="Arial">QualityAssurance Engineer</font><br /><font face="Arial">Opus Healthcare Solutions, Inc.</font><br /><font
face="Arial">12301Research Blvd.</font><br /><font face="Arial">Bldg. IV, Suite 200</font><br /><font
face="Arial">Austin,Texas  78759</font><br /><font face="Arial"> </font><br /><font face="Arial">Phone:
512.336.4562</font><br/><font face="Arial">E-Mail: </font><a href="mailto:lpate@opushealthcare.com"><u><font
color="#0000FF"face="Arial">lpate@opushealthcare.com</font></u></a><br /><font face="Arial">AIM: loripate0508</font><br
/><ahref="http://www.opushealthcare.com/"><u><font color="#0000FF" face="Times New
Roman">http://www.opushealthcare.com/</font></u></a><br/><font face="Arial"> </font> 

Re: Column name validation in embedded query.

From
Dave Page
Date:
<font face="Verdana, Helvetica, Arial"><span style="font-size:12.0px"><br /><br /><br /> On 7/11/05 2:55 pm, "Lori
Pate"<lpate@opushealthcare.com> wrote:<br /><br /></span></font><blockquote><span style="font-size:12.0px"><font
face="Arial">Iam using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows XP Pro.</font><font face="Verdana,
Helvetica,Arial"><br /><br /></font><font face="Arial">The following query, when executed as a stand alone query
returnsan error, correctly, that the column name is not valid:<br /></font><font face="Verdana, Helvetica, Arial"><br
/></font><fontface="Arial">Query A) Select patientorder_key from patientorder where visit_key = 250314 and provider_key
=301;</font><font face="Verdana, Helvetica, Arial"><br /><br /></font><font face="Arial">The correct syntax should
be:</font><fontface="Verdana, Helvetica, Arial"><br /></font><font face="Arial">Query B) Select patientorder.key from
patientorderwhere visit_key = 250314 and provider_key = 301;</font><font face="Verdana, Helvetica, Arial"><br /><br
/></font><fontface="Arial">However, when the query with the incorrect column name (Query A) is embedded in a where
clause,column validation does not happen, no error is displayed, and PGAdmin apparently ignores the where clause all
together,resulting in complete data deletion, as if there were no where clause:<br /></font><font face="Verdana,
Helvetica,Arial"><br /></font><font face="Arial">Begin;</font><font face="Verdana, Helvetica, Arial"><br /></font><font
face="Arial">Deletefrom testorder where patientorder_key in <br /> (Select patientorder_key from patientorder where
visit_key= 250314 and provider_key = 301);</font><font face="Verdana, Helvetica, Arial"><br /></font><font
face="Arial">End;</font><fontface="Verdana, Helvetica, Arial"><br /><br /></font><font face="Arial">This resulted in
massiveamounts of data being deleted erroneously.</font><font face="Verdana, Helvetica, Arial"><br /><br /></font><font
face="Arial">Ibelieve this is a bug.</font><font face="Verdana, Helvetica, Arial"><br
/></font></span></blockquote><spanstyle="font-size:12.0px"><font face="Verdana, Helvetica, Arial"><br /> Not in
pgAdmin.pgAdmin passes the query verbatim to PostgreSQL, making no attempt to parse or understand it at all. PostgreSQL
parses,plans and executes the query and returns any resulting data or messages to pgAdmin which displays it/them. <br
/><br/> I would suggest producing an easily re-createable test case and posting it to pgsql-bugs@postgresql.org.<br
/><br/> Regards, Dave <br /><br /></font></span> 

Re: Column name validation in embedded query.

From
"Jean-Pierre Pelletier"
Date:
Column name validation in embedded query.The columns of testorder are 
visible within the subquery
so the dbms interpret (correcly) patientorder_key as coming
from table testorder and gives no error.

I suggest you prefix column names within the subquery to avoid
such mistake.

Jean-Pierre Pelletier
e-djuster

----- Original Message ----- 
From: Lori Pate
To: pgadmin-support@postgresql.org
Sent: Monday, November 07, 2005 9:55 AM
Subject: [pgadmin-support] Column name validation in embedded query.


I am using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows XP 
Pro.
The following query, when executed as a stand alone query returns an error, 
correctly, that the column name is not valid:
Query A) Select patientorder_key from patientorder where visit_key = 250314 
and provider_key = 301;
The correct syntax should be:
Query B) Select patientorder.key from patientorder where visit_key = 250314 
and provider_key = 301;
However, when the query with the incorrect column name (Query A) is embedded 
in a where clause, column validation does not happen, no error is displayed, 
and PGAdmin apparently ignores the where clause all together, resulting in 
complete data deletion, as if there were no where clause:
Begin;
Delete from testorder where patientorder_key in
(Select patientorder_key from patientorder where visit_key = 250314 and 
provider_key = 301);
End;
This resulted in massive amounts of data being deleted erroneously.
I believe this is a bug.
Lori E. Pate

Quality Assurance Engineer
Opus Healthcare Solutions, Inc.
12301 Research Blvd.
Bldg. IV, Suite 200
Austin, Texas  78759

Phone: 512.336.4562
E-Mail: lpate@opushealthcare.com
AIM: loripate0508
http://www.opushealthcare.com/