freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
[freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes
- 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.
-
[freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes,
Richard Hughes, 08/07/2014
- Re: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes, Frediano Ziglio, 08/08/2014
- Re: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes, Frediano Ziglio, 08/10/2014
Archive powered by MHonArc 2.6.24.