After utilizing encode_json, accented characters are experiencing double encoding

Question

After utilizing encode_json, accented characters are experiencing double encoding

We are currently experiencing an issue with the encode_json function while using use JSON::XS qw(encode_json);. Our web-based project is built on Perl, JavaScript, jQuery, and MySQL as the backend.

In one specific screen within the application, the user's name is displaying inaccurately.

The problem arises due to the fact that the user's name is in Spanish and includes accented characters. The database table where we store this information follows an old schema with a charset of latin1. However, during data retrieval, we convert and encode it into UTF-8 format.

select CONVERT(CAST(CONVERT(u.last_name USING latin1) AS BINARY) USING utf8) AS last_name from user where user_id = 'XXX'

In the storage structure for the database results, everything looks correct.

{ last_name => Cvas García}

This structured data is passed to a JavaScript function responsible for rendering the page.

$data_json = encode_json( $data )

However, when printing $data_json, the accented characters once again appear distorted.

"user_permissions":[{"last_name":"Cvas GarcÃa"}]},

We are seeking assistance in resolving this issue, whether it involves adjustments in MySQL, Perl, or JavaScript code.

javascript mysql ajax perl utf-8

Answer 1

Answer №1

The character set used in the table is not important. MySQL will automatically convert from the table's encoding to the connection's encoding. After that, you will need to decode it on the Perl side, either explicitly or by passing mysql_enable_utf8 => 1 to the connect function. I suggest using the latter option.

use utf8;                             # Source code encoded with UTF-8.
use open ':std', ':encoding(UTF-8)';  # Terminal expects/provides UTF-8.

use DBI      qw( );
use JSON::XS qw( encode_json );

my $user_id = "...";

my $dsn = "...";
my $user = "...";
my $password = "...";

my $dbh = DBI->connect("dbi:mysql:".$dsn, $user, $password, {
   PrintError => 0,
   RaiseError => 1,
   mysql_enable_utf8 => 1,  # Switch to UTF-8 for communication and decoding.
});

my $name = $dbh->selectrow_array("
   SELECT `last_name`
     FROM `user`
    WHERE `user_id` = ?
", undef, $user_id);

# Encoding handled by "use open".
print(sprintf("%vX", $name), "\n");        # 47.61.72.63.ED.61
print($name, "\n");                        # García

# Encoding handled by "use open".
my $json = JSON::XS->new->encode([$name]);
print(sprintf("%vX", $json), "\n");        # 5B.22.47.61.72.63.ED.61.22.5D
print($json, "\n");                        # ["García"]

binmode(STDOUT); # Override "use open".
my $json_utf8 = JSON::XS->new->utf8->encode([$name]);  # aka encode_json([$name])
print(sprintf("%vX", $json_utf8), "\n");   # 5B.22.47.61.72.63.C3.AD.61.22.5D
print($json_utf8, "\n");                   # ["García"]

Answer 2

The character set used in the table is not important. MySQL will automatically convert from the table's encoding to the connection's encoding. After that, you will need to decode it on the Perl side, either explicitly or by passing mysql_enable_utf8 => 1 to the connect function. I suggest using the latter option.

use utf8;                             # Source code encoded with UTF-8.
use open ':std', ':encoding(UTF-8)';  # Terminal expects/provides UTF-8.

use DBI      qw( );
use JSON::XS qw( encode_json );

my $user_id = "...";

my $dsn = "...";
my $user = "...";
my $password = "...";

my $dbh = DBI->connect("dbi:mysql:".$dsn, $user, $password, {
   PrintError => 0,
   RaiseError => 1,
   mysql_enable_utf8 => 1,  # Switch to UTF-8 for communication and decoding.
});

my $name = $dbh->selectrow_array("
   SELECT `last_name`
     FROM `user`
    WHERE `user_id` = ?
", undef, $user_id);

# Encoding handled by "use open".
print(sprintf("%vX", $name), "\n");        # 47.61.72.63.ED.61
print($name, "\n");                        # García

# Encoding handled by "use open".
my $json = JSON::XS->new->encode([$name]);
print(sprintf("%vX", $json), "\n");        # 5B.22.47.61.72.63.ED.61.22.5D
print($json, "\n");                        # ["García"]

binmode(STDOUT); # Override "use open".
my $json_utf8 = JSON::XS->new->utf8->encode([$name]);  # aka encode_json([$name])
print(sprintf("%vX", $json_utf8), "\n");   # 5B.22.47.61.72.63.C3.AD.61.22.5D
print($json_utf8, "\n");                   # ["García"]

Answer 3

Answer №2

Give this a shot

require 'json'
require 'utf8'
require 'data_printer'
utf8.encode(data) unless (utf8.is_utf8(data))
print encode_json(data)

Answer 4

Give this a shot

require 'json'
require 'utf8'
require 'data_printer'
utf8.encode(data) unless (utf8.is_utf8(data))
print encode_json(data)

After utilizing encode_json, accented characters are experiencing double encoding

Answer №1

Answer №2

Similar questions

Variables are losing their way in the vast expanse of self-submitting

Error: The $filter function in AngularJS is not recognized

What is the best way to apply a CSS class to the button that has been clicked when there are multiple buttons using jQuery?

Tips for effectively splitting arrays nested within an array using JavaScript

Start a fresh project using the Teamweek API with jQuery integration

Display the $_SESSION data without refreshing the page when a button is clicked

Creating an automated CRUD API using Node.js, Express, and Mongoose

Is there a way to implement a nested case statement in MySQL?

"Enhancing partial views in Ruby on Rails: A step-by-step guide to

How can we increase the efficiency of calculating the total sum for various fields across multiple rows?

Ways to block or inhibit the ability to traverse back using the browser's back button with react-router-dom v6

The outcome of the Next.js query has not been explicitly provided

Retrieve data from server using Angular and present content on a dedicated webpage

What is the reason behind the appearance of 'Cannot Get/' when trying to render pug?

Newtonsoft date parsing issue causes date to be off by one day

Save the json data to a local storage using jQuery Mobile

"Can we trust the accuracy of the Reverse Bits Solution that Utilizes Left Shift Operations

Attempting to fetch information from MySQL database in Laravel 5 for display

Creating unique validation rules using VeeValidate 3 and vue.js

Having trouble sending data from my useState hook to Mysql database