user947659 user947659 - 3 years ago 83
Java Question

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

Answer Source

You can do either of the below:

  1. Use prepared Statement. (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 +"' )";
    
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download