Skip to Content.
Sympa Menu

freetds - Re: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: Richard Hughes <richard.freetds AT rhughes.net>, FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes
  • Date: Sun, 10 Aug 2014 12:57:18 +0100

Backport patch in 0.91 branch.

Frediano

Frediano


2014-08-07 19:58 GMT+01:00 Richard Hughes <cyreve AT gmail.com>:

> Hi there,
>
> I wrote a stored procedure using some of SQL Server's XML functions
> and discovered that, upon trying to run the create proc statement
> through a FreeTDS/unixodbc connection, the content was getting mangled
> and hence rejected.
>
> Here's a reduced test case in Python:
>
> import pyodbc
> db =
> pyodbc.connect('DRIVER={FreeTDS};SERVER=198.51.100.1;PORT=1433;DATABASE=master;UID=sa;PWD=password;QuotedID=Yes;AnsiNPW=Yes',
> autocommit=True)
> cur = db.cursor()
> cur.execute('''
> set quoted_identifier on
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> -- This doesn't work
> declare @x xml = '';
> declare @d datetime;
> set @x.modify('insert attribute d {sql:variable("@d")} into (/e)[1]');
> ''')
>
> It looks like src/odbc/native.c:to_native() is not caring about the
> comment "--" so is losing track of whether we're in a string or not.
> It then concludes that {sql:variable("@d")} is an ODBC escape and
> transforms it into just :variable("@d"), which SQL Server rejects.
> This means that you can make the above test case work by simply
> removing the comment (or, indeed, adding another quote to it).
>
> Is this right?
>
> Richard.
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>




Archive powered by MHonArc 2.6.24.

Top of Page