MySQL Bugs: #20888: Backslash-Single Quote Sometimes Not Recognized by Connector/J v3.1.13

Bug #20888	Backslash-Single Quote Sometimes Not Recognized by Connector/J v3.1.13
Submitted:	6 Jul 2006 15:09	Modified:	26 Jul 2006 20:17
Reporter:	John Gillen
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	v3.1.13	OS:	Linux (Linux)
Assigned to:		Target Version:

Description:
We attempted to upgrade from mm.mysql (v2.0) to connector J (v3.1), but found that for
certain insert queries, the backslash escape of single quotes was not recognized,
resulting in a java.sql.SQLException: No value specified for parameter 1.

This occurred only on some TEXT columns within some tables, and only for certain rows in
those tables.  Changing the content within those rows could eliminate the problem.

We are running MySQL v4.0.25 on Redhat Linux v2.6.9-34.ELsmp
We use UTF-8 encoding.  Our application uses JDK v1.5.

How to repeat:
***** Table:

CREATE TABLE `Studies` (
  `UId` varchar(16) NOT NULL default '',
  `NCTId` varchar(31) NOT NULL default '',
  `OrgName` varchar(255) NOT NULL default '',
  `OrgStudyId` varchar(31) NOT NULL default '',
  `ProviderName` varchar(255) NOT NULL default '',
  `ProviderStudyId` varchar(31) NOT NULL default '',
  `LeadSponsor` varchar(255) NOT NULL default '',
  `IsINDStudy` varchar(15) NOT NULL default '',
  `INDNumber` varchar(31) NOT NULL default '',
  `INDSerialNumber` varchar(31) NOT NULL default '',
  `INDGrantor` varchar(255) NOT NULL default '',
  `BriefTitle` text NOT NULL,
  `OfficialTitle` text NOT NULL,
  `BriefSummary` text NOT NULL,
  `DetailedDescription` text NOT NULL,
  `Purpose` varchar(31) NOT NULL default '',
  `IntDesignType` varchar(31) NOT NULL default '',
  `IntDesignAllocation` varchar(31) NOT NULL default '',
  `IntDesignMasking` varchar(31) NOT NULL default '',
  `IntDesignControl` varchar(31) NOT NULL default '',
  `IntDesignAssignment` varchar(31) NOT NULL default '',
  `IntDesignEndpoint` varchar(31) NOT NULL default '',
  `ObsDesignType` varchar(31) NOT NULL default '',
  `ObsDesignDuration` varchar(31) NOT NULL default '',
  `ObsDesignSelection` varchar(31) NOT NULL default '',
  `ObsDesignTiming` varchar(31) NOT NULL default '',
  `Phase` varchar(31) NOT NULL default '',
  `Criteria` text NOT NULL,
  `Gender` varchar(15) NOT NULL default '',
  `MinAgeQuantity` varchar(15) NOT NULL default '',
  `MinAgeUnits` varchar(15) NOT NULL default '',
  `MaxAgeQuantity` varchar(15) NOT NULL default '',
  `MaxAgeUnits` varchar(15) NOT NULL default '',
  `TakesHealthy` varchar(15) NOT NULL default '',
  `TakesPatients` varchar(15) NOT NULL default '',
  `ExpectedEnrollment` varchar(15) NOT NULL default '',
  `StartDate` varchar(31) NOT NULL default '',
  `EndDate` varchar(31) NOT NULL default '',
  `LastFollowUpDate` varchar(31) NOT NULL default '',
  `LastDataEntryDate` varchar(31) NOT NULL default '',
  `OverallStatus` varchar(31) NOT NULL default '',
  `VerificationDate` varchar(31) NOT NULL default '',
  `IrbApproved` varchar(31) NOT NULL default '',
  `OvrConFirstName` varchar(63) NOT NULL default '',
  `OvrConMiddleName` varchar(31) NOT NULL default '',
  `OvrConLastName` varchar(63) NOT NULL default '',
  `OvrConDegrees` varchar(31) NOT NULL default '',
  `OvrConRole` varchar(63) NOT NULL default '',
  `OvrConPhone` varchar(31) NOT NULL default '',
  `OvrConExtension` varchar(15) NOT NULL default '',
  `OvrConEMail` varchar(255) NOT NULL default '',
  `OvrConBkpFirstName` varchar(63) NOT NULL default '',
  `OvrConBkpMiddleName` varchar(31) NOT NULL default '',
  `OvrConBkpLastName` varchar(63) NOT NULL default '',
  `OvrConBkpDegrees` varchar(31) NOT NULL default '',
  `OvrConBkpRole` varchar(63) NOT NULL default '',
  `OvrConBkpPhone` varchar(31) NOT NULL default '',
  `OvrConBkpExtension` varchar(15) NOT NULL default '',
  `OvrConBkpEMail` varchar(255) NOT NULL default '',
  `MeetsReqs` varchar(15) NOT NULL default '',
  `AutoDeemed` varchar(15) NOT NULL default '',
  `IrbApprovalNumber` varchar(31) NOT NULL default '',
  `IrbBoardName` varchar(255) NOT NULL default '',
  `IrbAffiliation` varchar(255) NOT NULL default '',
  `IrbChairName` varchar(255) NOT NULL default '',
  `IrbAddress` text NOT NULL,
  `IrbPhone` varchar(31) NOT NULL default '',
  `IrbExtension` varchar(15) NOT NULL default '',
  `IrbEmail` varchar(255) NOT NULL default '',
  `IrbApprovalStatus` varchar(31) NOT NULL default '',
  UNIQUE KEY `UId` (`UId`),
  KEY `NCTId` (`NCTId`),
  KEY `OrgName` (`OrgName`),
  KEY `IrbApprovalStatus` (`IrbApprovalStatus`)
) TYPE=MyISAM

***** Query:

INSERT INTO Studies VALUES ('S0000O6S00000039', '', 'NLM', '000sql-error', 'NLM_DES',
'S0000O6S', 'Boehringer Ingelheim Pharmaceuticals', 'No', '', '', '', 'Profile of
depressive symptoms in Parkinsons Disease', 'Profile of depressive symptoms in Parkinsons
Disease single quote \' here', 'To explore or establish the relationship between
cognitive, mood and motor symptoms in PD to scores on depression rating scales in a
naturalistic setting.\n\n', 'This is a multi-national, multicenter, prospective
observational study in which 8 European countries participate. Patients with idiopathic
PD as defined by the criteria of the United Kingdom Parkinsons Disease Society Brain Bank
(UK-PDS-BB) and a score on the Mini Mental State Examination (MMSE) of ?24 are included.
The UPDRS is used to quantify ADL function, motor symptoms, and complications of therapy.
Depression is diagnosed according to the DSM IV criteria. The depression rating scales
used are the  Hospital Anxiety and Depression rating Scale (HADS), the Hamilton
Depression Rating Scale (HAMD-17), and the Beck Depression Inventory (BDI-1A). The
Frontal Assessment Battery (FAB) is used to assess frontal function. The target patient
number in this study will be 1000 patients with an inclusion period of 6 months.
\n\n\nStudy Hypothesis:\n\nComparison(s):\n', 'Observational', '', '', '', '', '', '',
'', '', '', '', 'Phase 4', 'Inclusion_Criteria:\r\n\r\nObservation criteria:\r\nare able
to provide written informed consent in accordance with Good Clinical Practice (GCP) and
local legislation\r\nhave idiopathic Parkinsons disease according to the United Kingdom
Parkinsons Disease Society Brain Bank Diagnostic Criteria for Parkinsons disease\r\nshow
no impairment of cognitive function (MMSE score ?24)\r\nare with or without symptoms of
depression (full range)\r\nare stable on anti-Parkinson/anti-depressive treatment for at
least 1 month before entering the study\r\nare or are not on concomitant antidepressant
Tx \r\nare in the on state during the observation period\r\ndid not previously undergo PD
surgery\r\n', 'Both', '18', 'Years', 'N/A', 'N/A', 'No', 'None', '1000', '2006-04',
'2007-04', '', '', 'Recruiting', '2006-06', 'None', 'Boehringer Ingelheim', '', 'Study
Coordinator', '', '', '', '', 'clintriage@rdg.boehringer-ingelheim.com', '', '', '', '',
'', '', '', '', 'None', 'None', '05-213', 'Medisch Ethische Commissie Academisch
Ziekenhuis Maastricht', 'Academisch Ziekenhuis Maastricht, The Netherlands', 'De
Die-Smulders,MD, PhD', 'PO box 5800,Maastricht,6202 AZ,Netherlands', '+31 (0)43 3872040',
'', 'MECsecretariaat@ctcm.azu.nl', 'None')

Testcase for the bug

Attachment: bug20888.java (application/octet-stream, text), 2.45 KiB.

Thank you for the report.

Verified as described both on 3.1.13 and 5.0.0.-beta.

Attached file contains shortest testcase.

Corrected test case

Attachment: bug20888_3.java (application/octet-stream, text), 1.88 KiB.

As previous test was incorrect, I rewrited it.

I tested on Windows XP SP2 with mysql-connector-java-3.1.13 and JDK 1.5.0 and on Linux
(Linux 2.6.16-1.2108_FC4smp #1 SMP Fri May 5 00:20:42 EDT 2006 i686 i686 i386 GNU/Linux)
with mysql-connector-java-5.0.0-beta and gij (GNU libgcj) version 4.0.2 20051125 (Red Hat
4.0.2-8), java version "1.4.2"

In this case behaviour is different: there is not any exception, but allways string
contained backslash inserted in prepared statement test:
"testPreparedStatement(bug20888_3)junit.framework.AssertionFailedError: Prepared
statement test has failed with expected result: 'D'artanian ps' and actual: 'D\'artanian
ps'"

So I awhile stay "Analyzing" status

Could you please provide code in Java you use to INSERT the row? I.e. stmt.execute('INSERT
...') or what you really use.

Corrected test case

Attachment: bug20888_test.java (application/octet-stream, text), 1.52 KiB.

Tested using Solaris 10 and Windows XP SP2, JDK 1.5, Connector/J 3.1.13.

Error is repeatable only on MySQL 4.0.x

Also tested with JDK 1.4.2 and Connector/J 5.0.0 on Linux with same result: it is possible
to catch this bug only using MySQL 4.0.x (I use 4.0.27-max). Bug is repeatable not only
for TEXT columns, but also for VARCHAR.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9314