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

[Richard Hughes]

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.

[Frediano Ziglio]

Post by Richard Hughes
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.
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
''')
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.
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

[Frediano Ziglio]

Backport patch in 0.91 branch.

Frediano

Frediano

Post by Richard Hughes
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.
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
''')
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.
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