Bug in PostgreSQL ODBC - in names with dollar

Hi,

I’m sorry, I really tried to register at pgsql-odbc list at postgresql.org, using different email addresses, but it didn’t’ work.

I saw that you may deal with this problem.

When UseServerSidePrepare is set to 1 (which is default, I guess), any names with $ in them cause error:

“The # of binded parameters < the # of parameter markers”

It’s both on Windows and Linux.

It seems that PG tries to use un-paired ‘$’ symbols as parameter marks for the server-side prepare/execute feature, even if there’s no PREPARE or EXECUTE statements.

I believe, PG should not parse any statements for $-parameters outside PREPARE/EXECUTE.

Simple ODBC program:

$ cat test_odbc.c

// gcc -I unixODBC-2.3.4/include -I unixODBC-2.3.4 -L libs -lodbc test_odbc.c -o test_odbc

// test_odbc 0|1

#include <stdio.h>

#include <time.h>

#include <sql.h>

#include <sqlext.h>

int main(int ac, char* av[])

{

SQLHENV env;

SQLHDBC dbc;

SQLRETURN ret;

SQLCHAR outstr[1024];

SQLSMALLINT outstrlen;

SQLHSTMT stmt;

if( ac!=2 || av[1][1]!='\0' || !(av[1][0]=='0' || av[1][0]=='1')) return 1;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

char conn[512];

sprintf( conn, "DSN=PGDriver;UseServerSidePrepare=%s", av[1] );

printf( "Connection: %s\n", conn );

ret = SQLDriverConnect(dbc, NULL, conn, SQL_NTS,

outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);

if(!SQL_SUCCEEDED(ret))

{

fprintf(stderr, "Failed to connect\n");

}

else

{

printf("Returned connection string was:\n\t%s\n", outstr);

char sql[512];

sprintf( sql, "create table T$_x_%u (f integer)", (int)time(0) );

printf( "Exec: %s\n", sql );

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

ret = SQLExecDirect(stmt, sql, SQL_NTS);

if(SQL_SUCCEEDED(ret))

printf("ok\n");

else

printf("error\n");

SQLFreeHandle(SQL_HANDLE_STMT,stmt);

SQLDisconnect(dbc);

}

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

return 0;

}

$ test_odbc 0

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was:

DSN=PGDriver;DATABASE=…;SERVER=…;PORT=5432;UID=…;PWD=…;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;=Fetch=100;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;ExtraSysTablePrefixes=;LFConversion=0;UpdatableCursors=1;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;

Exec: create table T$_x_1504022913 (f integer)

ok

$ test_odbc 1

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was:

DSN=PGDriver;DATABASE=…;SERVER=…;PORT=5432;UID=…;PWD=…;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;=Fetch=100;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;ExtraSysTablePrefixes=;LFConversion=0;UpdatableCursors=1;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=1;LowerCaseIdentifier=0;

Exec: create table T$_x_1504022916 (f integer)

error

Actually, probably any statement with ‘$’ in table/field name causes this error.

PGDriver - /usr/pgsql-9.6/lib/psqlodbcw.so

Driver Info

DBMS Name: PostgreSQL

DBMS Version: 9.6.3

PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

This is probably the same error as in https://www.postgresql.org/message-id/09ce3d91ce364987964726aa50a16155%40neosys.si <http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiY4NWFkOWEwNjFkMTgzOGRiYj01OUE1NzQ2NV8yMTIyOF8xMzEzN18xJiZkZTU4YWZhNmRjMzY5YTk9MTIyMyYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRm1lc3NhZ2UtaWQlMkYwOWNlM2Q5MWNlMzY0OTg3OTY0NzI2YWE1MGExNjE1NSU0MG5lb3N5cyUyRXNp>

Thank you.

Georgy Pruss.