Escape MySQL strings in Java... without prepared statements

user_2526586 0
Viewed 8248

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.

jdbc mysql java
6 Answers

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

user_73450
user_34381370

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

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