Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 127146
Status: open
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: steve [...] yewtc.demon.co.uk
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 697b
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.
Download (untitled) / with headers
text/plain 1.4k
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
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>
Download (untitled) / with headers
text/plain 1.3k
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


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.