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

Question

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 1

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