[Plugin: Contact Form 7 to Database Extension] Problems with Umlaut-Characters (?, ?, ü, é , à

OK, I did some testing in your debug site and I think I came up with a fix.

I just pushed version 2.2.1. Hopefully that update will make everything magically work now.

Works now – thanks so much!
I like things that magically work ??

Dear Michael

I just write to confirm that with the 2.2.1 Version my Umlaut-Problems have disappeared too. Thanks also to dmysticd, who apparently enabled Michael to fix the problem by providing him with a debug install.

best regards
balthasar

…however, existing entries in the db with UMLAUTS in there have been deleted. So there seems to be an issue with converting the existing content of already submitted form entries.

The records are still there, however, the respective fields that do contain an Umlaut Charakter (?,?,ü e.g.) are entirely deleted.

It has to be noted that I used some db script before to regularly change crumbled Umlauts into correct ones. So, the fields deleted already contained correct Umlauts. The code is as follows

update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "ü") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?¤", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?§", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "é") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?¨", "è") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?\x0A", "à") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?±", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?￠", "a") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?–", "?") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "ü") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "??", "?") ;

best
balthasar

@bglaettli

The records are still there, however, the respective fields that do contain an Umlaut Charakter (?,?,ü e.g.) are entirely deleted.

What I would expect, is that noting in the actual DB has changed due to this update, but it is now pulling the data out and converting it to your default character. In the case for dmysticd, it was latin1 (iso-8859-1). A side effect of this is that in some cases, the output characters may not appear on the screen due to incorrect encoding.

This mistake I was making in the code was forcing it to be returned to the PHP page in UTF-8. Which seemed logical since the web page was in UTF-8. But actually it needed to be returned in latin1, then it seems to get converted somewhere to UTF-8. So when that second UTF-8 conversion ran on strings that were already UTF-8, it mangled certain characters.

So you would actually need to convert you old characters back to regular UTF-8 versions in your DB. Sorry for this headache.

If you want to look at the plugin code for to temporarily switch back to the old behavior until you convert, in CFDBQueryResultIterator.php look for

// Target charset is in wp-config.php DB_CHARSET
if (defined('DB_CHARSET') && 'DB_CHARSET' != '') {
    global $wpdb;
    $wpdb->set_charset($con,
                       DB_CHARSET,
                       (defined('DB_COLLATE') && 'DB_COLLATE' != '') ? DB_COLLATE : null);
}

And replace that with

global $wpdb;
$wpdb->set_charset($con, 'utf-8');

Then you should have the old behavior.

You are right, the “missing” fields were still in the database. So I could solve the problem by simply reverting the Umlauts I had converted before.

update wp_cf7dbplugin_submits set field_value = replace(field_value, "ü", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "?¤") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "?§") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "é", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "è", "?¨") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "?±") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "a", "?￠") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "?–") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "ü", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "?", "??") ;
update wp_cf7dbplugin_submits set field_value = replace(field_value, "à", "?\x0A" ) ;

This did the trick – now I am completely happy.

I’m delighted to hear it!