Java mail vs. MySQL: probably a character encoding issue?

Question

I'm currently fetching data from a MySQL database using JDBC and executeQuery. One of the fields contains the email content, which I fetch via ResultSet.getString("emailBody").

The mail is sent using the following code (simplified):

Properties props = new Properties();
Session session;
Message message;

props.put("mail.smtp.host", "mysmtpserver");
session = Session.getInstance(props, null);
message = new MimeMessage(session);

message.setFrom(new InternetAddress("myaddress@example.com", "System");
message.setSubject("Automatic notification");

message.setRecipient(RecipientType.BCC,
        new InternetAddress("admin@example.com", "Admin Distribution List"));
// email contains the previously fetched value
message.setContent(email, "text/plain"); 
Transport.send(message);

This works fine for all characters, including german umlaute, brackets, etc. Unfortunately the following characters fail:

– which is displayed as ? on the mail clients
" which becomes \"
' which is sent as \'

I couldn't find anything useful on the web, please advise. Many thanks!

score 0 · Answer 1 · answered Mar 10 '11 at 13:49

0

" turing into \" and ' turining into \' is escaping issue. During insert those values were escaped so it won't break sql insert query. During select you have to unescape them. (don't know specific java functions...)

answered Mar 10 '11 at 13:49

Adrian Serafin

7,665
5
46
67

I thought of the same, but couldn't find anything useful when searching for "jdbc java unescape string"... – MrG Mar 10 '11 at 13:56
you should never see the escaped strings on the other end. if you do, you are doing it wrong. – jtahlborn Mar 10 '11 at 15:16
String a = "\"; is escaped and thinks it is part of a control code. This is because of \t, \n, \u1234 sequences and so on. String a = "\\"; will display a \. – Mar 10 '11 at 19:26
The entry in the database is created by a different system to which I do not have access. I agree, it seems to be a multiple escape issue, which is solved by escaping manually once again on my end. – MrG Mar 13 '11 at 22:23

score 0 · Accepted Answer · answered Mar 10 '11 at 14:49

Your mail is probably send encoded as iso-8859-1, which does not include the codepoint for en-dash. You could try to specify the charset as utf-8 in the setContent call:

message.setContent(email, "text/plain; charset=utf-8");

This does however not explain the problem with quotes you are seeing, but I guess these are actually two different problems.

score 0 · Answer 3 · answered Mar 10 '11 at 17:48

The quotes problem is happening because at some point the strings are being escaped one too many times.

If you select the strings from the database manually, does this return quotes visually escaped? If so, you're escaping too many times before inserting into the database.

Java mail vs. MySQL: probably a character encoding issue?

3 Answers3