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 13:09	Modified:	26 Jul 2006 18:17
Reporter:	John Gillen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	v3.1.13	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

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