Suitecrm: Difference between the data from the website and the data in the database

[heziwm]

I am using logic hook to check if the user has changed the content of the form in custom module.

Like
if ($bean->name != $bean->fetched_row['name'])

When the variable contains certain characters like "ó", "&", "-" and nothing has been changed in the form, the value from the base and the value from the page are not the same.

I found a partial solution.

But every day users find a new symbol that site doesn't work. How to do it properly? Utf-8 is set up everywhere I could.

MariaDB [suitecrm_db]> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB [suitecrm_db]> SHOW FULL COLUMNS FROM pm6_opis_faktur;
+------------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | char(36) | utf8_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| date_entered | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| date_modified | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| modified_user_id | char(36) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| created_by | char(36) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| description | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| deleted | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
| assigned_user_id | char(36) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |

SuiteCRM v7.11.15

[johnM2401]

Hey,

There seem to be inconsistent Encoding/Decoding behaviours when fetching/submitting data, so I've marked this as a Bug

However, as a workaround, you could try using the HTML Decode functions, which should _hopefully_ provide more general coverage of special characters.

Perhaps something like this?:

I am saving an Account with the name "ó & -"

Using the above two Decode functions in tandem seems to give the same result for both $finalConvert and $bean->fetched_row['name']

Meaning that the condition:
_if ($finalConvert != $bean->fetched_row['name']){_

Correctly returns "False"

I'm not sure if this will provide 100% coverage, especially if we differ with DB/CRM Encoding formats
However, please feel free to give it a try.
I hope that it proves useful!

[heziwm]

Thank you for your help.

if (htmlspecialchars_decode(html_entity_decode($bean->name)) != htmlspecialchars_decode(html_entity_decode($bean->fetched_row['name'],ENT_QUOTES))){

AND

if (htmlspecialchars_decode(html_entity_decode($users_list->name)) != htmlspecialchars_decode(html_entity_decode($bean->Textbox_Who_Txt)) ){

In my case, this code works - it was tested by users for several days.

[chris001]

@heziwm @johnM2401
Probably this issue needs a pull request to fix in the core CRM code...

[johnM2401]

Hey @chris001
Agreed, this is something that is worth resolving
Unfortunately, the changes above are just a workaround for @heziwm's issue, and not a permanent solution

However, I will reopen this, as I agree it is worth further investigation

[pgorod]

We shouldn't do HTML clean-ups when the data is entering the system and going into the database.

We should do SQL clean-ups before going into the database, and HTML clean-ups just before outputting to a browser.

Since we're doing this wrong, for everything that goes into SuiteCRM, then we need to multiply undoing unnecessary clean-ups and it's a mess. Sometimes it's not possible to get data back.

[chris001]

1. What's the original Sugar 6.5 CE specification for data storage in each table/field of the DB? ie, which table fields contain numbers, which contain text, long text, which contain valid HTML, which contain unprocessed mail headers or mail bodies or entire mail messages header plus body, which contain user generated input, filtered user generated input, etc.
2. Are there tests that cover all the code and say in Travis builds whether it works according to the spec?
3. Should the spec be upgraded to reflect current standards, ie UTF8MB4 for MySQL DBs so that all UTF8 characters store correctly without getting destroyed? Yes!
4. Again, are there tests that cover all the upgraded code and say in Travis builds whether it works according to the spec?