yii\db\Exception: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x94\xBA",...' for column 'cache'

[giorgiopogliani]

Describe the bug

The error is probably due to some emojis in the labels fields of Salesforce and so saving the fields in the cache column fail. The content of the column is something like this:

...
{\"class\":\"verbb\\\\formie\\\\models\\\\IntegrationField\",\"handle\":\"Example_Label__c\",\"name\":\"Example Label 🔺\"
...

Steps to reproduce

1. This error happens during the setup of the Salesforce CRM integration. The integration gets configured successfully
2. In any form settings, refreshing the integration fails with 500 error. The logs have the errors in the title.

Form settings

Basic form settings with a salesforce integration under integrations.

Craft CMS version

3.7.43

Plugin version

1.6.14

Multi-site?

No response

Additional context

No response

[engram-design]

Fixed for the next release. To get this early, change your verbb/formie requirement in composer.json to:

"require": {
  "verbb/formie": "dev-craft-3 as 1.6.14",
  "...": "..."
}

Then run composer update.

[giorgiopogliani]

Hello @engram-design, I did what you said but I still have the same error PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x94\xBA",...' for column 'cache' ....

I updated the composer.json as you said and run composer update and tried the integration again, but nothing changed apparently.

[engram-design]

Have you refreshed the integration after the update?

[giorgiopogliani]

Yes, that's the moment where it gives error. I also tried to disconnect and reconnect but didn't help

[engram-design]

Is it that exact same error? Support should allow for any label of a custom field to contain an emoji now. Your original example showed Example Label 🔺 which I'll assume is a custom field in your Salesforce account. This does seem to work for me.

[giorgiopogliani]

Yeah same error, although there are other emojis scattered in the json and in different locations, that was an example. Do you require every use case? I see also emojis in the options of some fields, could this help?

[engram-design]

I'll require every use-case, yes - because we need to add specific handling for it. Option labels are a good point though, which I've just pushed an additional fix for (run composer update again).

[engram-design]

Fixed in 1.6.15

[giorgiopogliani]

@engram-design I tried to update but the issue is still present. Did you also "escape" labels for options of options? This is another example:

{\"class\":\"verbb\\\\formie\\\\models\\\\IntegrationField\",\"handle\":\"Test__c\",\"type\":\"string\",\"required\":false,\"options\":{\"label\":\"Test\",\"options\":[{\"label\":\"🟢\",\"value\":\"Good\"},{\"label\":\"🟡\",\"value\":\"Okay\"},{\"label\":\"🔴\",\"value\":\"Bad\"}]}

[engram-design]

Yeah, everything is escaped now, the entire string that's saved to the database is checked for emoji's and replaced. I did also check option labels just to be sure, but I can't seem to replicate that (I have a picklist field in Salesforce, which I believe is the same as your Test__c field)

[giorgiopogliani]

@engram-design I checked the failed query again and I found this part:

{\"label\":\"🟢\",\"value\":\"Good\"},{\"label\":\"🟡\",\"value\":\"Okay\"},{\"label\":\":red_circle:\",\"value\":\"Bad\"}

It looks like the red circle got escaped but not the others.

[engram-design]

Looks like this is actually down to the litemoji library (that both Craft and Formie use) not supporting these emoji's altogether. I've filed an issue there.

[engram-design]

FYI, this will be fixed in Craft 4.4 https://github.com/craftcms/cms/discussions/12226

[maxstrebel]

I am on Craft 4.4 and I used an emoji in an error message. Which also resulted in a DB error. I will open a dedicated ticket, soon. Just wanted to mention :)

[engram-design]

If you can let me know which emoji's you're using as well? The issue was that some emoji support was missing.

[maxstrebel]

Oh, I'm sorry for that. I'm in a MAC and used the smiley emoji from the emoji menu that you can toggle with the function key. This one to be precise 🙂

Does that help?

[engram-design]

That should certainly be supported! Which field are you setting this on, and for which integration?

Ah, my apologies - you mentioned in the error message. Messages aren't supported yet.

[maxstrebel]

I thought so. To not steal your time, I'll post a proper Ticket for you and link it to this one.