Bug #127146 for DBD-ODBC: DBD::OBCD prepare fails for a select if a column name contains a quote char.

Wed Sep 19 09:22:24 2018 steve [...] yewtc.demon.co.uk - Ticket created

Subject:	DBD::OBCD prepare fails for a select if a column name contains a quote char.
Date:	Wed, 19 Sep 2018 13:22:02 +0000
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	Steve Rogerson <steve [...] yewtc.demon.co.uk>

The quote char is a valid character for column names in MSSQL (why, why, why...) anyway it is. It seems to screw up prepare. Without prepare all is well. SELECT [edets key], [edets no PR's] FROM [EVENT DETAILS] WHERE ( [edets key] =433) Works ok, but $sth = $db->prepare("SELECT [edets key], [edets no PR's] FROM [EVENT DETAILS] WHERE ( [edets key] = ?") $sth->execute(433) fails with BD::ODBC::st execute failed: called with 1 bind variables when 0 are needed. Also $sth = $db->prepare("SELECT [edets key] FROM [EVENT DETAILS] WHERE ( [edets key] = ?") $sth->execute(433) works fine. At first glance this seems to be down to dbd_preparse in dbdimp.c.

Wed Sep 19 10:04:51 2018 bohica [...] ntlworld.com - Correspondence added

On Wed Sep 19 09:22:24 2018, steve@yewtc.demon.co.uk wrote: Show quoted text

> The quote char is a valid character for column names in MSSQL (why, > why, why...) anyway it is. It seems to screw up prepare. > > Without prepare all is well. > > SELECT [edets key], [edets no PR's] FROM [EVENT DETAILS] WHERE ( > [edets key] =433) > > Works ok, but > > $sth = $db->prepare("SELECT [edets key], [edets no PR's] FROM [EVENT > DETAILS] WHERE ( [edets key] = ?") > > $sth->execute(433) fails with > > > BD::ODBC::st execute failed: called with 1 bind variables when 0 are > needed. > > Also > > $sth = $db->prepare("SELECT [edets key] FROM [EVENT DETAILS] WHERE ( > [edets key] = ?") > > $sth->execute(433) > > works fine. > > > At first glance this seems to be down to dbd_preparse in dbdimp.c. > >

It is bound to be the preparse as it is trying to work out how many parameters you have and it needs to know the quoting character to do its job properly. The code assumes the quote character is ' or " and certainly doesn't recognise []. The preparse wouldn't strictly be necessary if it wasn't for a) DBI allowing :name parameters in its spec and b) ODBC drivers which can't do SQLNumParams/SQLDescribeParams. I haven't looked at this code in ages (most of it predates even but it could probably be changed to handle [] but it would be better if it worked on what the ODBC driver said was the quote character. I'm open to patches. Martin -- Martin J. Evans Wetherby, UK

Wed Sep 19 10:04:52 2018 The RT System itself - Status changed from 'new' to 'open'

Wed Sep 19 15:12:50 2018 steve [...] yewtc.demon.co.uk - Correspondence added

Subject:	Re: [rt.cpan.org #127146] DBD::OBCD prepare fails for a select if a column name contains a quote char.
Date:	Wed, 19 Sep 2018 20:12:26 +0100
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Steve Rogerson <steve [...] yewtc.demon.co.uk>

On 19/09/2018 15:04, Martin J Evans via RT wrote: Show quoted text

> > It is bound to be the preparse as it is trying to work out how many parameters you have and it needs to know the quoting character to do its job properly. The code assumes the quote character is ' or " and certainly doesn't recognise []. The preparse wouldn't strictly be necessary if it wasn't for a) DBI allowing :name parameters in its spec and b) ODBC drivers which can't do SQLNumParams/SQLDescribeParams. > > I haven't looked at this code in ages (most of it predates even but it could probably be changed to handle [] but it would be better if it worked on what the ODBC driver said was the quote character. > > I'm open to patches.

Are [] valid in identifiers in any other case? If not horrible a kludge could be to change if ((*src == '\'') || (*src == '"')) { literal_ch = *src; /* save quote chr */ next_state = LITERAL; to if ((*src == '[') || (*src == '\'') || (*src == '"')) { literal_ch = *src; /* save quote chr */ if (literal_ch == '[') { literal_ch = ']') next_state = LITERAL; How would you find out the quote character in this context? Steve