For those with a curiosity about the details of the ODBC / 64-bit problem, I 
think I understand the ODBC spec vs. implementation differences better. I 
think Microsoft’s definition of types is OK. It may be the (inconsistent?) way 
implementers have chosen to implement the spec that is an issue. In 
particular, the definition and/or use of the “SQLINTEGER”, “long”, 
and “SDWORD” types are causing us a problem.

ODBC SPEC AS I UNDERSTAND IT:

There is a type defined by ODBC called SQLINTEGER and a flag used to identify 
this type SQL_C_LONG. The SQLINTEGER is (according to ODBC specs) a typedef 
for “long int”. So it would seem logical to me that if a user passes a 
variable of type SQLINTEGER to a given ODBC call then the SQL_C_LONG flag 
should be used to tell the ODBC function what type of variable was passed. The 
way I see it, the proper behavior for the ODBC driver would be to cast a 
variable from a void* to a SQLINTEGER*, when given a SQL_C_LONG flag.

One other thing, I noticed that the ODBC spec states that the “BufferLength” 
variable is ignored when the data variable in question is not “variable 
length” (e.g., byte arrays, char buffers, etc.). So this is NOT a valid way to 
protect against overwriting the buffer. What I think would have been better in 
the RLS code would have been to use SQLINTEGER for the variables rather than 
what was done originally which was to just make them “int” types.

Bottom line, RLS and other ODBC apps must depend on the SQL_C_LONG flag and 
the SQLINTEGER (or other) typedefs. Unfortunately, as you will see below, they 
cannot depend on them in reality. Therefore, even if we had used SQLINTEGER 
instead of int during ODBC calls, we still would have this exact problem.

PROBLEMS EVERYWHERE:

More details below, but suffice to say there are inconsistencies between 
unixODBC and iodbc in how they specify various typedefs and between MySQL ODBC 
and Postgres ODBC in how they cast types based on SQL_C_LONG (and other) 
flags. To make matters worse, Microsft’s ODBC spec defines SQLINTEGER as 
a “long int” which thus varies in precision based on the computer architecture 
(4 or 8 bytes depending on 32bit or 64bit architectures), thus leaving things 
open to confusion. It is not pretty.

MYSQL PROBLEM:

The problem with the MySQL ODBC driver is that it casts the data variable from 
a void* to a long* (based on a SQL_C_LONG flag) rather than using the 
SQLINTEGER type defined by the ODBC spec. So yes, anyone can read the spec and 
see that the SQLINTEGER typedef anyways is a “long int”, but it is not good 
practice that they didn’t stick to using the typedef. This definitely seems 
wrong to me.

POSTGRES PROBLEM:

I checked the Postgres ODBC driver and it casts the data variable from a void* 
to a SDWORD* (based on a SQL_C_LONG flag). This works out because the DWORD is 
a typedef for a “int” when using unixODBC. DWORD is actually a Microsoft type 
(at least, that’s where I know it from) but for some reason the ODBC managers 
(unixODBC and iodbc) both make sure it is defined. SDWORD is a fixed-precision 
type always to be a 32-bit integer (AFAIR).

Still, even with Postgres, I do not understand why they do not cast the 
variable to a SQLINTEGER. Again, unless I misunderstood the spec, it seems 
like better practice to stick to using the spec’s typedef for SQLINTEGER 
rather than using anything else – even if SDWORD should have a consistent 
meaning across architectures. It is also divergent from the ODBC spec since 
the SQL_C_LONG indicates a SQLINTEGER which is a typedef for “long int”. It 
seems that Postgres may be more wrong than MySQL though they are both wrong 
ultimately IMO.

THE unixODBC AND iodbc INCONSISTENCY PROBLEMS:

The unixODBC library makes some adjustments to its typedefs based on whether 
the architecture is 64-bit. For instance on a SuSE box like the oceans11 LANL 
machine, unixODBC will conditionally typedef the SDWORD type as a 32-bit 
integer “int” whereas iodbc blindly typedefs a SDWORD to a “long” thus making 
it a 64-bit integer on SuSE. From what I understand the SDWORD is always 
supposed to be a 32-bit value, thus making iodbc somewhat broken on a 64-bit 
platform. It also means that RLS WILL NOT WORK when using Postres with iodbc 
on a 64-bit machine.

SUMMARY OF PROBLEMS:

* unixODBC provides reasonable support for 64-bit platforms

* iodbc is broken for 64-bit platforms

* Postgres may be somewhat broken for 64-bit platforms but it will work with 
RLS with unixODBC on 64-bit platforms because we too are assuming SQL_C_LONG 
means 32-bit always (but it may just be that RLS and Postgres make the same 
mistake)

* MySQL is broken on 64-bit platforms irregardless of using unixODBC or iodbc

* RLS is somewhat broken on 64-bit platforms because we directly use “int” 
instead of SQLINTEGER – having said that, even if we did it the right way by 
following Microsoft’s ODBC spec and using the SQLINTEGER type, it still 
wouldn’t have worked because of the fact neither driver casts to the 
SQLINTEGER (which is what I would assert is the right thing for all to be 
doing).

THOUGHTS ON FIXING THE PROBLEMS:

Solving this problem will amount to getting Microsoft, unixODBC, iodbc, MySQL, 
and Postgres to be consistent in how they define the specification 
(Microsoft), typedef per the spec (unixODBC, iodbc), and then properly 
implement type conversions per the spec (MySQL, Postgres). So that seems like 
a hopeless proposition.

The “right” solution as I see it is to make RLS use integer variables of type 
SQLINTEGER when calling ODBC routines, however, this will not work because of 
(what I consider to be) bugs in the MySQL and Postgres drivers.

What makes sense to me at this point is to continue with the RLS fix the way I 
was going about it. That is, to pass a variable of type “long int” when 
flagging with SQL_C_LONG. I have tested with MySQL and Postgres and it appears 
to work. So providing a 64-bit buffer when the driver will cast to a 32-
bit “int” (as will be the case with Postgres) does not cause any apparent 
problems.

It isn’t pretty though.

Created an attachment (id=730) [details]
Diff with unified context for readability

This diff illustrates the code changes that appear sufficient to work around
the odbc bugs.

Created an attachment (id=731) [details]
Patch to db.c to work around bugs in odbc software

This patch has been sufficient so far in working around the odbc bugs.

Committed to HEAD.

Also, updated in globus_4_0_branch.