One of my programs work with MySQL (Connector/J Driver). The issue is, some of the contents I'm passing into the query string are read from a file that contains several escapable characters. When I try to commit the query (such as an INSERT) with a value that has unescaped characters I get a syntax error from MySQL.

Is there a well built java function to escape characters in a string? You would think something like String.excape("text") would exist, but nope :p


Any suggestions? I could do a String.replaceAll(.....) on some excapable characters, but I don't know all of them & since its a fairly common problem, I'm sure there's a better way (preferably something built into java or mysql connector/j). I couldn't find anything in my searching.

I heard people at my [previous] internship talking about this, it is called an SQL injection. So look up how to prevent mySQL injections on google. I found how to prevent them in php fairly quickly. But I'm assuming your problem was a lack of the right search terms, so I hope that helps.

The two main things to escape in the MySQL world are the single quote (common across virtually all DBs) and the backslash (specific to MySQL). You can do this yourself, assuming you've also validated that you're working with the right data types to start with (in other words, don't fix a string when you are supposed to be working with a number, date, etc.).

However, most people would suggest going the parameterized query route. Try looking at Prepared Statements. http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Edited 6 Years Ago by apegram: n/a

Agreed. But you never mentioned whether or not you knew it was called an injection, so I was only pointing you towards search terms, not doing research.

edit: It looks like prepared statements are the way to go on this, as seen here and here. I'm not saying that I'd know either way, but there are a heck of a lot of programmers out there saying it is worth it, so you may want to take a stab at it and see if the results satisfy you.

Edited 6 Years Ago by BestJewSinceJC: n/a

Actually, that term "injection" was helpful. It lead me to PreparedStatements thanks.

and thanks apegram too :)

Yeah, no problem. . I was reading about PreparedStatement as well, lol, I updated my post before I noticed that you'd seen it. Be careful though, I read one link where it said if you use PreparedStatement incorrectly, it has the same pitfall as if you'd never used it. Personally I'd go with the example from the stackoverflow link I sent you.

Edited 6 Years Ago by BestJewSinceJC: n/a

This article has been dead for over six months. Start a new discussion instead.