Escape MySQL strings in Java... without prepared statements

Question

I am looking for a way to escape strings for MySQL queries with Java.

But WAIT, before some of you jump straight away to the conclusion of "prepared statements", I wish to explain a little more.

The problem is, I don't have a MySQL connection. I am not executing any MySQL queries directly from Java. I don't know much about prepared statement, but AFAIK, prepared statements need a live connection to work.

I want to my code to read in a bunch of CSV/XLS files, get the data and then generate a huge INSERT query for MySQL. BUT this query will be stored in a text file for now. It won't execute just yet until someone gives the green light and dump this text file into the database.

Is there an easy way to do so without employing an overkill framework/library? Thank you all.

Answer 1

It seems like there isn't any existing lib/framework out there that does such thing. So I guess a simple String manipulator will do?

class xxx {

    private String escapeStringForMySQL(String s) {
        return s.replaceAll("\\", "\\\\")
                .replaceAll("\b","\\b")
                .replaceAll("\n","\\n")
                .replaceAll("\r", "\\r")
                .replaceAll("\t", "\\t")
                .replaceAll("\\x1A", "\\Z")
                .replaceAll("\\x00", "\\0")
                .replaceAll("'", "\\'")
                .replaceAll("\"", "\\\"");
    }

    private String escapeWildcardsForMySQL(String s) {
        return escapeStringForMySQL(s)
                .replaceAll("%", "\\%")
                .replaceAll("_","\\_");
    }

}

Answer 2

You can use the designated Special Character Escape Sequences for MySQL

Escape Sequence Character Represented by Sequence
\0  An ASCII NUL (X'00') character
\'  A single quote (') character
\"  A double quote (") character
\b  A backspace character
\n  A newline (linefeed) character
\r  A carriage return character
\t  A tab character
\Z  ASCII 26 (Control+Z); see note following the table
\\  A backslash (\) character
\%  A % character; see note following the table
\_  A _ character; see note following the table