CODEWITHSUNDEEP
javasqljdbc
user947659
user947659

Reputation: 2645

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

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

Upvotes: 31

Views: 89821

Answers (3)

Divya
Divya

Reputation: 1485

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;

Upvotes: 9

ngrashia
ngrashia

Reputation: 9904

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

Upvotes: 67

leoneboaventura
leoneboaventura

Reputation: 435

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

Upvotes: 3

Related Questions