search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
357 stars 99 forks source link

Backslashes in queries #314

Open jef-sure opened 1 year ago

jef-sure commented 1 year ago

Issue report

Backslashes in queries

Operating system

PRETTY_NAME="Ubuntu 22.04.1 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.1 LTS (Jammy Jellyfish)"

Version of tds_fdw

tds_fdw        | 2.0.2   | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Version of PostgreSQL

PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit

Version of FreeTDS

ii  freetds-common                                1.3.6-1                                 all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                                   1.3.6-1                                 amd64        MS SQL and Sybase client library (static libs and headers)

Sentences, data structures, data

select * from tsqldbo."AD_USERS" au  where au."DistinguishedName" = 'CN=Surname\, Firstname';
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

tsqldbo."AD_USERS" is a table from Microsoft SQL Server Standard (64-bit) 13.0.4522.0, DistinguishedName is a column from this table. When I try to select a row from the table and my query value contains backslash, I get error. The only suspicious code I found is from deparse.c:

    /*
     * Rather than making assumptions about the remote server's value of
     * standard_conforming_strings, always use E'foo' syntax if there are any
     * backslashes.  This will fail on remote servers before 8.1, but those
     * are long out of support.
     */
    if (strchr(val, '\\') != NULL)
        appendStringInfoChar(buf, ESCAPE_STRING_SYNTAX);
    appendStringInfoChar(buf, '\'');
juliogonzalez commented 1 year ago

I can't tell what the fix would be, but this is indeed a real bug.

I can reproduce this issue by adding a backslash to our varchar test:

It fails when running SELECT * FROM postgresql14_ubuntu2004_test.varchar WHERE value = 'this is a string with a \ backslash';