Killer apostrophe in SQL query

[heff100]

Hello!-
My J2EE app builds SQL queries using text from form data. While testing,
I noticed that an sql exception occurs whenever the user includes an apostrophe in a form text fields. I'm sure there must be a more elegant solution than removing all apostrophes from user input :-). Can anyone help me with this?

Thanks.

[ractoc]

What you could do is html encode the text before sending it to the database, and html decoding it when extracting it. HTML encoding makes sure that any strange characters(like the patrofe) are coded in the following manner: '
this way, the text itself may become a bit larger though.

Another way to solve this problem is to use bound variables to put the text into the database, but I'm not sure if your database driver supports this feature.
Boud variables work like this:

String text1 = "text to put in col 1";
String text2 = "text to put in col 2";
Connection conn = createDatabaseConnection();
String sql = "insert into table (col1,col2) values (?,?)";
CallableStatement stm = conn.prepareCall(sql);
stm.setString(1, text1);
stm.setString(2, text2);
stm.execute();

But like I said I don't know if you driver or database supports this feature. If it does, you should be able to use bound variables with all your queries, not just the inserts.