Using single quotes in a SQL statement

[jason adam]

I'm taking in a parameter from a servlet, as a string, and everything works fine unless the parameter includes a single quotation mark. What are some ways you can work this into your code so that you don't throw a SQL exception error?
Thanks!
Jason

[Fred Abbot]

write a method that places another sigle quote in front of the single quote

[Manjunath Reddy]

Something like...
<pre>
public String clean( String sourceString )
{
StringBuffer target = new StringBuffer();
int length = sourceString.length();
char c;
for( int i=0; i<length; i++ )>
{
c = sourceString.charAt( i );
target.append( c );
if ( c == '\'' )
{
target.append( c ); // add another single quote for escape char
}
}
return target.toString();
}
</pre>

[jason adam]

Ah, so if you need a single quote, you put two back-to-back.
What I did was used the Str.replace( char , String ) method in javaranch.common, worked great.
Thanks you two for the response, solved a very annoying problem
Jason

[jason adam]

Another question about this. Does this rule apply to insert statements also? For some reason, when I grab a String from req.getParameter(), and then use statement.setString() to place the parameter where it needs to go, I can use a single quote without any errors.
Jason

[Jamie Robertson]

you can get around this using PreparedStatement(at least I can using Oracle8 thin drivers).

query = "insert into emp values( ? , ? )";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, 99999);  //id field
ps.setString(2, "O'Connor"); //name field
ps.executeUpdate();
//and
ps = con.prepareStatement("select * from emp where name = ?");
ps.setString(1,"O'Connor");
ResultSet r = ps.executeQuery();

both work, no single quote substitution method required.
Jamie

[jason adam]

Ah! I didn't realize that I was using a PreparedStatement for the insert, and a Statement for the update. Helps to keep uniform.
Thanks Jamie
Jason

[jason adam]

Nice tip Jamie! I switched everything to use PreparedStatement, and I used double-quotes in my SQL statements instead of single-quotes, for example update names set name="somename" instead of update names set name='somename'. That seems to have cleared up everything.
Thanks again y'all!
Jason