Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Case sensitivity first letter in escape clause [JDBC632] #662

Closed
firebird-automations opened this issue Aug 11, 2020 · 7 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: Anton Shchyrov (anton)

When I try to create an object using escape syntax, provided the keyword is in uppercase, I get the exception

org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C

Code
try (Connection con = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:c:/db/employee.fdb", "SYSDBA", "masterkey")) {
CallableStatement stmt = con.prepareCall("{CALL sp_test(?)}");
stmt.close();
}

Call stack

Exception in thread "main" org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C
at org.firebirdsql.jdbc.escape.FBEscapedParser$ParserState$4.nextState(FBEscapedParser.java:461)
at org.firebirdsql.jdbc.escape.FBEscapedParser.parse(FBEscapedParser.java:114)
at org.firebirdsql.jdbc.FBConnection.nativeSQL(FBConnection.java:330)
at org.firebirdsql.jdbc.FBStatement.nativeSQL(FBStatement.java:904)
at org.firebirdsql.jdbc.AbstractCallableStatement.<init>(AbstractCallableStatement.java:80)
at org.firebirdsql.jdbc.FBCallableStatement.<init>(FBCallableStatement.java:43)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:811)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:777)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:297)
at test.DBTest.main(DBTest.java:10)

The problem is in method nextState of class ESCAPE_ENTER_STATE

ESCAPE_ENTER_STATE {
protected FBEscapedParser.ParserState nextState(char inputChar) throws FBSQLParseException {
switch(inputChar) {
case '?':
case 'c':
case 'd':
case 'e':
case 'f':
case 'l':
case 'o':
case 't':
return NORMAL_STATE;
default:
throw new FBSQLParseException("Unexpected first character inside JDBC escape: " + inputChar);
}
}
}

This method assumes that the first letter of the keyword should be written in lowercase. Although later, when converting the escape command to native code, there is a forced conversion to lowercase

public final class FBEscapedParser {
...............
private void escapeToNative(StringBuilder target, String escaped) throws SQLException {
StringBuilder keyword = new StringBuilder();
StringBuilder payload = new StringBuilder(Math.max(16, escaped.length()));
this.processEscaped(escaped, keyword, payload);
String keywordStr = keyword.toString().toLowerCase();

as a consequence, this code works

CallableStatement stmt = con.prepareCall("{сALL sp_test(?)}");
(first letter in lower case)

Commits: ca6f796 089d5b1 8749301

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

The JDBC specification specifies the escape as {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} and {call <procedure-name>[(<arg1>,<arg2>, ...)]}, so with all lowercase; it doesn't explicitly specify case-sensitivity of escapes, but assuming behaviour similar to SQL keywords, I agree that it should be case-insensitive.

This a regression compared to Jaybird 2.2.x introduced in 3.0.0; I will fix this in Jaybird 4.0.2 and 3.0.10 (and Jaybird 5). Unfortunately, you reported this just while I was releasing 4.0.1, I have no set date for releasing 4.0.2.

As a workaround, I recommend using the syntax as specified by JDBC (that is: lowercase `call`).

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Version: Jaybird 4.0.1 [ 10921 ]

Version: Jaybird 3.0.9 [ 10917 ]

Version: Jaybird 3.0.8 [ 10916 ]

Version: Jaybird 3.0.7 [ 10910 ]

Fix Version: Jaybird 3.0.10 [ 10932 ]

Fix Version: Jaybird 4.0.2 [ 10950 ]

Fix Version: Jaybird 5 [ 10871 ]

description: When I try to create an object using escape syntax, provided the keyword is in uppercase, I get the exception

{quote}
org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C
{quote}

Code

{code:java}
try (Connection con = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:c:/db/employee.fdb", "SYSDBA", "masterkey")) {
CallableStatement stmt = con.prepareCall("{CALL sp_test(?)}");
stmt.close();
}
{code}
Call stack
{code}
Exception in thread "main" org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C
at org.firebirdsql.jdbc.escape.FBEscapedParser$ParserState$4.nextState(FBEscapedParser.java:461)
at org.firebirdsql.jdbc.escape.FBEscapedParser.parse(FBEscapedParser.java:114)
at org.firebirdsql.jdbc.FBConnection.nativeSQL(FBConnection.java:330)
at org.firebirdsql.jdbc.FBStatement.nativeSQL(FBStatement.java:904)
at org.firebirdsql.jdbc.AbstractCallableStatement.<init>(AbstractCallableStatement.java:80)
at org.firebirdsql.jdbc.FBCallableStatement.<init>(FBCallableStatement.java:43)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:811)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:777)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:297)
at test.DBTest.main(DBTest.java:10)
{code}

The problem is in method nextState of class ESCAPE_ENTER_STATE

{code:java}
ESCAPE_ENTER_STATE {
protected FBEscapedParser.ParserState nextState(char inputChar) throws FBSQLParseException {
switch(inputChar) {
case '?':
case 'c':
case 'd':
case 'e':
case 'f':
case 'l':
case 'o':
case 't':
return NORMAL_STATE;
default:
throw new FBSQLParseException("Unexpected first character inside JDBC escape: " + inputChar);
}
}
}{code}

This method assumes that the first letter of the keyword should be written in lowercase. Although later, when converting the escape command to native code, there is a forced conversion to lowercase

{code:java}
public final class FBEscapedParser {
...............
private void escapeToNative(StringBuilder target, String escaped) throws SQLException {
StringBuilder keyword = new StringBuilder();
StringBuilder payload = new StringBuilder(Math.max(16, escaped.length()));
this.processEscaped(escaped, keyword, payload);
String keywordStr = keyword.toString().toLowerCase();
{code}
as a consequence, this code works
{code}
CallableStatement stmt = con.prepareCall("{сALL sp_test(?)}");
{code}(first letter in lower case)

=>

When I try to create an object using escape syntax, provided the keyword is in uppercase, I get the exception

org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C

Code
try (Connection con = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:c:/db/employee.fdb", "SYSDBA", "masterkey")) {
CallableStatement stmt = con.prepareCall("{CALL sp_test(?)}");
stmt.close();
}

Call stack

Exception in thread "main" org.firebirdsql.jdbc.escape.FBSQLParseException: Unexpected first character inside JDBC escape: C
at org.firebirdsql.jdbc.escape.FBEscapedParser$ParserState$4.nextState(FBEscapedParser.java:461)
at org.firebirdsql.jdbc.escape.FBEscapedParser.parse(FBEscapedParser.java:114)
at org.firebirdsql.jdbc.FBConnection.nativeSQL(FBConnection.java:330)
at org.firebirdsql.jdbc.FBStatement.nativeSQL(FBStatement.java:904)
at org.firebirdsql.jdbc.AbstractCallableStatement.<init>(AbstractCallableStatement.java:80)
at org.firebirdsql.jdbc.FBCallableStatement.<init>(FBCallableStatement.java:43)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:811)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:777)
at org.firebirdsql.jdbc.FBConnection.prepareCall(FBConnection.java:297)
at test.DBTest.main(DBTest.java:10)

The problem is in method nextState of class ESCAPE_ENTER_STATE

ESCAPE_ENTER_STATE {
protected FBEscapedParser.ParserState nextState(char inputChar) throws FBSQLParseException {
switch(inputChar) {
case '?':
case 'c':
case 'd':
case 'e':
case 'f':
case 'l':
case 'o':
case 't':
return NORMAL_STATE;
default:
throw new FBSQLParseException("Unexpected first character inside JDBC escape: " + inputChar);
}
}
}

This method assumes that the first letter of the keyword should be written in lowercase. Although later, when converting the escape command to native code, there is a forced conversion to lowercase

public final class FBEscapedParser {
...............
private void escapeToNative(StringBuilder target, String escaped) throws SQLException {
StringBuilder keyword = new StringBuilder();
StringBuilder payload = new StringBuilder(Math.max(16, escaped.length()));
this.processEscaped(escaped, keyword, payload);
String keywordStr = keyword.toString().toLowerCase();

as a consequence, this code works

CallableStatement stmt = con.prepareCall("{сALL sp_test(?)}");
(first letter in lower case)

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

summary: Case sensativity firs letter in escape clause => Case sensitivity first letter in escape clause

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Fix committed

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Commented by: Anton Shchyrov (anton)

Thanks for the quick fix

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment