This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
When I use INSERT and UPDATE queries to a database and one of the fields contain the ' char in it (for example: ab'c) the query fails. I can run replaceAll on the String before I do the INSERT and UPDATE but I don't want to lose this character. Any ideas...?
Are you creating the SQL statement by concatenating parts and parameters together, like this?
You should not do that. Not only do you get problems like this with special characters, you might also make your code vulnerable to an SQL injection attack.
To avoid this, you should always use PreparedStatements. By doing this, the JDBC driver will take care of handling special characters in the parameters. It is also more efficient if you execute the same statement more than once, because the database then only has to parse the statement once (and execute it multiple times, but just with different data).
You probably get into a habit of using prepared statements from a security point of view. The ' problem you experienced the first time is not just a pain, it's a security risk because it's not *only* the ' character you need to escape. It varies on what needs escaping depending on the DBMS and the character encoding used. You risk SQL injection attacks if you simply escape '.
The prepared statement method works by using an underlying transport API provided by the DMBS so you let the DBMS deal with the raw security exploits.