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

2014-08-07 19:58 GMT+01:00 Richard Hughes <[email protected]>:

> 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.
>
>
Surely is not right. Committed a patch in git master, see
https://gitorious.org/freetds/freetds/.

Frediano