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:


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


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