Java 如何为 SQL 插入转义单引号...当要插入的字符串在用户生成的变量中时

How to escape single quotes for SQL insert...when string to insert is in a user generated variable

提问by user947659

I am building an insert command to execute using jdbc. Part of it is to concatenate a user generated string...this all works until the user uses a string like this:

我正在构建一个插入命令以使用 jdbc 执行。它的一部分是连接用户生成的字符串......这一切都有效，直到用户使用这样的字符串：

a'bcd

A B C D

String userString="a'bcd";
String insertTableSQL = "INSERT INTO myTable "
                            + "(insertColumn) " 
                            + "VALUES("
                                +"'"+userString+"'"
                                +")";

statement.executeUpdate(insertTableSQL);

采纳答案by Nishanthi Grashia

You can do either of the below:

您可以执行以下任一操作：

1. Use the PreparedStatementclass. (Recommended)

   String userString="a'bcd";
   String myStatement = " INSERT INTO MYTABLE (INSERTCOLUMN) VALUES (?)";
   PreparedStatement statement= con.prepareStatement   (myStatement );
   statement.setString(1,userString);
   statement.executeUpdate();
   

2. Escape the single quotes.

   In SQL, single quotes will be escaped by using double single quotes. '--> ''

   String userString="a'bcd";
   String changedUserString = userString.replace("'","''");
           //changedUserString  = a''bcd
   String insertTableSQL = "INSERT INTO myTable (insertColumn) VALUES("
                           +" '"+changedUserString +"' )";
   

1. 使用PreparedStatement类。（推荐）

   String userString="a'bcd";
   String myStatement = " INSERT INTO MYTABLE (INSERTCOLUMN) VALUES (?)";
   PreparedStatement statement= con.prepareStatement   (myStatement );
   statement.setString(1,userString);
   statement.executeUpdate();
   

2. 转义单引号。

   在 SQL 中，单引号将使用双单引号进行转义。'-->''

   String userString="a'bcd";
   String changedUserString = userString.replace("'","''");
           //changedUserString  = a''bcd
   String insertTableSQL = "INSERT INTO myTable (insertColumn) VALUES("
                           +" '"+changedUserString +"' )";
   

回答by Divya

You can use StringEscapeUtilsfrom the Apache Commons Langlibrary. Using this you can escape characters from html, xml, sql, etc. Look for method escapeXXXfor your purpose. For reference: When i need to escape Html string?

您可以使用StringEscapeUtils从阿帕奇共享郎库。使用它，您可以从 html、xml、sql等中转义字符。寻找escapeXXX适合您目的的方法。供参考：当我需要转义 Html 字符串时？

note: escapeSqlwas removed in Apache Commons Lang 3 (see Migrating StringEscapeUtils.escapeSql from commons.langwhich references https://commons.apache.org/proper/commons-lang/article3_0.html#StringEscapeUtils.escapeSql)

注意：escapeSql已在 Apache Commons Lang 3 中删除（请参阅从 commons.lang 迁移 StringEscapeUtils.escapeSql ，其中引用了https://commons.apache.org/proper/commons-lang/article3_0.html#StringEscapeUtils.escapeSql）

Eg:

例如：

String str = FileUtils.readFileToString(new File("input.txt"));
        String results = StringEscapeUtils.escapeHtml(str);
        System.out.println(results);

Input:

输入：

<sometext>
Here is some "Text" that I'd like to be "escaped" for HTML
& here is some Swedish: Tack. Vars?god.
</sometext>

Output:

输出：

<sometext>
Here is some "Text" that I'd like to be "escaped" for HTML
& here is some Swedish: Tack. Varsågod.
</sometext>

回答by leoneboaventura

Here's another option:

这是另一种选择：

Use a native Android method designed for exactly this purpose:

使用专为此目的而设计的原生 Android 方法：

DatabaseUtils.sqlEscapeString(String)

Here is the documentation for it online:

这是它的在线文档：

• sqlEscapeString() on Android Developers

• Android 开发人员上的 sqlEscapeString()

The main advantage of using this method, in my opinion, is the self-documentation because of the clear method name.

在我看来，使用这种方法的主要优点是由于方法名称清晰，因此具有自文档性。

---

---

String userString="a'bcd";
String insertTableSQL = "INSERT INTO myTable "
                            + "(insertColumn) " 
                            + "VALUES("
                                +"'"+DatabaseUtils.sqlEscapeString(userString)+"'"
                                +")";

statement.executeUpdate(insertTableSQL);