- mac OS 10.13.6 (openjdk version "11.0.6" 2020-01-14 LTS)
- centOS 7 with java 11
Description
We recently upgraded to MariaDB Connector/J (version 2.6.0) from version 1.5.x and found that our insert statement is breaking with "SQLSyntaxErrorException: unknown escape sequence" exception. The same insert works fine if we use mysql client
For example,
CREATE TABLE TEST_SYNTAX_ERROR(
id INTEGER unsigned NOT NULL AUTO_INCREMENT,
str_value MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL,
json_value MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL
PRIMARY KEY ( id )
)
and if try to insert a string for "str_value" with trailing '\' character followed by some json string for "json_value", we see the following exception.
INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('abc\\', '{"data": "test"}')
at org.mariadb.jdbc.internal.util.Utils.resolveEscapes(Utils.java:465)
at org.mariadb.jdbc.internal.util.Utils.nativeSql(Utils.java:560)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
... 3 more
The main problem is with trailing '\' character in the first string and if the insert command got some column which can hold json string. If we remove the trailing '\' characters in the first string, the query passes.
Please find here attached the test case with the above issue. Our project is blocked by issue.
The fix for the bug is this (or something like it) in Utils.nativeSql():
if (lastChar == '\\' && !protocol.noBackslashEscapes()) {
sqlBuffer.append(car);
lastChar = (car == '\\') ? '.' : car;
continue;
}
Explanation: if we hit an escaped backslash character, we do not want it to still act like an escaping character for the next character visited. So that's why the code above sets 'lastChar' to '.' after we see two backslash characters in a row (the '.' character could be something different, so long as it's not something that has special meaning to the rest of the function).
Note: the nativeSql() call adds a noticeable slowdown in the driver performance when processing large queries (as compared to previous versions of the driver). All it takes is a single '{' anywhere in the query to force the expensive character-by-character analysis and copy of the query. When inserting variable numbers of rows we have found the best performance is achieved by constructing our own escaped queries. Occasionally our queries are >1MB in size, which is slow to process in nativeSql(). These queries are noticeably slower to execute because of the client-side / driver overhead when we upgraded to the latest version.
It would be great for performance-sensitive folks to have an option that disables the functionality provided by nativeSql(), allowing that expensive function to be bypassed. Please let me know if this suggestion should be filed as a separate ticket.
T Tock
added a comment - - edited The fix for the bug is this (or something like it) in Utils.nativeSql():
if (lastChar == '\\' && !protocol.noBackslashEscapes()) {
sqlBuffer.append(car);
lastChar = (car == '\\') ? '.' : car;
continue;
}
Explanation: if we hit an escaped backslash character, we do not want it to still act like an escaping character for the next character visited. So that's why the code above sets 'lastChar' to '.' after we see two backslash characters in a row (the '.' character could be something different, so long as it's not something that has special meaning to the rest of the function).
Note: the nativeSql() call adds a noticeable slowdown in the driver performance when processing large queries (as compared to previous versions of the driver). All it takes is a single '{' anywhere in the query to force the expensive character-by-character analysis and copy of the query. When inserting variable numbers of rows we have found the best performance is achieved by constructing our own escaped queries. Occasionally our queries are >1MB in size, which is slow to process in nativeSql(). These queries are noticeably slower to execute because of the client-side / driver overhead when we upgraded to the latest version.
It would be great for performance-sensitive folks to have an option that disables the functionality provided by nativeSql(), allowing that expensive function to be bypassed. Please let me know if this suggestion should be filed as a separate ticket.
Thank you for the fix, as well as for providing a way to avoid escaping all together. Where can I see the changes that you've made? I looked here: https://github.com/mariadb-corporation/mariadb-connector-j/
but is that the right repo to be looking in?
Thanks!
T Tock
added a comment - Thank you for the fix, as well as for providing a way to avoid escaping all together. Where can I see the changes that you've made? I looked here:
https://github.com/mariadb-corporation/mariadb-connector-j/
but is that the right repo to be looking in?
Thanks!
People
Diego Dupin
Cabir Zounaidou
Votes:
0Vote for this issue
Watchers:
2Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1191.0999999046326,"ttfb":327.39999985694885,"pageVisibility":"visible","entityId":86061,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"433e4044-64dd-43f3-b84a-51beab375fff","navigationType":0,"readyForUser":1337.8999998569489,"redirectCount":0,"resourceLoadedEnd":916.5,"resourceLoadedStart":335.89999985694885,"resourceTiming":[{"duration":24.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":335.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":335.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":360.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":25.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":336.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":336.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":361.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":259.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":336.5,"connectEnd":365.39999985694885,"connectStart":365.39999985694885,"domainLookupEnd":365.39999985694885,"domainLookupStart":365.39999985694885,"fetchStart":336.5,"redirectEnd":0,"redirectStart":0,"requestStart":367.2000000476837,"responseEnd":596.2999999523163,"responseStart":386.7999999523163,"secureConnectionStart":365.39999985694885},{"duration":497,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":337.2000000476837,"connectEnd":337.2000000476837,"connectStart":337.2000000476837,"domainLookupEnd":337.2000000476837,"domainLookupStart":337.2000000476837,"fetchStart":337.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":367.39999985694885,"responseEnd":834.2000000476837,"responseStart":388.59999990463257,"secureConnectionStart":337.2000000476837},{"duration":51.700000047683716,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":337.5,"connectEnd":337.5,"connectStart":337.5,"domainLookupEnd":337.5,"domainLookupStart":337.5,"fetchStart":337.5,"redirectEnd":0,"redirectStart":0,"requestStart":367.5,"responseEnd":389.2000000476837,"responseStart":382.7000000476837,"secureConnectionStart":337.5},{"duration":59.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":337.7000000476837,"connectEnd":337.7000000476837,"connectStart":337.7000000476837,"domainLookupEnd":337.7000000476837,"domainLookupStart":337.7000000476837,"fetchStart":337.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":368.7000000476837,"responseEnd":397.2000000476837,"responseStart":389.39999985694885,"secureConnectionStart":337.7000000476837},{"duration":63,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":337.7999999523163,"connectEnd":337.7999999523163,"connectStart":337.7999999523163,"domainLookupEnd":337.7999999523163,"domainLookupStart":337.7999999523163,"fetchStart":337.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":369.2000000476837,"responseEnd":400.7999999523163,"responseStart":389.89999985694885,"secureConnectionStart":337.7999999523163},{"duration":30.399999856948853,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":338,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":338,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":368.39999985694885,"responseStart":0,"secureConnectionStart":0},{"duration":95.39999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":338.2000000476837,"connectEnd":338.2000000476837,"connectStart":338.2000000476837,"domainLookupEnd":338.2000000476837,"domainLookupStart":338.2000000476837,"fetchStart":338.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":376.39999985694885,"responseEnd":433.59999990463257,"responseStart":431.7000000476837,"secureConnectionStart":338.2000000476837},{"duration":36.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":338.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":338.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":375.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":66.79999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":338.5,"connectEnd":338.5,"connectStart":338.5,"domainLookupEnd":338.5,"domainLookupStart":338.5,"fetchStart":338.5,"redirectEnd":0,"redirectStart":0,"requestStart":382,"responseEnd":405.2999999523163,"responseStart":402.7999999523163,"secureConnectionStart":338.5},{"duration":570.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":345.2000000476837,"connectEnd":345.2000000476837,"connectStart":345.2000000476837,"domainLookupEnd":345.2000000476837,"domainLookupStart":345.2000000476837,"fetchStart":345.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":433.09999990463257,"responseEnd":915.7000000476837,"responseStart":904.7999999523163,"secureConnectionStart":345.2000000476837},{"duration":571.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":345.2999999523163,"connectEnd":345.2999999523163,"connectStart":345.2999999523163,"domainLookupEnd":345.2999999523163,"domainLookupStart":345.2999999523163,"fetchStart":345.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":462.7000000476837,"responseEnd":916.5,"responseStart":908.2999999523163,"secureConnectionStart":345.2999999523163},{"duration":286.89999985694885,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":735.5,"connectEnd":735.5,"connectStart":735.5,"domainLookupEnd":735.5,"domainLookupStart":735.5,"fetchStart":735.5,"redirectEnd":0,"redirectStart":0,"requestStart":980,"responseEnd":1022.3999998569489,"responseStart":1021.7000000476837,"secureConnectionStart":735.5}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":159,"responseStart":327,"responseEnd":344,"domLoading":331,"domInteractive":1446,"domContentLoadedEventStart":1446,"domContentLoadedEventEnd":1510,"domComplete":3076,"loadEventStart":3076,"loadEventEnd":3077,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1411.7000000476837},{"name":"bigPipe.sidebar-id.end","time":1412.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1412.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.end","time":1414.0999999046326},{"name":"activityTabFullyLoaded","time":1528.7999999523163}],"measures":[],"correlationId":"de2348d5aec40","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":97,"dbReadsTimeInMs":11,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
CONJ-800 implement Statement setEscapeProcessing to avoid escape
The fix for the bug is this (or something like it) in Utils.nativeSql():
if (lastChar == '\\' && !protocol.noBackslashEscapes()) {sqlBuffer.append(car);lastChar = (car == '\\') ? '.' : car;continue;}Explanation: if we hit an escaped backslash character, we do not want it to still act like an escaping character for the next character visited. So that's why the code above sets 'lastChar' to '.' after we see two backslash characters in a row (the '.' character could be something different, so long as it's not something that has special meaning to the rest of the function).
Note: the nativeSql() call adds a noticeable slowdown in the driver performance when processing large queries (as compared to previous versions of the driver). All it takes is a single '{' anywhere in the query to force the expensive character-by-character analysis and copy of the query. When inserting variable numbers of rows we have found the best performance is achieved by constructing our own escaped queries. Occasionally our queries are >1MB in size, which is slow to process in nativeSql(). These queries are noticeably slower to execute because of the client-side / driver overhead when we upgraded to the latest version.
It would be great for performance-sensitive folks to have an option that disables the functionality provided by nativeSql(), allowing that expensive function to be bypassed. Please let me know if this suggestion should be filed as a separate ticket.