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@candle.pha.pa.us> 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@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
? 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 =
relnamespaceand 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");