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.

This sounds like a strange setup. If your code does not have direct access to the database, why should it create the SQL statements? Shouldn't it be enough to update the XML and have the code accessing the database doing the SQL stuff? — Timothy Truckle, Jul 24 '17 at 09:12
@TimothyTruckle Well, I am not the one who dumps the query into the database. The person who is going to run the query, may or may not know Java and she may or may not know much about coding either. She might just simply copy the query and run the query in some GUI SQL program. Or... simply put, my task here is to rip all the data from a huge amount of CSVs and make the data into a huge INSERT query, Java is just my chosen tool for automation. You got any better ideas? — user2526586, Jul 24 '17 at 09:18
Possible duplicate of [Escaping SQL Strings in Java](https://stackoverflow.com/questions/10522257/escaping-sql-strings-in-java) — OH GOD SPIDERS, Jul 24 '17 at 09:21
This means you problem is an undefined/broken workflow which you have to bypass with a solution that is broken by design. I'd try to sell my customer an additional program connected to the database where the end user can drag and drop the XML file to that does the SQL stuff. — Timothy Truckle, Jul 24 '17 at 09:27
@OHGODSPIDERS The thing is... with MySQL, there are two styles of escaping special characters - the double character style and the slash style. May be I'd just write a string manipulating method and add slashes accordingly? — user2526586, Jul 24 '17 at 09:33
@TimothyTruckle Hmmm.... yes and no... I won't entirely agree so. May be you are assuming things too perfectly... and may be you have a MS SQL background instead of a MySQL one. First of all, I am not doing this for my client. I am doing this for internal use. Secondly, this operation is going to be done a few times in a year, except there are loads of files each time. Thirdly, I am not the one who manage the database. I am just doing so because my boss said I should help with any automation. Lastly, I think inserting XMLs directly to MySQL directly can be a mess. Why not just insert SQLs? — user2526586, Jul 24 '17 at 09:41

score 7 · Accepted Answer · answered Jul 25 '17 at 05:55

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("_","\\_");
    }

}

replaceAll("\\", "\\\\") throws java.util.regex.PatternSyntaxException: Unexpected internal error near index 1 since replaceAll interprets the argument as regex hence more escaping is needed. This is the correct way: replaceAll("\\\\", "\\\\\\\\") Or else, just use replace("\\", "\\\\"). See discussion here: https://stackoverflow.com/questions/1701839/string-replaceall-single-backslashes-with-double-backslashes — Sylvester, Mar 05 '21 at 23:08

score 1 · Answer 2 · answered Jul 24 '17 at 13:48

1

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

answered Jul 24 '17 at 13:48

MaVRoSCy

17,747
15
82
125

Thanks a lot. It seems like there is not any existing lib/framework out there that does such thing. I guess I have to write my own method to replace these characters then. – user2526586 Jul 25 '17 at 05:30
Late to the party, but as mindas said in a recent answer, [OWASP's ESAPI](https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.md) does it. Here's the [GitHub repo](https://github.com/ESAPI/esapi-java-legacy) of the legacy version, which is actually maintained, and also available via [Maven Repo](https://mvnrepository.com/artifact/org.owasp.esapi/esapi). – Benito Jan 23 '20 at 17:28

score 1 · Answer 3 · answered Dec 10 '19 at 14:29

1

You could be using OWASP's ESAPI which was designed for this very purpose. It has MySQL codec.

answered Dec 10 '19 at 14:29

mindas

26,463
15
97
154

score 0 · Answer 4 · answered Dec 16 '19 at 11:22

I used org.apache.commons.lang3.StringEscapeUtils.escapeHtml4(input);

You can check the documentation here String Escape Util (Commons Lang 3.1 API)

public static final String escapeHtml4(String input) Escapes the characters in a String using HTML entities.

For example:

"bread" & "butter"

becomes: "bread" & "butter".

Supports all known HTML 4.0 entities, including funky accents. Note that the commonly used apostrophe escape character (') is not a legal entity and so is not supported).

Parameters: input - the String to escape, may be null Returns: a new escaped String, null if null string input Since: 3.0

score 0 · Answer 5 · answered Dec 17 '21 at 02:32

package io.github.sinri.Keel.test.mysql;

public class MySQLTest {
    public static void main(String[] args) {
        StringBuilder sb=new StringBuilder();
        sb.append('\0').append('\'').append('"').append('\b').append('\n').append('\t').append('\r')
                .append(Character.toChars(26)).append('\\').append('_').append('%');

        String raw=sb.toString();
        System.out.println("raw: "+raw);
        System.out.println("escapeStringForMySQL: "+escapeStringForMySQL(raw));
        System.out.println("escapeWildcardsForMySQL: "+escapeWildcardsForMySQL(raw));

    }

    private static String escapeStringForMySQL(String s) {
        return s.replace("\\", "\\\\")
                .replace("\b","\\b")
                .replace("\n","\\n")
                .replace("\r", "\\r")
                .replace("\t", "\\t")
                .replace(new String(Character.toChars(26)), "\\Z")
                .replace(new String(Character.toChars(0)), "\\0")
                .replace("'", "\\'")
                .replace("\"", "\\\"");
    }

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

score -1 · Answer 6 · answered Nov 16 '17 at 14:14

-1

Try StringEscapeUtils.escapeSql() This will do what you are expecting.

answered Nov 16 '17 at 14:14

user3438137

19
3

2

This method appears to have been removed in lang 3.7 – Todd Kamin Aug 07 '18 at 20:45
1

This method does nothing but replace single quotes with double quotes, would not advise – DGK Oct 25 '19 at 12:49

Escape MySQL strings in Java... without prepared statements

6 Answers6