This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes Using single quotes in a SQL statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using single quotes in a SQL statement" Watch "Using single quotes in a SQL statement" New topic
Author

Using single quotes in a SQL statement

jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
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
Ranch Hand

Joined: Jun 01, 2000
Posts: 300
write a method that places another sigle quote in front of the single quote
Manjunath Reddy
Ranch Hand

Joined: Jul 26, 2001
Posts: 60
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
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
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
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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

both work, no single quote substitution method required.
Jamie
jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
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
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
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
 
GeeCON Prague 2014
 
subject: Using single quotes in a SQL statement