problem with ' (quote) in JSP & MySQL

Looks like I was somewhat unclear: I don't (yet) have the problem to

insert the escaped string into the database, I look for a way to escape

the string itself.

I get the string from a HTML-form in JSP, if I insert it like this in

the MySQL DB it won't work because it's not escaped so I look for a

method which takes the string and escapes all ' in it.

In PHP (scripting language) there is a method called addslashes which

does exactly what I am looking for in JSP/Java.

Does something like this exist as well?

hippie1 at 2007-7-5 >

I recently developed a web application which basically added data entered by the user into database.

The way I approached it is that on the HTML page when the user clicked submit I did validaion of the input fileds to check for '. If there was a single quote i alerted the user to remove it and then proceed.

You could also folow the same logic but implement it to suit your requirements.

If you come across any other different way pls do post it here.

Thanks.

dasnani at 2007-7-5 >

Instead u just check for the occurance of ' and get its position, replace it with '' before inserting it in database.

make the substring till that position

concate ' ' and then concate the remaining line to insert it into database,

see if this works,

if u come across any problem write again,

Bye,

Samir

samirkolarkar at 2007-7-5 >

I believe that with most drivers, if you use a PreparedStatement and its setString() method, it will do the escaping for you.

// this code will fail.

Connection conn;

Statement stmt = conn.createStatement();

String name = "O'Reilly";

String sql = " insert into table ( last_name ) values ( '"+name+"' )";

stmt.execute( sql );

// this code shouldn't fail

Connection conn;

String name = "O'Reilly";

String sql = " insert into table ( last_name ) values ( ? ) ";

PreparedStatement pstmt = conn.prepareStatement( sql );

pstmt.setString( 1, name );

pstmt.execute();

preaney at 2007-7-5 >

Hi,

there is a reallly lazy way of doing it using a character replace function. You might need to double check this, but if you have a String text, then try:

text.replace("/' ",") and it will replace a single quote with a double quote. Notice the single quote is escaped by a backslash or forward slash.

I have pinched a brilliant bit of code from Wrox - Javav server pages 2nd edition (chapter 9 - I think) where the writer creates a JCS(JDBC connector) taglib section. This book is brilliant to say the least, and full of good / excellent working examples. I cannot remember the code at this point in time, but will post it tomorrow (as long as its alright with Wrox?). It is simple and checks for the indexOf a character, uses a pointer to point at this and then uses substring to change it to a doubled up single quote. Simple stuff, but it works.

Nothing original by me, but hope it helps.

best,

kev

kwilding2 at 2007-7-5 >