MySQL Bugs: #32989: MyODBC Crystal Reports can't connect to a tbl if a field name has a single quote

Bug #32989	MyODBC Crystal Reports can't connect to a tbl if a field name has a single quote
Submitted:	5 Dec 2007 4:27	Modified:	14 Mar 2008 18:43
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.22	OS:	Windows (xp2 sp2)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	crystal reports developer XI revision 1

Description:
When trying to use a MySQL table that has a field with a single quote in the name in Crystal Reports an error is received, and the table fails to open.

This is a snip from the attached ODBC trace of the steps below:

crw32           1658-1548	EXIT  SQLColumnsW  with return code -1 (SQL_ERROR)
		HSTMT               07E52A80
		WCHAR *             0x7FE55564 [      -3] "xtreme\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x7FE554FC [      -3] "foo\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 

		DIAG [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Unknown column 'foo\'s field' in 'field list' (1054) 

How to repeat:
mysql>
use test;
create table foo (c1 int primary key, `foo's field` char(20));
insert into foo values (1, "test");

Steps:
0. Create a DSN using MyODBC 3.51 and database "test"
1. Open Crystal Reports and click the New Report button
2. When the wizard opens select New Connection / ODBC
3. Select the DSN name created above and click Finish
4. Select table "foo" from the new connection node, and click the right arrow to add it to the selected column.

Error is received

ODBC trace

Attachment: SQL.LOG (application/octet-stream, text), 87.97 KiB.

crystal report sql statement truncates column name that is more then 20 characters. Due to this truncation, report shows an error message "...Unknown column". Is any fix for it in MySQL odbc connector?

Works in odbcte32 with 5.0.54BK and 3.51.23:
Get Data All:
"foo.c1", "foo.foo's field"
1, "test"
1 row fetched from 2 columns.

fix+test

Attachment: bug32989.diff (application/octet-stream, text), 7.38 KiB.

Based on Jim's guidance, created a new method for escaping identifiers. When we send identifiers, they're always enclosed in backticks and thats the only character escaped. Fixed all occurrences where this was happening.

patch approved

Committed as rev1057. Will be released in 3.51.24.

A note has been added to the 3.51.24 changelog: 

Using tables with a single quote or other non-standard characters in the table or column names through ODBC would fail.