CODEWITHSUNDEEP
javamysqljdbc
user2526586
user2526586

Reputation: 1200

Escape MySQL strings in Java... without prepared statements

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.

Upvotes: 6

Views: 11271

Answers (6)

mindas
mindas

Reputation: 26713

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

Upvotes: 2

Sinri Edogawa
Sinri Edogawa

Reputation: 331

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

Upvotes: 2

Uchephilz
Uchephilz

Reputation: 548

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

Upvotes: 1

user3438137
user3438137

Reputation: 19

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

Upvotes: -1

user2526586
user2526586

Reputation: 1200

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

}

Upvotes: 8

MaVRoSCy
MaVRoSCy

Reputation: 17839

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

Upvotes: 1

Related Questions