Re^2: How to Encode/Decode double encoded string.

Hi Haj,
Please disregard my previous reply, as i messed up with that
Thank you for the reply. I have basically below two strings in the postgres database

select title from TABLE;
                  title
-------------------------------------------
 this is a test international ï¿½ï¿½ï¿½ï¿½  #This string got inserte
d in the database using oldest version of version i don't know the ex
act version but it seems it this version doesn't handle utf8 by defau
lt, this was inserted using Legacy version of the application. <br> 

 this is a test international Ã Ã¡Ã¢Ã¤    #This string got inserte
d in the database using perl version 5.24.1
[download]

Thank you

Comment on Re^2: How to Encode/Decode double encoded string. Download Code

Replies are listed 'Best First'.
Re^3: How to Encode/Decode double encoded string. by haj (Vicar) on Sep 22, 2020 at 08:59 UTC
Thanks for the clarifications! It is relevant information that the stuff comes from a Postgres database. There's a lot of encoding done behind the scenes if a database is part of the game. Postgres has a configurable server encoding and a configurable client encoding, either one or both might have changed between the legacy and current application. The string `ï¿½` is an UTF-8-encoded version of the "Unicode replacement character". You get this by software which tries to decode strings as UTF-8 which contain non-UTF-8 characters, and then encodes this result as UTF-8. I guess that the decoding step gets fed with plain ISO-latin `àáâä`. There is a chance that the bogus decoding happens in Perl's Postgres database driver. You can check that by setting the DBH option `pg_enable_utf8` to zero when connecting. Your application will then be able to examine the "raw" contents, and decode accordingly. A convenient way to examine strings is `printf` with the "v" format specifier: `printf "%vx",$string` From there you can decide how to proceed. Probably you need to re-build the data with a consistent encoding.	[reply] [d/l]
Re^4: How to Encode/Decode double encoded string. by Anonymous Monk on Sep 22, 2020 at 11:08 UTC
Hi Haj, Thank you for the reply. Yes i did set pg_enable_utf8 = 0, after that only i can see the same above raw strings on the web applications. Please note whatever i see in the database, i see as it is in the application too. postgres server encoding and client encoding is 'UTF-8', i tried to change the client_encoding to SQL_ASCII but it didn't help. I am still not quite sure what exactly needs to be done in order to get around this issue. Thank you	[reply]
Re^5: How to Encode/Decode double encoded string. by haj (Vicar) on Sep 22, 2020 at 11:45 UTC
If your database actually contains `ï¿½ï¿½ï¿½ï¿½`, then it will no longer be able to provide the invalid UTF-8 string which was converted to this sequence of replacement characters. So either the legacy version of the application didn't store correct data in the first place, or something went wrong when converting the server encoding to UTF-8. In the latter case, you might be able to grab an old database backup and restart from there. We don't have access to your database nor to your application, so it is up to you to find out which part of software did the bogus UTF-8-decoding (bogus because it failed to check for errors). In Perl, you can (and should) trap this type of error by catching errors like this: `Use Encode; eval { decode('UTF-8',$string,Encode::FB_CROAK) }; if ($@) { # Well, that $string wasn't a valid UTF-8-string. # Go now and die in what way seems best to you. -- Denethor }` [download]	[reply] [d/l]