Skip to Content.
Sympa Menu

freetds - [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: Richard Hughes <cyreve AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes
  • Date: Thu, 7 Aug 2014 19:58:04 +0100

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.





Archive powered by MHonArc 2.6.24.

Top of Page