Backslash (\) and Quote (') in search/insert query jdbc

Question

I am using JDBC with cratedb(which almost uses PSQL protocols). It happens that when I try to execute a query like this

String query = "select * from test where col1='dhruv\'";

It gives me Parser exception.

Caused by: io.crate.shade.org.postgresql.util.PSQLException: Unterminated string literal started at position 39 in SQL select * from test where col1='dhruv\'. Expected  char
    at io.crate.shade.org.postgresql.core.Parser.checkParsePosition(Parser.java:1310)
    at io.crate.shade.org.postgresql.core.Parser.parseSql(Parser.java:1217)

Then on debugging I found that this code in Parser.java of jdbc

 else if (c == '\'') {
                    i0 = i;
                    i = parseSingleQuotes(p_sql, i, stdStrings);
                    checkParsePosition(i, len, i0, p_sql, "Unterminated string literal started at position {0} in SQL {1}. Expected ' char");
                    newsql.append(p_sql, i0, i - i0 + 1);
                }

is causing issues. Ad per my understanding they break the values in char arrays and '\'' is conflicting with my \ in the end of the statement.

What I know or tried

• I read that people ask to use prepared statement since same issue is happening with inserts, but my queries are dynamic so I cannot use that

• We cannot say that its is not possible to insert values like dhruv\ in database. We can insert directly by console or json files(btw its difficult by java since we need to escape single \ and for db \ is not escape character)

• Cratedb in latest version has String Literals with C-Style Escape, but just to use this feature I cannot update my whole data base

So is there a way around for it?

++Update

Also found queries like

select * from test where col1='dh\''ruv'

will also not work due to same reason.

++More Update

• So as per my understanding select * from workkards where w_number='dhruv\\', at run time sees it as select * from workkards where w_number='dhruv\'
  • In 'dhruv\' , now backslash is escaping quote , so quote is escaped
  • So crate jdbc parser says unterminated string since ' is escaped

Way around on which I am working

• I am replacing \ in java code with \ i.e backslash and space, The user cannot see any difference since space is not visible

if(value.contains("\\") ){
            return value.replace("\\", "\\ ");
        }

• Somewhat like above, seems to be working find as I am able to insert value, but there is one problem
• The value is stored in database with trailing space, so there is issue in searching this value
• We can apply the same logic to search query so it will work
• One problem remains is what if user enters value like 'dh\''ruv'

Answer 1

You need to escape the single quote at least. As far as I know "\" backslash doesn't need escaping.

The problem lies with single quotes, crate.io uses them to explicitly denote column value, as mentioned here

SELECT "field" FROM "doc"."test" where field = '''dhruv\' limit 100;

Above will return a result (Example of what I run on my local machine).

So your query will therefore need to look like this (note the proper termination with a single quote):

String query = "select * from test where col1='''dhruv\'";

UPDATE:

Then I'd suggest if you have to do it this way, escape only the "escapedValue" variable and add it

String query = "select * from test where col1=" + "'" + escapedValue + "'";