Thread: ODBC 7.0006 bugs
I found some bugs working with Access'97-ODBC 7.006 on a Win98 client and
PostgreSQL 7.1.2 on a Redhat 6.2 server:
1) subselect side-effects:
if I run the following query:
select expr1 as field1, expr2 as field2, ... expr-j as field-j, ... expr-n
as field-n;
where expr-j is a subselect (e.g. select count(*) from table_x)
I only have results for columns 1 .. j . the driver omits columns j+1 .. n
(all the columns subsequent the subselect expression)
I can workaround the problem putting the subselect as last expression, but
there is no solution when the query contains more than one subselects
2) updating a table without row versioning:
I can't update a table with a float field (but the problem could affect also
other kind of fields) with row-versioning deactivated.
This happens because the driver executes the query :
UPDATE "d_info" SET "conf"='1'::float8 WHERE "id" = 758 AND "descr" = 'IntA
EPD-TX' AND "conf" IS NULL AND "id_dist" IS NULL AND "mean" =
'1200.51020408163'::float8 AND "sigma" = '944.166085407992'::float8 AND
"nelems" = 49 AND "ntotelems" = 1838 AND "noutrange" = 0 AND "nintrvs" = 400
AND "gp1" = 9 AND "gp2" IS NULL AND "unit" = 'ms' AND "norm" = '0.1'::float8
AND "nsigma" = 5 AND "type" = 0 AND "filterzero" = '1' AND "sql_descr" =
'SELECT descr FROM aggregates_master WHERE idagg=gp1_param;''
as you can see the driver uses all fields to identify the row even if I
specified in Access that the unique identifier is column "id".
I suppose that the problem is that "mean" and "sigma" fields (in general
every float field with a lot of decimal places) aren't the same as they're
represented in ascii format, so the backend updates 0 rows and Access
reports a strange error about a condivision violation.
If I activate the row versioning the driver works correctly (UPDATE "d_info"
SET "conf"='1'::float8 WHERE "id" = value and xmin = ...)
I suppose the same problem could affect also a DELETE statement
3) The driver setting "Use Declare/Fetch" is driver and not datasource
dependent. This means that I can't open tables from a datasource using a
normal select and from another datasource with a declare/fetch statement
I had to work around renaming psqlodbc.dll in psql2dbc.dll and building
another driver profile in the registry.
I think that moving this feature from driver to datasource settings would be
very useful. In general I think that making all settings on a per-datasource
basis
would be helpful.
4) I can't execute a query like
select 'hello world';
or
select 'hello world'::text;
I get the error
Invalid column number in describe col.
instead
select text('hello world');
works fine.
Similarly in any query I cant simply put a string expression specifying the
field name:
select ... , 'string expression' as str_expr, ... from ....
or
select ... , 'string expression'::text as str_expr, ... from ....
reports the same error previously specified
instead
select ... , 'string expression' , ... from .... (without 'as'
clause)
or
select ... , text('string expression') as str_expr, ... from ....
(with
function-like explicit typecasting)
works fine.
5) The odbc connection logfile generated contains the password in a plain
text format.
I think this could be a security violation and should be removed.
Any comment is appreciated
Regards,
David Ciarniello
David Ciarniello wrote: > > I found some bugs working with Access'97-ODBC 7.006 on a Win98 client and > PostgreSQL 7.1.2 on a Redhat 6.2 server: > > 1) subselect side-effects: > if I run the following query: > > select expr1 as field1, expr2 as field2, ... expr-j as field-j, ... expr-n > as field-n; > where expr-j is a subselect (e.g. select count(*) from table_x) > > I only have results for columns 1 .. j . the driver omits columns j+1 .. n > (all the columns subsequent the subselect expression) You seem to turn on *Parse Statements* option setting. psqlodbc driver doesn't parse compilcated queries well. OK I will take care of this. > 2) updating a table without row versioning: > > I can't update a table with a float field (but the problem could affect also > other kind of fields) with row-versioning deactivated. It's a known issuea and psqlodbc driver isn't guilty. If row-versioning is activated, appications could use an optimistic concurrency control using row versions, otherwise they have to use an optimistic concurrency control using values. The main problem is that PostgreSQL servers doesn't return float values with sufficient precisions. For example, float4 -> 6, float8 -> 15. > 3) The driver setting "Use Declare/Fetch" is driver and not datasource [snip] > In general I think that making all settings on a per-datasource > basis would be helpful. > Agreed. I've long wanted to do it but have had no time to do so unfortunately. > 4) I can't execute a query like > This also seems to be a problem of *Parse Statements*. I will take care of this too but I'm not sure. > 5) The odbc connection logfile generated contains the password in a plain > text format. > I think this could be a security violation and should be removed. > OK I would remove it if there's no objection. regards, Hiroshi Inoue
> David Ciarniello wrote:
> >
> > I found some bugs working with Access'97-ODBC 7.006 on a Win98 client
and
> > PostgreSQL 7.1.2 on a Redhat 6.2 server:
> >
> > 1) subselect side-effects:
> > if I run the following query:
> >
> > select expr1 as field1, expr2 as field2, ... expr-j as field-j, ...
expr-n
> > as field-n;
> > where expr-j is a subselect (e.g. select count(*) from table_x)
> >
> > I only have results for columns 1 .. j . the driver omits columns j+1 ..
n
> > (all the columns subsequent the subselect expression)
>
> You seem to turn on *Parse Statements* option setting.
> psqlodbc driver doesn't parse compilcated queries well.
> OK I will take care of this.
Yes. Disabling the "Parse Statement" it works fine! Thank you.
But now I have to create a third driver installation, with a datasource
dedicated only to pass-through queries because "Parse Statement" is a
driver-based setting :-((
>
> > 2) updating a table without row versioning:
> >
> > I can't update a table with a float field (but the problem could affect
also
> > other kind of fields) with row-versioning deactivated.
>
> It's a known issuea and psqlodbc driver isn't guilty.
> If row-versioning is activated, appications could use
> an optimistic concurrency control using row versions,
> otherwise they have to use an optimistic concurrency
> control using values.
> The main problem is that PostgreSQL servers doesn't
> return float values with sufficient precisions.
> For example, float4 -> 6, float8 -> 15.
>
Now I understood a thing that appeared weird to me; the driver uses all
fields to identify a row (even if there's a unique column) to avoid
concurrent changes.
I think that the problem could be solved identifying some fields with a
between operator (with a very small range) instead of an equal (=) operator;
this just to workaround the problem.
Let's say that the backend returns
a.bcde
you can identify the field with a condition
field between a.bcde and a.bcde9
The probability of a collision is quite the same as with the = operator.
The same rule (of course changed) could be applied with all other datatypes
that create this kind of problems.
Of course it's enormously simpler to adopt the row versioning technique :-))
Regards,
David Ciarniello
David Ciarniello wrote: > > > David Ciarniello wrote: > > > > > > I found some bugs working with Access'97-ODBC 7.006 on a Win98 client > and > > > PostgreSQL 7.1.2 on a Redhat 6.2 server: > > > [snip] > > > > > 2) updating a table without row versioning: > > > > > > I can't update a table with a float field (but the problem could affect > also > > > other kind of fields) with row-versioning deactivated. > > [snip] > > Now I understood a thing that appeared weird to me; the driver uses all > fields to identify a row (even if there's a unique column) to avoid > concurrent changes. > I think that the problem could be solved identifying some fields with a > between operator (with a very small range) instead of an equal (=) operator; Unfortunately it's the application like DAO-JET not the driver which issues the queries. regards, Hiroshi Inoue
Hello,
1) Could be the Jet query handler is throwing up over this. Try as a
pass through query
2) This is so that Access can tell wether some one else has altered
the record you are working on. If you don't use row versioning it needs to
look to see if any fields have changed (which creates the problem you
described). Also Access can be a bit funny about text/memo fields.
3) I can see you're point. However I tend not to use DSN's but
rather connection strings so its helpful to be able to turn on logging with
out editing the program.
4) Are you using Jet for this? Select 'Hello World'; works fine in
psql.It also works just fine in Access query builder (although select 'hello
world'::text doesn't). How are you entering this query? Also I believe
typecasting by ::text is a Postgresism. I think the SQL'92 way is
CAST('Hello World' AS text). Neither works with Access queries (AFAIK). In
Access something like CStr('Hello World') uses the VB string conversion
routine and should be applicable most places (NOT pass through queries
though).
5) I disagree. If I'm having problems connecting I want to see all
the options in the connection string. Don't log when you're not debugging,
it slows everything down.
- Stuart
> -----Original Message-----
> From: David Ciarniello [SMTP:brainlost@rocketmail.com]
> Sent: Thursday, July 05, 2001 10:50 AM
> To: pgsql-odbc@postgresql.org
> Subject: ODBC 7.0006 bugs
>
> I found some bugs working with Access'97-ODBC 7.006 on a Win98 client and
> PostgreSQL 7.1.2 on a Redhat 6.2 server:
>
> 1) subselect side-effects:
> if I run the following query:
>
> select expr1 as field1, expr2 as field2, ... expr-j as field-j, ... expr-n
> as field-n;
> where expr-j is a subselect (e.g. select count(*) from table_x)
>
> I only have results for columns 1 .. j . the driver omits columns j+1 .. n
> (all the columns subsequent the subselect expression)
> I can workaround the problem putting the subselect as last expression, but
> there is no solution when the query contains more than one subselects
>
> 2) updating a table without row versioning:
>
> I can't update a table with a float field (but the problem could affect
> also
> other kind of fields) with row-versioning deactivated.
> This happens because the driver executes the query :
>
> UPDATE "d_info" SET "conf"='1'::float8 WHERE "id" = 758 AND "descr" =
> 'IntA
> EPD-TX' AND "conf" IS NULL AND "id_dist" IS NULL AND "mean" =
> '1200.51020408163'::float8 AND "sigma" = '944.166085407992'::float8 AND
> "nelems" = 49 AND "ntotelems" = 1838 AND "noutrange" = 0 AND "nintrvs" =
> 400
> AND "gp1" = 9 AND "gp2" IS NULL AND "unit" = 'ms' AND "norm" =
> '0.1'::float8
> AND "nsigma" = 5 AND "type" = 0 AND "filterzero" = '1' AND "sql_descr" =
> 'SELECT descr FROM aggregates_master WHERE idagg=gp1_param;''
>
> as you can see the driver uses all fields to identify the row even if I
> specified in Access that the unique identifier is column "id".
> I suppose that the problem is that "mean" and "sigma" fields (in general
> every float field with a lot of decimal places) aren't the same as they're
> represented in ascii format, so the backend updates 0 rows and Access
> reports a strange error about a condivision violation.
>
> If I activate the row versioning the driver works correctly (UPDATE
> "d_info"
> SET "conf"='1'::float8 WHERE "id" = value and xmin = ...)
>
> I suppose the same problem could affect also a DELETE statement
>
> 3) The driver setting "Use Declare/Fetch" is driver and not datasource
> dependent. This means that I can't open tables from a datasource using a
> normal select and from another datasource with a declare/fetch statement
> I had to work around renaming psqlodbc.dll in psql2dbc.dll and building
> another driver profile in the registry.
> I think that moving this feature from driver to datasource settings would
> be
> very useful. In general I think that making all settings on a
> per-datasource
> basis
> would be helpful.
>
> 4) I can't execute a query like
>
> select 'hello world';
> or
> select 'hello world'::text;
>
> I get the error
> Invalid column number in describe col.
>
> instead
>
> select text('hello world');
>
> works fine.
>
> Similarly in any query I cant simply put a string expression specifying
> the
> field name:
>
> select ... , 'string expression' as str_expr, ... from ....
> or
> select ... , 'string expression'::text as str_expr, ... from ....
>
> reports the same error previously specified
> instead
>
> select ... , 'string expression' , ... from .... (without 'as'
> clause)
>
> or
>
> select ... , text('string expression') as str_expr, ... from ....
> (with
> function-like explicit typecasting)
>
> works fine.
>
>
> 5) The odbc connection logfile generated contains the password in a plain
> text format.
> I think this could be a security violation and should be removed.
>
>
> Any comment is appreciated
>
> Regards,
> David Ciarniello
>
>
>
>
>
>
>
> Hello,
> 1) Could be the Jet query handler is throwing up over this. Try as a
> pass through query
it was a pass-through query.
the problem doesn't affect connection with "Parse statement" setting
disabled
(so I suppose it is the parser in the driver that causes it)
> 2) This is so that Access can tell wether some one else has altered
> the record you are working on. If you don't use row versioning it needs to
> look to see if any fields have changed (which creates the problem you
> described). Also Access can be a bit funny about text/memo fields.
> 3) I can see you're point. However I tend not to use DSN's but
> rather connection strings so its helpful to be able to turn on logging
with
> out editing the program.
Can't we move all settings on a datasource basis continuing to support
all parametrs on connection strings ?
> 4) Are you using Jet for this? Select 'Hello World'; works fine in
> psql.It also works just fine in Access query builder (although select
'hello
> world'::text doesn't). How are you entering this query? Also I believe
> typecasting by ::text is a Postgresism. I think the SQL'92 way is
> CAST('Hello World' AS text). Neither works with Access queries (AFAIK). In
> Access something like CStr('Hello World') uses the VB string conversion
> routine and should be applicable most places (NOT pass through queries
> though).
I found the problem was the parser (as point 1)
> 5) I disagree. If I'm having problems connecting I want to see all
> the options in the connection string. Don't log when you're not debugging,
> it slows everything down.
You can find the authentication response into the backend logs (like the
(in)famous "password authentication failed for user admin")
Instead somebody could activate the logger without my authorization
(consider a pc that shares the hard drive, just put the right reg file into
the startup folder and wait for the next reboot - considering win9x
stability you don't have to wait too much :-)) - so that the log can be
produced... you can grab the password from a network environment even
without ever seeing that pc).
I think it's a security risk.
> -----Original Message-----
> From: David Ciarniello [SMTP:brainlost@rocketmail.com]
> Sent: Friday, July 06, 2001 11:56 AM
> To: Henshall, Stuart - WCP
> Cc: pgsql-odbc@postgresql.org
> Subject: R: ODBC 7.0006 bugs
>
>
Makes me glad I havn't used the parse option (what is it for?)
> > 3) I can see you're point. However I tend not to use DSN's but
> > rather connection strings so its helpful to be able to turn on logging
> with
> > out editing the program.
>
> Can't we move all settings on a datasource basis continuing to support
> all parametrs on connection strings ?
>
I was meaning so that I didn't have to change my connect string at
all. Maybe having all the options available as driver defaults which are
only overwritten if they are also in the datasource string. I must admit I
don't really know about the drivers internals so have no idea how tricky
that would be.
> > 5) I disagree. If I'm having problems connecting I want to see all
> > the options in the connection string. Don't log when you're not
> debugging,
> > it slows everything down.
>
> You can find the authentication response into the backend logs (like the
> (in)famous "password authentication failed for user admin")
>
yes, but it doesn't give the ODBC side of the story.
> Instead somebody could activate the logger without my authorization
> (consider a pc that shares the hard drive, just put the right reg file
> into
> the startup folder and wait for the next reboot - considering win9x
> stability you don't have to wait too much :-)) - so that the log can be
> produced... you can grab the password from a network environment even
> without ever seeing that pc).
> I think it's a security risk.
>
True. Howeversomeone could just make a little alteration to
the source, recompile the ODBC driver then drop it into \windows\system.
Having sensitive areas of the disk shared is inherently unsafe. Or someone
could write a wrapper DLL that just passed everything along while grabbing
the PWD. Or drop a Trojan into your startup to expose your PC. I suppose
these would be trickier, but not ridiculously so. Maybe have two driver
builds. A production model that disables logging (plus anything else deemed
a risk) and a developer version that allows it to be enabled. I really must
get MSVC so I can fiddle with the driver like this.
- Stuart
> Makes me glad I havn't used the parse option (what is it for?) the only thing I know about it has been taken form odbc faq: Parse Statements option -- driver parses the SQL statement and retrieves characteristics such as precision, nullability, aliases, etc. for the columns. I'd like to know in which situations it's useful and when it can be safely turned off. > > > > 5) I disagree. If I'm having problems connecting I want to see all > > > the options in the connection string. Don't log when you're not > > debugging, > > > it slows everything down. > > > > You can find the authentication response into the backend logs (like the > > (in)famous "password authentication failed for user admin") > > > yes, but it doesn't give the ODBC side of the story. > > > Instead somebody could activate the logger without my authorization > > (consider a pc that shares the hard drive, just put the right reg file > > into > > the startup folder and wait for the next reboot - considering win9x > > stability you don't have to wait too much :-)) - so that the log can be > > produced... you can grab the password from a network environment even > > without ever seeing that pc). > > I think it's a security risk. > > > True. Howeversomeone could just make a little alteration to > the source, recompile the ODBC driver then drop it into \windows\system. > Having sensitive areas of the disk shared is inherently unsafe. Or someone > could write a wrapper DLL that just passed everything along while grabbing > the PWD. Or drop a Trojan into your startup to expose your PC. I suppose > these would be trickier, but not ridiculously so. Maybe have two driver > builds. A production model that disables logging (plus anything else deemed > a risk) and a developer version that allows it to be enabled. I really must > get MSVC so I can fiddle with the driver like this. making two versions of odbc could be an idea. certainly in a production environment it's a risk to cache locally a plain text password. regards, David Ciarniello
On Fri, 6 Jul 2001, Hiroshi Inoue wrote: > > > 5) The odbc connection logfile generated contains the password in a plain > > text format. > > I think this could be a security violation and should be removed. > > > > OK I would remove it if there's no objection. Replacing the password with *'s would probably be best (?) my two cents...