• Registered: 01/04/2015
  • Posts: 2

Topic: Database character encoding issues

I am using Front Accounting 2.3.22 on Gentoo with MySQL 5.5.40. The default character encoding for my system and MySQL is UTF-8. I have installed the "British COA" 2.3.0-5, which is an iso-8859-1 chart of accounts.

I have entered some setup data via the Simple API module.

I am noticing that names and address with accented characters submitted via the API look correct when viewed from the MySQL client. However, from the Front Accounting application the accented characters appear corrupted.

On the other hand, accented characters entered via the application look corrupted from the MySQL database client, but reappear correctly in the UI.

It looks like there is some sort of encoding issue. All of my MySQL tables are set to UTF-8, which is the system default.

This posting suggests that all database tables should be latin1. If this is the case it strikes me that the charset for the database tables is not being set to this on database creation.

The table creation script en_GB-general.sql which I presume was used to create my database tables does not set the charset for the tables, except for 2 tables where the charset is set to UTF-8, so it is not surprising the created tables ended up with the default.

I have tried dumping my database, setting CHARTSET=latin1 for every CREATE TABLE and reimporting but this has not resolved the problem.

Any suggestions as to what I can try next to resolve the encoding mismatch between the database and Front Accounting?

Thanks.

2 Reply by apmuthu (edited by apmuthu 01/04/2015 05:58:48 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Database character encoding issues

What happens is that the $_POST info gets cleaned using the db_escape() function that does some scrubbing that may not be useful in your context. htmlspecialchars and url_decode and the state of some MySQL / PHP constants that change the way these functions work.

Some other posts in this forum that may be useful here are:

https://frontaccounting.com/punbb/viewtopic.php?id=3119
https://frontaccounting.com/punbb/viewtopic.php?id=5372
https://frontaccounting.com/punbb/viewtopic.php?id=5371
https://frontaccounting.com/punbb/viewtopic.php?id=4530

Study the changes between files in my FAMods with their counterparts in the core to see how you want to implement it.

apmuthu's Website

  • Registered: 01/04/2015
  • Posts: 2

Re: Database character encoding issues

Thank you apmuthu for your reply, but I'm not sure I understand what you are suggesting. The various posts all seem to be struggling with the same issue, but no definitive answer.

In my mind, when reading from the MySQL database the Front Accounting code should be decoding the strings from the MySQL table encoding into whatever the PHP native encoding is. On display, the strings should be decoded from the PHP native encoding to whatever encoding is being used for the HTML, replacing HTML special characters as appropriate. If the data in the database looks OK, but the data on the web UI looks wrong then there must be an issue with this process, no?

Is there an equivalent function to db_escape which does this process in reverse and is used by all database calls?

I'm not sure what you are saying by "see how you want to implement it" -- I'm just trying to use/setup the Front Accounting package.

Thanks

4 Reply by apmuthu (edited by apmuthu 01/05/2015 05:07:13 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Database character encoding issues

Yes, I have been wanting the devs to look at the db_escape() function and was at one time half way thru implementing a db_unescape() function but dorpped it because:
1. I found the multiple language issues and the way PHP functions perceive them differently too varied
2. The multitude of files in FA that use the $_POST / $_GET variables directly when receiving them
3. The way Ajax routines handle them (all such functions bear the same name) on the fly

Will check how the devs manage it in FA v2.4 when UTF-8 will become the standard.

One downside of using UTF-8 is that all PDF sizes will get bloated due to font embedding by default unless "changed" in TCPDF library.

The various posts were listed so that you can monitor them for any future solutions that may accrue there.

As a short term measure for Item Description field, where I needed single quote and double quote to represent feet and inches, I have manually imported them into the tables in SQLyog and expect not to edit them inside the FA UI - a tall order since it will have other data in their records that may need to be updated - there is no atomic editing of fields as available in vTigerCRM. My mod in the previous post was for preventing a change in the Description field by not allowing it to be a form field itself if it is set to be "Not Editable".

apmuthu's Website

  • Registered: 08/07/2015
  • Posts: 1

Re: Database character encoding issues

A character encoding tells the computer how to interpret raw zeroes and ones into real characters. It usually does this by pairing numbers with characters. Words and sentences in text are created from characters and these characters are grouped into a character set. 

More abot Character Encoding: href="http://net-informations.com/q/faq/encoding.html


Mercal