Java special character escaping problem with string splicing SQL statement

in the actual project development, often according to the user in the Interface text box input information, go to the database to do fuzzy query. If you are using raw JDBC and SQL, you often need to escape the user's input, avoid SQL syntax errors generated, or prevent SQL injection. For example, the input of% and _ and ', you need to escape, because these 3 characters are special characters of SQL, if not processing will cause SQL error or query data is incorrect.

If there are 1 query requests, the fuzzy query header contains a%b_cc ' d records, the correct SQL should be the following:

SELECT * from T_sch_work_info t where  t.title like  '%a/%b/_cc ' d% ' ESCAPE '/';

This requires that string content = "A%b_cc ' d" be escaped and then spelled into an SQL statement. The Oracle database allows us to use our own defined characters as escape characters. We looked at the code style of DAO layer of our project and used 2 kinds of escape characters \ and/. When locating the problem, it was found that there was an error in stitching the SQL statement. Using these 2 kinds of characters, I wrote 2 common methods, which were tested correctly.

/** * * After the content is converted, the value of the conditional field in the Oracle query. Use/As an escape character for Oracle, it's more appropriate. <br> * can achieve results, and Java code is relatively easy to understand, it is recommended this way of use <br> * "% '" + content + "'% ESCAPE '/'" This stitching SQL seems easy to understand <br&gt     ; * * @param content * @return */public static string Decodespecialcharswhenlikeusebackslash (string content        {//single quotation marks are the bounds of the Oracle string, Oralce 2 single quotes for 1 single quotes string afterdecode = Content.replaceall ("'", "" "); It is necessary to escape the character by using/as Escape special character,//The function here is to turn "a/a" into "a//a" Afterdecode = Afterdecode.replaceall ("/", "//")        );        Use the escape character/, to escape the Oracle special character%, only as a normal query character, not a fuzzy match Afterdecode = Afterdecode.replaceall ("%", "/%");        Use the escape character/, to escape the Oracle special character _, only as a normal query character, not a fuzzy match Afterdecode = Afterdecode.replaceall ("_", "/_");    return afterdecode; }/** * After the content is converted, the value of the condition field as an Oracle query. Use \ As an escape character for Oracle. <br> * This approach also achieves the goal, but is not a good practice, more error-prone, and the code is very understanding. <br> * "% '" + content + "'% ESCAPE ' \ '" This stitching SQL is actually wrong.<br> * "% '" + content + "'% ESCAPE ' \ \ '" This stitching SQL is the correct <br> * * @param content * @return */public static string Decodespecialcharswhenlikeuseslash (string content) {//single quote is the boundary of the Oracle string, with 2 orders in Oralce        The quotation marks represent 1 single quotes String Afterdecode = Content.replaceall ("'", "" "); Because of the use of \ as escape special character, it is necessary to escape the character//because \ in Java and regular expressions are special characters, need special treatment//Here is the role of "a\a" into "a\\a" after        Decode = Afterdecode.replaceall ("\\\\", "\\\\\\\\");        Use the escape character \, to escape the Oracle special character%, only as a normal query character, not a fuzzy match Afterdecode = Afterdecode.replaceall ("%", "\\\\%");        Using the escape character \, the Oracle special character _ is escaped, only as a normal query character, not a fuzzy match Afterdecode = Afterdecode.replaceall ("_", "\\\\_");    return afterdecode;  }

by comparing the code above, it is easy to see that the code is simpler and easier to understand. The reason why I write this blog is not to recommend you to manually splicing SQL strings, because this approach is inefficient and error prone. In real-world development, we should use a framework such as JDBC or hibernate to provide pre-compiled SQL. Using precompiled statements not only makes your code more readable, but it also has performance benefits. You can refer to this article:

Benefits of HQL or SQL use: Reduce SQL parsing time, reduce memory overhead, and prevent SQL injection

Java special character escaping problem with string splicing SQL statement