Replacing Apostrophes from Strings // Cleaning String to pass them as SQL statements 6


IMPORTANT NOTICE
This post was published in 2007 and some code was used to interact with databases which had no JDBC connector. It is a very bad practice and unrecommended to send SQL statements directly to a database

Such a simple thing as replacing an apostrophe with an escape character from a string sometimes can become a very tedious process, more if you’re using String’s replaceAll() function. Replacing apostrophes from Strings may be very useful when creating Statements to pass to an SQL database, preventing SQL injection. Many may say that using PreparedStatements is easier and safer, but in many occasions you can’t use prepared statements to accomplish what you need.

The problem with the replaceAll function comes because of the arguments the function expects. In first place you must pass a regular expression that suits what you want to replace. Secondly, the function expects another regular expression with the String’s replacement. In postgreSQL when passing apostrophes and other special characters as text, you must use the backslash as an escape character, just like in java. So if you want to insert “John’s hand” as an argument to an Insert statement you must write something such as ‘John\’s hand’.

If you refer to Pattern javadoc you can read:

The backslash character (‘\’) serves to introduce escaped constructs, as defined in the table above, as well as to quote characters that otherwise would be interpreted as unescaped constructs. Thus the expression \\ matches a single backslash and \{ matches a left brace.

Here is the answer to the problem. When replacing ‘ for \’ you need to place 4 backslashes before the apostrophe.

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Pin on PinterestEmail this to someone

Leave a comment

Your email address will not be published. Required fields are marked *

6 thoughts on “Replacing Apostrophes from Strings // Cleaning String to pass them as SQL statements

  • Richard Rodger

    Yeah – it sure would be nice to have literal regexs in Java – would solve all that sort of stuff.

    For the specific case you mention, I think you would be better off using the ‘?’ argument notation provided by JDBC PreparedStatements, rather than rolling your own. The Postgres driver should take care of it for you.

  • Master of Nothing

    I guess it would be a pain in the ass to escape all your String parameters to an SQL statement. Luckily for me I have never come across a instance where I had to pick Statements over PreparedStatements. Actually I haven’t worked at the JDBC API level in the last 3 years , thanks to EJB, Hibernate and iBatis :)

  • Marc Nuri

    It would really be if you had to do it repeatedly. You can avoid it by using a static function to do it every time.

    public static String parseSQL(String in){

    return in.replaceAll(“‘”, “\\\\'”).replaceAll(..whatever other things you need…)…;

    }

    This way, you can call this function any time you need to pass an argument to an SQL statement.

  • Brunis

    as was mentioned on Sun’s Java forums, the two ticks is the ANSI standard, so replacing a ‘ with ” would be easier.. but PS is surely the way to go for oft repeated statements.

  • Tessa

    Although your conclusion reridgang the regex pattern is correct, the function you suggest as a bad example is actually worse than just bad practice. It simply won’t work.if StringUtils.hasLength sText sText= sText.replaceAll ” “, ” AND ” ; sText= sText.replaceAll ” “, ” AND ” ; sText= sText.replaceAll ” “, ” AND ” ; sText= sText.replaceAll ” “, ” AND ” ; sText= sText.replaceAll ” “, ” AND ” ; The first replaceAll used on”Hello Google”will result in”Hello AND AND AND AND AND AND AND AND AND AND AND AND Google”and the subsequent replaceAlls will never be matching anything.