搜索/插入查询jdbc中的反斜杠(\)和引号(') [英] Backslash (\) and Quote (') in search/insert query jdbc

问题描述

我正在使用带有cratedb的JDBC(几乎使用PSQL协议).碰巧当我尝试执行这样的查询

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)

然后在调试时，我在jdbc的Parser.java中发现了这段代码

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);
                }

正在引起问题.根据我的理解，它们会破坏char数组中的值，并且'\''与语句末尾的\冲突.

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.

• 我读到人们要求使用prepared statement，因为插入内容也发生了同样的问题，但是我的查询是动态的，所以我不能使用它.

• 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

我们不能说不可能在数据库中插入像dhruv\这样的值.我们可以直接通过控制台或json文件插入(由于Java需要转义单个\，而db \不是转义字符，这在Java中是困难的)

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最新版本具有

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?

还找到了类似的查询

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

由于同样的原因也将无法正常工作.

will also not work due to same reason.

• 根据我的理解select * from workkards where w_number='dhruv\\'，在运行时将其视为select * from workkards where w_number='dhruv\'
  • 在'dhruv\'中，现在反斜杠转义了引号，因此引号被转义了
  • 所以板条箱jdbc解析器会说出未终止的字符串，因为'被转义了
  • 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
    • 我将Java代码中的\替换为\，即反斜杠和空格，由于空格不可见，用户看不到任何区别
    • 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("\\", "\\ ");
            }
    

    • 类似于上面的内容，似乎可以正常工作，因为我能够插入值，但是存在一个问题
    • 该值存储在数据库中，带有尾随空格，因此搜索该值时会出现问题
    • 我们可以将相同的逻辑应用于搜索查询，这样它将起作用
    • 一个问题仍然是，如果用户输入'dh\''ruv'
    之类的值，该怎么办?
    • 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'

    推荐答案

    您至少需要转义单引号.据我所知，"\"反斜杠不需要转义.

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

    问题在于单引号，crate.io使用单引号明确表示列值，如

    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\'";
    

    更新:

    然后我建议您是否必须这样做，仅转义"escapedValue"变量并添加它

    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 + "'";
    

    这篇关于搜索/插入查询jdbc中的反斜杠(\)和引号(')的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！