This bug is starting to become a pain in the XXXX.

https://drupal.org/node/1540686

The only patches I have found are oriented to preparing the data where it is generated and convert it to UTF-8 when necessary.

Can this be done at a database driver level? I tried to figure out where...

The driver should check for string query parameters and convert to UTF-8 if it is not yet:

if (!mb_detect_encoding($log_entry['message'], 'UTF-8', true))
$log_entry['message'] = mb_convert_encoding($log_entry['message'], 'UTF-8', 'ISO-8859-1');

I have come accross this issue in at least 3 more scenarios than those covered in https://drupal.org/node/1540686, but I can only recall 2 two of them...

[1] In drupal_set_message, try doing this in an ajax Call so that the message is shown in next page refresh:

drupal_set_message('Wélcome to the Jungle')

You won't se anything because Drupal fails to persist sesion to database because of the text encoding.

[2] Geolocation module, tracks user location when configured to do so and stores this information in $user->data, the same problem again, some locations have "accents" and are not UTF-8 encoded, leading to error when persisting to databse.

CommentFileSizeAuthor
#4 2168191-sqlsrv-encoding-issue.patch2.69 KBdavid_garcia
#3 sqlsrv-encoding-issue.patch2.51 KBdavid_garcia
#2 database.patch721 bytesdavid_garcia
#1 query.inc_.patch1.07 KBdavid_garcia

Comments

david_garcia’s picture

david_garcia commented
Title: Problema with non UTF-8 in queries » Problem with non UTF-8 in queries
StatusFileSize
new query.inc_.patch1.07 KB

For reference:

- To replicate, on a clean unpatched install: 

<?php

define('DRUPAL_ROOT', getcwd());

require_once DRUPAL_ROOT . '/includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

watchdog('test','hóla esto va a petár porqué tiéne un montón de acentós y no tiene la codificación qué debería');

watchdog('test',utf8_encode('hóla estó no va a pétar porqué está bien codificado, pero hay muchos sitios donde los módulos no gestionan bien la codificación'));

return;

You get:

PDOException: SQLSTATE[IMSSP]: An error occurred translating string for input param 3 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page. en dblog_watchdog() (línea 169 de modules\dblog\dblog.module).

After applying patch, no more errors. Performance of solution is not good, but reliable.

david_garcia’s picture

david_garcia commented
StatusFileSize
new database.patch721 bytes

This also happens with select queries:

db_query('select * from users where uid = :uid', array(':uid' => 'más'));

david_garcia’s picture

david_garcia commented
Status: Active » Needs review
StatusFileSize
new sqlsrv-encoding-issue.patch2.51 KB

Rolled everything into a proper patch, refactored and included the same logic for Update statements.

david_garcia’s picture

david_garcia commented
StatusFileSize
new 2168191-sqlsrv-encoding-issue.patch2.69 KB

Encoding fixing was not working on update/insert statements due to a missing "&" in a loop. Fixed an re-rolled.

david_garcia’s picture

david_garcia commented
Status: Needs review » Closed (won't fix)

I've been working more into this and these are my conclusions:

- It is difficult to ensure that everything that goes through the database driver is something that can be straight converte to UCS-2, that was what I was trying to do all along.

- Even if it could be done, it will only solve the unhandled exceptions of the PDO driver not being able to convert encoding but there is a chance that the code that is producing the bad encoded strings will be messed up because these strings, when retrieve from database again, are coming back in UTF-8 and not original encoding.

- Once I realised that, I thought that maybe I do the conversions and log everything into watchdog for analysis: bad idea. Non utf-8 string are sent all the time through the driver, it is just that a small subset of them that have characters that cannot be straight converted to UCS-2.

Finally....

I recommend that when you come across non UTF-8 data try to find out where is it coming from and solve the issue from the root making sure that all data sent to database is UTF-8.

So.... closed won't fix!