My LimeSurvey production version is 3.14.9+180917 but likely applies to others. May have been solved in the repo since, but I can't verify that.
N.B. There are various other threads relating to this issue in the forum but they are all Locked unfortunately hence the new post. I would be grateful if anyone with access could add a ref to this potential solution to help others find it.
Like many others I've struggled / put up with the issue of Unicode characters not being stored properly in survey responses when using MS SQL Server (despite storing languages / translations just fine). I finally had a bit of time to debug this properly so I'm sharing my findings here in the hope that it saves someone else having to spend the same time again.
The root cause is that when UPDATEing survey responses, the LimeSurvey code does not use variable binding (as is used elsewhere and which works perfectly). Instead it generates the full UPDATE statement text dynamically. This can result in statements being executed like:
Code:
UPDATE [surveytable] SET [colname] = 'Zażółć gęślą jaźń' ...
As anyone who knows SQL Server will know, to treat a string literal like this as an nvarchar (unicode string type) you would need to prepend the opening single quote with N e.g.
Code:
UPDATE [surveytable] SET [colname] = N'Zażółć gęślą jaźń' ...
In generating the query text, use is made of the CDbConnection quoteValue() method which in turn calls quoteValue on the PDO driver, in this case pdo_sqlsrv.
If you have pdo_sqlsrv v5.8 or above, you should be able to change the default behaviour of the driver's quoteValue (and quoteValueWithType) methods by specifying the following in config.php :
Code:
...
'components' => array(
'db' => array(
'connectionString' => 'sqlsrv:Server=****;Database=****',
'username' => '****',
'password' => '****',
'charset' => 'utf8',
'tablePrefix' => 'sirius_',
'initSQLs'=>array('SET DATEFORMAT ymd;','SET QUOTED_IDENTIFIER ON;'),
// STUFF BELOW _SHOULD_ MAKE UNICODE SAVEING WORK IF PDO_SQLSRV v 5.8+ IS INSTALLED
'attributes' => array (
PDO::ATTR_DEFAULT_STR_PARAM => PDO::PARAM_STR_NATL
)
)
),
...
If, like me, you're stuck with an older version of the PDO driver in your PHP stack, then you'll need to modify either CMssqlSqlsrvPdoAdapter, extending the quoteValue and quoteValueWithType methods to prepend the magic N, or modify directly in CDbConnection which is the route I took because, frankly, I'd spent enough time on it.
I hope this proves to be more helpful than the usual "you're not using SQL Server are you?!" responses [sucks-through-teeth].
