Please take our Survey
logo       
<prev   next>

Choosing A Webhost:
A web hosting service is a type of Internet hosting service that allows individuals and organizations to provide their own website accessible via the World Wide Web. Web hosts are companies that provide space on a server they own for use by their clients as well as providing Internet connectivity, typically in a data center. Web hosts can also provide data center space and connectivity to the Internet for servers they do not own to be located in their data center, called colocation. more...

Re: bug in info.c file - incorrect SQL: msg#00046

db.postgresql.odbc

Subject: Re: bug in info.c file - incorrect SQL


This patch adds catalog prefixes to system catalog references for the
queries that assume schema support.

What I did not do was to duplicate other queries that reference system
tables that _don't_ assume schema code.

Comments?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@xxxxxxxxxxxxxxxx> writes:
> > The attached patch fixes this by creating a pn1 and pn2 alias for
> > pg_namespace in FROM. It also improves some of the alias tags. Applied
> > to ODBC CVS. Can someone run some tests on these?
>
> I see another bug here:
>
> The 7.3-and-up versions of these queries should explicitly prefix
> "pg_catalog." to the names of all system catalogs (and types and
> functions) they reference. This will make them bulletproof against
> conflicts against user-defined names, which might appear in front of
> the system names in the search path.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@xxxxxxxxxxxxxxxx | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
? autom4te.cache
Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.95
diff -c -c -r1.95 info.c
*** info.c 11 Feb 2003 16:57:49 -0000 1.95
--- info.c 21 Mar 2003 18:33:01 -0000
***************
*** 1270,1282 ****
if (conn->schema_support)
{
/* view is represented by its relkind since 7.1 */
! strcpy(tables_query, "select relname, nspname, relkind from
pg_class, pg_namespace");
strcat(tables_query, " where relkind in ('r', 'v')");
}
else if (PG_VERSION_GE(conn, 7.1))
{
/* view is represented by its relkind since 7.1 */
! strcpy(tables_query, "select relname, usename, relkind from
pg_class, pg_user");
strcat(tables_query, " where relkind in ('r', 'v')");
}
else
--- 1270,1284 ----
if (conn->schema_support)
{
/* view is represented by its relkind since 7.1 */
! strcpy(tables_query, "select relname, nspname, relkind"
! " from pg_catalog.pg_class, pg_catalog.pg_namespace");
strcat(tables_query, " where relkind in ('r', 'v')");
}
else if (PG_VERSION_GE(conn, 7.1))
{
/* view is represented by its relkind since 7.1 */
! strcpy(tables_query, "select relname, usename, relkind"
! " from pg_class, pg_user");
strcat(tables_query, " where relkind in ('r', 'v')");
}
else
***************
*** 1685,1691 ****
if (conn->schema_support)
sprintf(columns_query, "select u.nspname, c.relname, a.attname,
a.atttypid"
", t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules,
c.relkind"
! " from pg_namespace u, pg_class c, pg_attribute a,
pg_type t"
" where u.oid = c.relnamespace"
" and (not a.attisdropped)"
" and c.oid= a.attrelid and a.atttypid = t.oid and
(a.attnum > 0)",
--- 1687,1694 ----
if (conn->schema_support)
sprintf(columns_query, "select u.nspname, c.relname, a.attname,
a.atttypid"
", t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules,
c.relkind"
! " from pg_catalog.pg_namespace u, pg_catalog.pg_class
c,"
! " pg_catalog.pg_attribute a, pg_catalog.pg_type t"
" where u.oid = c.relnamespace"
" and (not a.attisdropped)"
" and c.oid= a.attrelid and a.atttypid = t.oid and
(a.attnum > 0)",
***************
*** 2202,2208 ****
if (PG_VERSION_GE(conn, 7.2))
strcat(columns_query, ", c.relhasoids");
if (conn->schema_support)
! strcat(columns_query, " from pg_namespace u, pg_class c where "
"u.oid = c.relnamespace");
else
strcat(columns_query, " from pg_user u, pg_class c where "
--- 2205,2212 ----
if (PG_VERSION_GE(conn, 7.2))
strcat(columns_query, ", c.relhasoids");
if (conn->schema_support)
! strcat(columns_query, " from pg_catalog.pg_namespace u,"
! " pg_catalog.pg_class c where "
"u.oid = c.relnamespace");
else
strcat(columns_query, " from pg_user u, pg_class c where "
***************
*** 2562,2568 ****
if (conn->schema_support)
sprintf(index_query, "select c.relname, i.indkey, i.indisunique"
", i.indisclustered, a.amname, c.relhasrules, n.nspname"
! " from pg_index i, pg_class c, pg_class d, pg_am a,
pg_namespace n"
" where d.relname = '%s'"
" and n.nspname = '%s'"
" and n.oid = d.relnamespace"
--- 2566,2574 ----
if (conn->schema_support)
sprintf(index_query, "select c.relname, i.indkey, i.indisunique"
", i.indisclustered, a.amname, c.relhasrules, n.nspname"
! " from pg_catalog.pg_index i, pg_catalog.pg_class c,"
! " pg_catalog.pg_class d, pg_catalog.pg_am a,"
! " pg_catalog.pg_namespace n"
" where d.relname = '%s'"
" and n.nspname = '%s'"
" and n.oid = d.relnamespace"
***************
*** 2960,2966 ****
*/
if (conn->schema_support)
sprintf(tables_query, "select
ta.attname, ia.attnum"
! " from pg_attribute ta,
pg_attribute ia, pg_class c, pg_index i, pg_namespace n"
" where c.relname = '%s'"
" AND n.nspname = '%s'"
" AND c.oid = i.indrelid"
--- 2966,2974 ----
*/
if (conn->schema_support)
sprintf(tables_query, "select
ta.attname, ia.attnum"
! " from pg_catalog.pg_attribute
ta,"
! " pg_catalog.pg_attribute ia,
pg_catalog.pg_class c,"
! " pg_catalog.pg_index i,
pg_catalog.pg_namespace n"
" where c.relname = '%s'"
" AND n.nspname = '%s'"
" AND c.oid = i.indrelid"
***************
*** 2990,2996 ****
*/
if (conn->schema_support)
sprintf(tables_query, "select
ta.attname, ia.attnum"
! " from pg_attribute ta,
pg_attribute ia, pg_class c, pg_index i, pg_namespace n"
" where c.relname = '%s_pkey'"
" AND n.nspname = '%s'"
" AND c.oid = i.indexrelid"
--- 2998,3006 ----
*/
if (conn->schema_support)
sprintf(tables_query, "select
ta.attname, ia.attnum"
! " from pg_catalog.pg_attribute
ta,"
! " pg_catalog.pg_attribute ia,
pg_catalog.pg_class c,"
! " pg_catalog.pg_index i,
pg_catalog.pg_namespace n"
" where c.relname = '%s_pkey'"
" AND n.nspname = '%s'"
" AND c.oid = i.indexrelid"
***************
*** 3125,3131 ****
if (!bError && continueExec)
{
if (conn->schema_support)
! sprintf(query, "select OID from pg_class, pg_namespace
where relname = '%s' and pg_namespace.oid = relnamespace and
pg_namespace.nspname = '%s'", serverTableName, serverSchemaName);
else
sprintf(query, "select OID from pg_class where relname
= '%s'", serverTableName);
if (res = CC_send_query(conn, query, NULL,
CLEAR_RESULT_ON_ABORT), res)
--- 3135,3144 ----
if (!bError && continueExec)
{
if (conn->schema_support)
! sprintf(query, "select OID from pg_catalog.pg_class,"
! " pg_catalog.pg_namespace"
! " where relname = '%s' and pg_namespace.oid =
relnamespace and"
! " pg_namespace.nspname = '%s'", serverTableName,
serverSchemaName);
else
sprintf(query, "select OID from pg_class where relname
= '%s'", serverTableName);
if (res = CC_send_query(conn, query, NULL,
CLEAR_RESULT_ON_ABORT), res)
***************
*** 3192,3201 ****
if (!bError && continueExec)
{
if (conn->schema_support)
! sprintf(query, "select attrelid, attnum from pg_class,
pg_attribute, pg_namespace "
"where relname = '%s' and attrelid =
pg_class.oid "
"and (not attisdropped) "
! "and attname = '%s' and pg_namespace.oid =
relnamespace and pg_namespace.nspname = '%s'", serverTableName,
serverColumnName, serverSchemaName);
else
sprintf(query, "select attrelid, attnum from pg_class,
pg_attribute "
"where relname = '%s' and attrelid =
pg_class.oid "
--- 3205,3216 ----
if (!bError && continueExec)
{
if (conn->schema_support)
! sprintf(query, "select attrelid, attnum from
pg_catalog.pg_class,"
! " pg_catalog.pg_attribute, pg_catalog.pg_namespace "
"where relname = '%s' and attrelid =
pg_class.oid "
"and (not attisdropped) "
! "and attname = '%s' and pg_namespace.oid =
relnamespace and"
! " pg_namespace.nspname = '%s'",
serverTableName, serverColumnName, serverSchemaName);
else
sprintf(query, "select attrelid, attnum from pg_class,
pg_attribute "
"where relname = '%s' and attrelid =
pg_class.oid "
***************
*** 3465,3471 ****
mylog("%s: entering Foreign Key Case #2", func);
if (conn->schema_support)
{
! schema_strcat(schema_needed, "%.*s", szFkTableOwner,
cbFkTableOwner, szFkTableName, cbFkTableName, conn);
sprintf(tables_query, "SELECT pt.tgargs, "
" pt.tgnargs, "
" pt.tgdeferrable, "
--- 3480,3486 ----
mylog("%s: entering Foreign Key Case #2", func);
if (conn->schema_support)
{
! schema_strcat(schema_needed, "%.*s", szFkTableOwner,
cbFkTableOwner, szFkTableName, cbFkTableName, conn);
sprintf(tables_query, "SELECT pt.tgargs, "
" pt.tgnargs, "
" pt.tgdeferrable, "
***************
*** 3476,3491 ****
" pc1.oid, "
" pc1.relname, "
" pn.nspname "
! "FROM pg_class pc, "
! " pg_proc pp1, "
! " pg_proc pp2, "
! " pg_trigger pt1, "
! " pg_trigger pt2, "
! " pg_proc pp, "
! " pg_trigger pt, "
! " pg_class pc1, "
! " pg_namespace pn "
! " pg_namespace pn1 "
"WHERE pt.tgrelid = pc.oid "
"AND pp.oid = pt.tgfoid "
"AND pt1.tgconstrrelid = pc.oid "
--- 3491,3506 ----
" pc1.oid, "
" pc1.relname, "
" pn.nspname "
! "FROM pg_catalog.pg_class pc, "
! " pg_catalog.pg_proc pp1, "
! " pg_catalog.pg_proc pp2, "
! " pg_catalog.pg_trigger pt1, "
! " pg_catalog.pg_trigger pt2, "
! " pg_catalog.pg_proc pp, "
! " pg_catalog.pg_trigger pt, "
! " pg_catalog.pg_class pc1, "
! " pg_catalog.pg_namespace pn "
! " pg_catalog.pg_namespace pn1 "
"WHERE pt.tgrelid = pc.oid "
"AND pp.oid = pt.tgfoid "
"AND pt1.tgconstrrelid = pc.oid "
***************
*** 3841,3847 ****
{
if (conn->schema_support)
{
! schema_strcat(schema_needed, "%.*s", szPkTableOwner,
cbPkTableOwner, szPkTableName, cbPkTableName, conn);
sprintf(tables_query, "SELECT pt.tgargs, "
" pt.tgnargs, "
" pt.tgdeferrable, "
--- 3856,3862 ----
{
if (conn->schema_support)
{
! schema_strcat(schema_needed, "%.*s", szPkTableOwner,
cbPkTableOwner, szPkTableName, cbPkTableName, conn);
sprintf(tables_query, "SELECT pt.tgargs, "
" pt.tgnargs, "
" pt.tgdeferrable, "
***************
*** 3852,3867 ****
" pc1.oid, "
" pc1.relname, "
" pn.nspname "
! "FROM pg_class pc, "
! " pg_class pc1, "
! " pg_class pc2, "
! " pg_proc pp, "
! " pg_proc pp1, "
! " pg_trigger pt, "
! " pg_trigger pt1, "
! " pg_trigger pt2, "
! " pg_namespace pn "
! " pg_namespace pn1 "
"WHERE pt.tgconstrrelid = pc.oid "
" AND pt.tgrelid = pc1.oid "
" AND pt1.tgfoid = pp1.oid "
--- 3867,3882 ----
" pc1.oid, "
" pc1.relname, "
" pn.nspname "
! "FROM pg_catalog.pg_class pc, "
! " pg_catalog.pg_class pc1, "
! " pg_catalog.pg_class pc2, "
! " pg_catalog.pg_proc pp, "
! " pg_catalog.pg_proc pp1, "
! " pg_catalog.pg_trigger pt, "
! " pg_catalog.pg_trigger pt1, "
! " pg_catalog.pg_trigger pt2, "
! " pg_catalog.pg_namespace pn "
! " pg_catalog.pg_namespace pn1 "
"WHERE pt.tgconstrrelid = pc.oid "
" AND pt.tgrelid = pc1.oid "
" AND pt1.tgfoid = pp1.oid "
***************
*** 4197,4203 ****
{
strcpy(proc_query, "select proname, proretset, prorettype, "
"pronargs, proargtypes, nspname from "
! "pg_namespace, pg_proc where "
"pg_proc.pronamespace = pg_namespace.oid "
"and (not proretset)");
schema_strcat(proc_query, " and nspname like '%.*s'",
szProcOwner, cbProcOwner, szProcName, cbProcName, conn);
--- 4212,4218 ----
{
strcpy(proc_query, "select proname, proretset, prorettype, "
"pronargs, proargtypes, nspname from "
! "pg_catalog.pg_namespace, pg_catalog.pg_proc
where "
"pg_proc.pronamespace = pg_namespace.oid "
"and (not proretset)");
schema_strcat(proc_query, " and nspname like '%.*s'",
szProcOwner, cbProcOwner, szProcName, cbProcName, conn);
***************
*** 4334,4340 ****
QR_add_tuple(res, row);
while (isdigit(*params))
params++;
! }
}
QR_Destructor(tres);
/*
--- 4349,4355 ----
QR_add_tuple(res, row);
while (isdigit(*params))
params++;
! }
}
QR_Destructor(tres);
/*
***************
*** 4387,4393 ****
" proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS"
","
" '' as " "NUM_OUTPUT_PARAMS" ", '' as " "NUM_RESULT_SETS"
","
" '' as " "REMARKS" ","
! " case when prorettype = 0 then 1::int2 else 2::int2 end as
" "PROCEDURE_TYPE" " from pg_namespace, pg_proc"
" where pg_proc.pronamespace = pg_namespace.oid");
schema_strcat(proc_query, " and nspname like '%.*s'",
szProcOwner, cbProcOwner, szProcName, cbProcName, conn);
my_strcat(proc_query, " and proname like '%.*s'", szProcName,
cbProcName);
--- 4402,4410 ----
" proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS"
","
" '' as " "NUM_OUTPUT_PARAMS" ", '' as " "NUM_RESULT_SETS"
","
" '' as " "REMARKS" ","
! " case when prorettype = 0 then 1::int2 else 2::int2 end"
! " as " "PROCEDURE_TYPE" " from
pg_catalog.pg_namespace,"
! " pg_catalog.pg_proc"
" where pg_proc.pronamespace = pg_namespace.oid");
schema_strcat(proc_query, " and nspname like '%.*s'",
szProcOwner, cbProcOwner, szProcName, cbProcName, conn);
my_strcat(proc_query, " and proname like '%.*s'", szProcName,
cbProcName);
***************
*** 4446,4452 ****
}
}
return addcnt;
! }
static void
useracl_upd(char (*useracl)[ACLMAX], QResultClass *allures, const char *user,
const char *auth)
{
--- 4463,4469 ----
}
}
return addcnt;
! }
static void
useracl_upd(char (*useracl)[ACLMAX], QResultClass *allures, const char *user,
const char *auth)
{
***************
*** 4527,4539 ****
stmt->rowset_start = -1;
stmt->current_col = -1;
if (conn->schema_support)
! strncpy_null(proc_query, "select relname, usename, relacl,
nspname from pg_namespace, pg_class , pg_user where", sizeof(proc_query));
! else
! strncpy_null(proc_query, "select relname, usename, relacl from
pg_class , pg_user where", sizeof(proc_query));
if ((flag & PODBC_NOT_SEARCH_PATTERN) != 0)
{
if (conn->schema_support)
! {
schema_strcat(proc_query, " nspname = '%.*s' and",
szTableOwner, cbTableOwner, szTableName, cbTableName, conn);
}
my_strcat(proc_query, " relname = '%.*s' and", szTableName,
cbTableName);
--- 4544,4559 ----
stmt->rowset_start = -1;
stmt->current_col = -1;
if (conn->schema_support)
! strncpy_null(proc_query, "select relname, usename, relacl,
nspname"
! " from pg_catalog.pg_namespace, pg_catalog.pg_class ,"
! " pg_catalog.pg_user where", sizeof(proc_query));
! else
! strncpy_null(proc_query, "select relname, usename, relacl"
! " from pg_class , pg_user where", sizeof(proc_query));
if ((flag & PODBC_NOT_SEARCH_PATTERN) != 0)
{
if (conn->schema_support)
! {
schema_strcat(proc_query, " nspname = '%.*s' and",
szTableOwner, cbTableOwner, szTableName, cbTableName, conn);
}
my_strcat(proc_query, " relname = '%.*s' and", szTableName,
cbTableName);
***************
*** 4552,4559 ****
my_strcat(proc_query, " relname like '%.*s' and",
esc_table_name, escTbnamelen);
}
if (conn->schema_support)
! strcat(proc_query, " pg_namespace.oid = relnamespace and");
! strcat(proc_query, " pg_user.usesysid = relowner");
if (res = CC_send_query(conn, proc_query, NULL, CLEAR_RESULT_ON_ABORT),
!res)
{
SC_set_error(stmt, STMT_EXEC_ERROR, "PGAPI_TablePrivileges
query error");
--- 4572,4579 ----
my_strcat(proc_query, " relname like '%.*s' and",
esc_table_name, escTbnamelen);
}
if (conn->schema_support)
! strcat(proc_query, " pg_namespace.oid = relnamespace and");
! strcat(proc_query, " pg_user.usesysid = relowner");
if (res = CC_send_query(conn, proc_query, NULL, CLEAR_RESULT_ON_ABORT),
!res)
{
SC_set_error(stmt, STMT_EXEC_ERROR, "PGAPI_TablePrivileges
query error");

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
<Prev in Thread] Current Thread [Next in Thread>
Previous by Date:  Re: password leak in mylog thru win odbc, pg
Next by Date:  Re: ODBC driver settings for BLOBs, Andreas Pflug
Previous by Thread:  ODBC driver settings for BLOBs, Andreas Pflug
Next by Thread:  blob inserted from linux not readable by MS Access, Max
Indexes:  [Date] [Thread] [Top] [All Lists]

Google Custom Search
Recently Viewed:
user-groups.jax...    php.zend.framew...    os.solaris.open...    web.quixote.use...    java.openjdk.ho...    ietf.secmech/20...    gnu.glpk/2004-0...    recreation.cars...    network.smokepi...    linux.drivers.i...    cms.opencms.dev...    fonts.gfontview...    text.xml.soap.u...    voip.nist-sip/2...    debian.ports.hp...    xfree86.interna...    science.biology...    qnx.openqnx.dev...    mail.sylpheed.c...    busybox/bios/20...    emulators.kvm.s...    hardware.openco...    apple.fink.begi...    kde.german/2006...   
Home | advertise | OSDir is an inevitable website. super tiny logo

Free Magazines

Cisco News
Receive a free quarterly e-newsletter with exclusive articles on how Cisco IT uses its own products and solutions to enable the business.
subscribe

Systems Management News, the newspaper for IT systems administration and data center managers! Each issue of Systems Management News is chock-full of news and analysis to help you understand what's happening in your field.
subscribe

The Enterprise Newsweekly eWeek is the essential technology information source for builders of e-business.
subscribe

Oracle Magazine Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world's largest enterprise software company.
subscribe

Total Telecom Total Telecom is "The Economist of the communications industry".
subscribe

Navigation