29

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:

a'bcd

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

statement.executeUpdate(insertTableSQL);
ngrashia
  • 9,869
  • 5
  • 43
  • 58
user947659
  • 2,485
  • 4
  • 21
  • 24

3 Answers3

64

You can do either of the below:

  1. Use the PreparedStatement class. (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 +"' )";
Community
  • 1
  • 1
ngrashia
  • 9,869
  • 5
  • 43
  • 58
8

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

note: escapeSql was removed in Apache Commons Lang 3 (see Migrating StringEscapeUtils.escapeSql from commons.lang which references 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:

&lt;sometext&gt;
Here is some &quot;Text&quot; that I'd like to be &quot;escaped&quot; for HTML
&amp; here is some Swedish: Tack. Vars&aring;god.
&lt;/sometext&gt;
vinnyjames
  • 2,040
  • 18
  • 26
Divya
  • 1,469
  • 1
  • 13
  • 25
  • 3
    In StringEscapeUtils documentationfor for escapeSQL: "At present, this method only turns single-quotes into doubled single-quotes" – potapuff Nov 13 '15 at 15:34
  • 5
    StringEscapeUtils.escapeSql is [depreciated](https://commons.apache.org/proper/commons-lang/article3_0.html). – chancyWu Jan 12 '17 at 05:42
  • 1
    This approach only works if you intend to use the input as a HTML output later on. About deprecation, v3.6 of commons-lang is still there and works fine with org.apache.commons.text.[StringEscapeUtils](https://commons.apache.org/proper/commons-text/javadocs/api-release/org/apache/commons/text/StringEscapeUtils.html). – Alfabravo Jun 23 '17 at 18:09
2

Here's another option:

Use a native Android method designed for exactly this purpose:

DatabaseUtils.sqlEscapeString(String)

Here is the documentation for it online:

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);
leoneboaventura
  • 415
  • 1
  • 3
  • 13