Complete Fix for utf8 languages [Archive]

aria_cop

04-06-2008, 04:04 PM

Hi ,

For complete fix , change the collate & character sets of folowing tables from latin_general_ci to utf8_bin in mysql.

This fix works on all email piping and pop3 fetch and open tickets and wants no change in many files.

add below texts to includes/class.misc.php sendmail function after if($xheaders) $headers .= $xheaders; :

$headers .="Content-Type: text/plain; charset=utf-8 \n";
$headers .= "Content-Transfer-Encoding: 8bit\r\n";

I alter tables and it works.

save below text as a .php file e.g(test.php) and upload to your host , then run it. (http://www.your-domain.com/test.php)

<?php
#your database settings , same as osticket databse settings.
mysql_connect(server_name, username, password);
mysql_select_db(database_name);

# Don't forget to change ost_ in below text to the table prefix you select in ost installation

$qt = array("ALTER TABLE `ost_email_template` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_email_template` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_subj` `ticket_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_body` `ticket_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_subj` `ticket_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_body` `ticket_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_subj` `message_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_body` `message_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_subj` `message_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_body` `message_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_subj` `assigned_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_body` `assigned_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_subj` `ticket_overdue_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_body` `ticket_overdue_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_subj` `ticket_overlimit_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_body` `ticket_overlimit_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_subj` `ticket_reply_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_body` `ticket_reply_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_help_topic` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_help_topic` CHANGE `topic` `topic` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_message` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_ticket_message` CHANGE `message` `message` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `subject` `subject` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '[no subject]'","ALTER TABLE `ost_ticket` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_response` CHANGE `staff_name` `staff_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `response` `response` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_groups` CHANGE `group_name` `group_name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_access` `dept_access` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_department` CHANGE `dept_name` `dept_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_signature` `dept_signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_config` CHANGE `helpdesk_title` `helpdesk_title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'osTicket Support Ticket System'","ALTER TABLE `ost_email_banlist` CHANGE `submitter` `submitter` VARCHAR( 126 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `answer` `answer` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_staff` CHANGE `username` `username` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `firstname` `firstname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `lastname` `lastname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `phone` `phone` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `phone_ext` `phone_ext` VARCHAR( 6 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `mobile` `mobile` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `signature` `signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `phone` `phone` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL","ALTER TABLE `ost_ticket_message` CHANGE `headers` `headers` TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ","ALTER TABLE `ost_ticket_note` CHANGE `source` `source` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `title` `title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Generic Intermal Notes',
CHANGE `note` `note` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_ticket_priority` CHANGE `priority` `priority` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `priority_desc` `priority_desc` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_timezone` CHANGE `timezone` `timezone` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ");

$i=0;
while ($qt[$i]) {
$rep = mysql_query($qt[$i]);
if ($rep) {
echo "" . $qt[$i] . " ";
} else {
echo "" . $qt[$i] . " ";
}
$i++;
}
?>

Cool :cool:

leonardoc

04-11-2008, 12:59 PM

Not work. Help me?

gnuscript

04-18-2008, 02:11 PM

Same as above.

aria_cop

04-27-2008, 03:44 PM

hi dear friends,

This file works perfect

plz, provide me the errors you seen.
or any information about it.

SebM

05-17-2008, 08:03 AM

Adding the two Headers above results in broken Emails.

The Email breaks immediatly before the first "special char".

matya

03-09-2009, 06:35 AM

Hello!

We decided to use OS Ticket, but got the same problem, so here is the fix to convert all incoming email to UTF8, depending on the Content-Type header's encoding.

It will work, as long iconv will recognize the the input format. The funny part is, that the variable is defined in the original code, but not used in this manner...

So, here is out solution for 1.6 RC 4:

File: osticket_1.6.rc4/upload/include/pear/Mail/RFC822.php

***************
*** 555,569 ****
return true;
}

+ /*
+ * The code breaks here, if non-standard char is found.
+ * Not nice hack, but commenting out this piece will work...
+ *
// Check for any char from ASCII 0 - ASCII 127
if (!preg_match('/^[\\x00-\\x7E]+$/i', $atom, $matches)) {
return false;
}
+ **/
// Check for specials:
if (preg_match('/[][()<>@,;\\:". ]/', $atom)) {
--- 555,564 ----

File osticket_1.6.rc4/upload/include/pear/Mail/mimeDecode.php:

***************
*** 299,309 ****
--- 299,311 ----
case 'text/plain':
$encoding = isset($content_transfer_encoding) ? $content_transfer_encoding['value'] : '7bit';
$this->_include_bodies ? $return->body = ($this->_decode_bodies ? $this->_decodeBody($body, $encoding) : $body) : null;
+ $return->body = iconv($content_type["other"]["charset"], "UTF-8", $return->body);
break;

case 'text/html':
$encoding = isset($content_transfer_encoding) ? $content_transfer_encoding['value'] : '7bit';
$this->_include_bodies ? $return->body = ($this->_decode_bodies ? $this->_decodeBody($body, $encoding) : $body) : null;
+ $return->body = iconv($content_type["other"]["charset"], "UTF-8", $return->body);
break;

case 'multipart/parallel':
***************
*** 567,572 ****
--- 569,575 ----
break;
}

+ $text = iconv($charset, "UTF-8", $text);
$input = str_replace($encoded, $text, $input);
}

Update: Forgot to post the fix for the headers.

So:

$text = iconv($charset, "UTF-8", $text);

Comments, suggestions and feedback is welcome in PM's.

Regards
Janos Mattyasovszky
BUTE / SCH KSZK

roed

06-11-2009, 07:34 AM

Hi Janos and all.
I have tried your solution for UTF8 and it works half way... The subject gets right but the body only shows ??? instead of the right characters.
I have doubled checked every line of code that should be changed but everything is just as you described.

Any ideas?

Thanx,
Michael

hanweb

02-13-2011, 03:51 PM

Hi ,

For complete fix , change the collate & character sets of folowing tables from latin_general_ci to utf8_bin in mysql.

This fix works on all email piping and pop3 fetch and open tickets and wants no change in many files.

add below texts to includes/class.misc.php sendmail function after if($xheaders) $headers .= $xheaders; :

$headers .="Content-Type: text/plain; charset=utf-8 \n";
$headers .= "Content-Transfer-Encoding: 8bit\r\n";

I alter tables and it works.

save below text as a .php file e.g(test.php) and upload to your host , then run it. (http://www.your-domain.com/test.php)

<?php
#your database settings , same as osticket databse settings.
mysql_connect(server_name, username, password);
mysql_select_db(database_name);

# Don't forget to change ost_ in below text to the table prefix you select in ost installation

$qt = array("ALTER TABLE `ost_email_template` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_email_template` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_subj` `ticket_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_body` `ticket_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_subj` `ticket_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_body` `ticket_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_subj` `message_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_body` `message_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_subj` `message_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_body` `message_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_subj` `assigned_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_body` `assigned_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_subj` `ticket_overdue_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_body` `ticket_overdue_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_subj` `ticket_overlimit_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_body` `ticket_overlimit_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_subj` `ticket_reply_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_body` `ticket_reply_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_help_topic` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_help_topic` CHANGE `topic` `topic` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_message` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_ticket_message` CHANGE `message` `message` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `subject` `subject` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '[no subject]'","ALTER TABLE `ost_ticket` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_response` CHANGE `staff_name` `staff_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `response` `response` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_groups` CHANGE `group_name` `group_name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_access` `dept_access` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_department` CHANGE `dept_name` `dept_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_signature` `dept_signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_config` CHANGE `helpdesk_title` `helpdesk_title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'osTicket Support Ticket System'","ALTER TABLE `ost_email_banlist` CHANGE `submitter` `submitter` VARCHAR( 126 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `answer` `answer` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_staff` CHANGE `username` `username` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `firstname` `firstname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `lastname` `lastname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `phone` `phone` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `phone_ext` `phone_ext` VARCHAR( 6 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `mobile` `mobile` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `signature` `signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `phone` `phone` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL","ALTER TABLE `ost_ticket_message` CHANGE `headers` `headers` TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ","ALTER TABLE `ost_ticket_note` CHANGE `source` `source` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `title` `title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Generic Intermal Notes',
CHANGE `note` `note` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_ticket_priority` CHANGE `priority` `priority` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `priority_desc` `priority_desc` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_timezone` CHANGE `timezone` `timezone` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ");

$i=0;
while ($qt[$i]) {
$rep = mysql_query($qt[$i]);
if ($rep) {
echo "" . $qt[$i] . " ";
} else {
echo "" . $qt[$i] . " ";
}
$i++;
}
?>

Cool :cool:

in file "include/class.email.php",line:220,change
$headers = array ('From' =>$from,
'To' => $to,
'Subject' => $subject,
'Message-ID' =>'<'.Misc::randCode(10).''.time().'@osTicket>',
'X-Mailer' =>'osTicket v 1.6',
'Content-Type' => 'text/html; charset="UTF-8"'
);
to

$headers = array ('From' =>$from,
'To' => $to,
'Subject' => $subject,
'Message-ID' =>'<'.Misc::randCode(10).''.time().'@osTicket>',
'X-Mailer' =>'osTicket v 1.6',
'Content-Type' => 'text/html; charset="UTF-8"',
'Content-Transfer-Encoding' => '8bit'
);

in file "include/mysql.php" function “db_query” add follow message:
mysql_query("SET NAMES 'UTF8'");