• Post Reply Bookmark Topic Watch Topic
  • New Topic

Statement.setEscapeProcessing doesn't work!  RSS feed

 
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's up with this method that javax.sql.Statement has? Does setEscapeProcessing(boolean) not escape single quotes from values?

I've also tried PreparedStatements, but they don't help, either. A guy here implied that they worked like a charm for him:
http://www.tek-tips.com/viewthread.cfm?qid=940825&page=1

I'm assuming they didn't work for me because the escaping with those probably happens only when you're utilizing parameters. Anyway, I'm connecting to Oracle 10g with their JDBC thin client (type 4 driver).

Do I just need to roll my own code to parse the complete SQL String and try to escape single quotes within value-marking single quotes?
[ January 12, 2005: Message edited by: Stephen Huey ]
 
author & internet detective
Marshal
Posts: 37518
554
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stephen,
Using a prepared statement only provides benefits when you have parameters. Sticking the original SQL in a PreparedStatement object doesn't accomplish anything. Since a major refactoring to really use PreparedStatements sounds out of the question here, you do need to write some quote to escape those quotes.

How is this different than the question you asked in JDBC?
 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, I admit it's not really different. I possibly edited that question once or twice as I found out more about what was or was not going on, and I think I edited this question 3 times! I originally just asked the first sentence or so about the method, and then I kept coming back here to edit it to provide more details (in other words, after posting that question, I thought I'd found a holy grail with the setEscapeProcessing method, but then I realized it was more of the same and didn't change anything for me). Do I sound like I'm rambling? This is how I feel! I'm just writing a utility method to parse and single-quote escape an entire SQL String since I probably don't have time to go throughout the entire codebase to wrap every little value with an escaping utility method...I've done it with parsing CSV files, and it's about the same...I'll probably contribute my work to Jakarta Commons.
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Stephen Huey:
I'm just writing a utility method to parse and single-quote escape an entire SQL String
You might find this more difficult than you think. Take this example:When you reach the ' inside "Ain't", how do you know it's part of a string and not the end of the string? You could try parsing the actual syntax of the statement, but that's non-trivial.

Anyway, I don't mean to deter you from your task, but just want to give you a heads up that the option you think is too time-consuming (escaping strings in each parameter throughout your code) may in fact be easier.
 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah...actually, because you can have both single quote marks AND the word "and" or "AND" inside of the values, then it makes me think it's actually impossible. Take this case:

select * from mytable where subject='O'Neil's party' and message='Mark Sanderson's handing the project to Jeff Band's group'

Because AND or and or whatever and the apostrophe are both part of the syntax in the WHERE clause, and you can also have both of those mixed up in the value/data (along with spaces), it doesn't appear to be possible to ever be sure what is what (or "where you are") when you're parsing the data.

I started trying to come up with a recursive method, but I'm pretty sure you can't get it to work in 100% of cases:

 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Exactly. In fact, it is impossible in general. How would you parse this statement?Seems trivial, eh? Note that the title of the book I'm searching for is "Contrived' and author = 'Bob". Certainly not likely to pop up in practice, but it does demonstrate the problem.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!