File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using single quotes in a SQL statement

 
jason adam
Chicken Farmer ()
Ranch Hand
Posts: 1932
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 300
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
write a method that places another sigle quote in front of the single quote
 
Manjunath Reddy
Ranch Hand
Posts: 60
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1932
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1932
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1932
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1932
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic