[jira] [Commented] (DERBY-5862) There is no way to escape the special _ and % characters in DatabaseMetaData search strings

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13419194#comment-13419194 ] 

Rick Hillegas commented on DERBY-5862:
--------------------------------------

If we add a knob for configuring a search string escape character, then we could rewrite the metadata queries in metadata.properties to take advantage of this character. Everywhere where we currently say "LIKE ?" we could say instead "LIKE ? ESCAPE ?".

For backward compatibility, we would still need to handle the default case where there is no search string escape character. Unfortunately we can't hand an empty string to the ESCAPE clause. A query like the following raises an error because the SQL Standard specifies that the ESCAPE argument must be a 1 character string (see part 2 of the Standard, section 8.5 <like predicate>, general rule 3.b.i.1):

    select tablename from sys.systables where tablename like ? escape '';

Note that the metadata queries run in a special "internal" mode. We could probably get away with allowing an empty string for the ESCAPE argument when we are running in that special "internal" mode.

A candidate solution for this issue would then be:

1) Add a knob so that applications can configure a search string escape character.

2) Add some special logic so that when we are running in "internal" mode, you can pass an empty string to the ESCAPE clause. The meaning of this special internal syntax would be the same as if you omitted the ESCAPE clause.

3) Change all of the "LIKE ?" clauses in metadata.properties to be "LIKE ? ESCAPE ?".

4) Change all of the code which invokes the metadata queries so that we stuff the second ? with the result of calling DatabaseMetaData.getSearchStringEscape().

This solution seems straightforward but tedious.

A variation of this approach (to eliminate steps 3 and 4) would be to modify "internal" mode (or create a special "metadata internal" mode). In this mode, the LIKE operator would always add an ESCAPE clause when DatabaseMetaData.getSearchStringEscape() evaluates to something other than the empty string.

In any event, we would want to write a battery of test cases to verify the escaping behavior of all of the DatabaseMetaData calls which take search strings as arguments.

Addressing this issue appears to me to be at least a 2 week mini-project.

                
> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5862
>                 URL: https://issues.apache.org/jira/browse/DERBY-5862
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.10.0.0
>            Reporter: Rick Hillegas
>
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a way to configure a search string escape character. See the discussion on this email thread: http://old.nabble.com/derby-getProcedureColumns-td34176144.html#a34176144

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira