JDBC problems through j2ee application 1

[mslane]

Does anyone know how to prepare a query statement so that the queries won't stall when certain characters are entered by a user. For instance ' " & % #. These characters all stall my queries. Is there a quick java fix that I can apply, or do I need to write a QueryPrepare class to run my statements through?

Thanks,
Matt

 

[DJSmith]

all you need to do is place a \ before the character

for example

String SQL = "Select * from table where ID = \""+ id +"\"";

will equal

Select * from table where ID = "id"

 

[mslane]

Do I have to write a method that adds a slash to all five of those characters for every sql statement I make? That's what I'm wondering, is there an easier way, or something that has already been written to run my queries through.

Thanks,
Matt

 

[DJSmith]

unfortunatley there isn't an SQL writing or anything like it in Java

 

[llucifer]

The quoting rules might differ from DB vendor to DB vendor. The best way is to use prepared statements:

PreparedStatement ps = connection.prepareStatement("select * from table where ID = ?"
ps.setInteger(1, 1000);
ResultSet rs = ps.executeQuery();

The setXXXX methods of PreparedStatement fill in the "?" gaps in the SQL query.

For additional info see the online Java Tutorial from sun.

 

[Guest_imported]

(Using j2sdk1.4.0) There is a method on string called replaceAll that accepts regexp. Using this you can do the following. I don't know the escape sequences for each character (only the quote), so I may be wrong with this. Please try it, correct it, and post it back here. Thanks!

(Coming from a Perl background, I'm a big fan of one-liners.)

public static String quoteSQL( String unquoted ) {
return ( unquoted.replaceAll("\\'", "\\'\\'&quot.replaceAll("\\&", "\\\\\\&&quot.replaceAll("\\%", "\\\\\\%&quot.replaceAll("\\#", "\\\\\\#&quot );
}