Hey Thom. I think I found a potential fix for this issue. I actually have two solutions, but one requires setting up the Project Nami DB to use a
_UTF8 collation during the install, so probably not an option for your existing system. That also then requires SQL Server 2019 (or newer, whenever that happens). That option is the easier of the two, but as I said, kinda needs to happen from the beginning (technically you can change the collation of the DB and all of the objects in one shot, but if you had any characters with values in the 128 - 255 range, then they would get corrupted).
The second option is to add a single upper-case
N to a single line of the PHP code, in the
prepare() function that all of the SQL statements (I believe) get run through. I have this written up in the following Issue for that project:
Unicode (NVARCHAR) data converted to 8-bit code page (VARCHAR) as it is stored #417
Currently, the line to edit is #1351 of wp-db.php, but that same line of code might be a different line # if you have a different version than what's checked into the master branch. The line in question is currently:
$query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.
and it should probably be:
$query = preg_replace( '/(?<!%)%s/', "N'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.
I have no way to test this, though I have tested the theory with PHP in general and it does fix the problem. As I mention in that GitHub issue, I'm a little concerned about non-string values that still get quoted, such as dates, times, and datetimes, and UNIQUEIDENTIFIERs / GUIDs. Those types of values will work in the current scheme of using single-quotes without an upper-case
N but won't work if the
N is there.
Please note that you need to have your connection using the
'CharacterSet'=> 'UTF-8' option in the array that gets passed into
sqlsrv_connect(). Most likely you already have this set (if you save this character, 👾 , and it comes back as
?? instead of
ðŸ‘¾ then you have it set).