[prev in list] [next in list] [prev in thread] [next in thread] 

List:       freetds
Subject:    Re: [freetds] Mangled SQL due to mis-tracking of quotes and ODBC escapes
From:       Frediano Ziglio <freddy77 () gmail ! com>
Date:       2014-08-10 11:57:18
Message-ID: CAHt6W4cse-aGGioZVo6FM1_-5mdNWYF84J-x2t=3G_iv=M=ZNg () mail ! gmail ! com
[Download RAW message or body]

Backport patch in 0.91 branch.

Frediano

Frediano


2014-08-07 19:58 GMT+01:00 Richard Hughes <cyreve@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@lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 
_______________________________________________
FreeTDS mailing list
FreeTDS@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


[prev in list] [next in list] [prev in thread] [next in thread]