How do you automatically escape characters in a block of mysql text

I am using a Java app to read thru text files and output them to a SQL script and eventually a Mysql database. One of the columns is made-up of several lines of unformated text, including symbols like commas and apostrophes. To make matters more difficult I have purposely add new lines in order to keep the text readable. Is there a way to have MySQL ignore character combinations that will cause problems (mainly apostrophes, but I have know way of knowing what these blocks of text will contain) without breaking the newlines?

Answers


Read through this: http://dev.mysql.com/doc/refman/5.1/en/string-literals.html

In a nutshell, these are the characters to replace

\0  An ASCII NUL (0x00) 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.

Here is what I have been using for a while in some Java programs where I can't/don't use Parameter Binding.

public static String addSlashesSearchMode(String s) {
    return addSlashes(s, true);
}

public static String addSlashes(String s) {
    return addSlashes(s, false);
}

private static String addSlashes(String s, boolean search) {
    if (s == null) {
        return s;
    }
    String[][] chars;
    if(!search) {
        chars = new String[][ ]{
                {"\\",  "\\\\"},
                {"\0", "\\0"},
                {"'", "\\'"}, 
                {"\"",  "\\\""},
                {"\b",  "\\b"},
                {"\n",  "\\n"},
                {"\r",  "\\r"},
                {"\t",  "\\t"},
                {"\\Z", "\\\\Z"}, // not sure about this one
                {"%", "\\%"},     // used in searching
                {"_", "\\_"}
        };
    } else {
        chars = new String[][ ]{
                {"\\",  "\\\\"},
                {"\0", "\\0"},
                {"'", "\\'"}, 
                {"\"",  "\\\""},
                {"\b",  "\\b"},
                {"\n",  "\\n"},
                {"\r",  "\\r"},
                {"\t",  "\\t"},
                {"\\Z", "\\\\Z"}, // not sure about this one
        };
    }
    for (String[] c : chars) {
        s = s.replace(c[0], c[1]);
    }
    return s;
}

Usage: StringUtils.addSlashes("ke\rn\tny's\"\nnew li\\ne%"))

output: ke\rn\tny\'s\"\nnew li\\ne\%

output viewed in phpmyadmin:

ke
n    ny's"
new li\ne\%

Note: that I'm not really sure about the \Z implementation, perhaps someone can elaborate more on that.


You would have to originally added the escape slash: \ So like \' means that its an actual single quote. When putting things in, you could use PHP to add_slash() the string you are putting into the database, automatically escaping them for you.


I know you want to output an SQL script, but in general I think it's far better to access the database directly and have the driver escape the text for you.

SQL: update myTable set value1=? where value2=?

Then in code create a PreparedStatement and call setParameter(1,value1) etc.

The driver will take care of escaping the apostrophes and other special characters.


Need Your Help

iOS7 NSKeyedArchiver warning: replacing existing value for key 'UITintColor'; probable duplication of encoding keys in class hierarchy

ios uitableview

I have created a uitableviewcell using a xib. And I have a cell factory where the cell is unarchived this way:

How do I sort my UITableViewCells based on the dates in a property list?

objective-c ios xcode

(Prepare to witness a newbie being very, very confused at what I'd assume is a basic form of logic that my brain is struggling to grasp.)