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.