Unicode (NVARCHAR) data converted to 8-bit code page (VARCHAR) as it is stored

[srutzky]

I'm using a site that uses Project Nami and I'm unable to save any Unicode characters in my posts. They all get converted to ?, or to a best-fit mapping. Supplementary characters such as "𒍅" (U+12345) get saved as ??. All of this behavior points to a conversion to VARCHAR somewhere along the way. The data model appears to be using NVARCHAR to store the data (which is correct), so that can't be the source of the problem. And with a character like "〛" (U+301B) getting converted to ] (i.e. a best-fit mapping), I don't think PHP would do that, even if running on Windows (though I haven't tested that specifically so I guess I can't rule it out entirely). I can HTML-encode those characters, but if the post is ever edited, the decoded character is displayed in the editor, so if I save without re-encoding all of the previously encoded characters, then they all get converted.

So, now I'm looking for code that results in something along the lines of:

INSERT INTO table (column_list) VALUES ('value_from_form', 'value_from_form', ..);

Which should be:

INSERT INTO table (column_list) VALUES (N'value_from_form', N'value_from_form', ..);

The difference is only the N prefix on strings (though dates/times/datetimes/uniqueidentifiers are single-quoted but without the N prefix).

I had found translations.php but was informed that isn't the main code path for DB interaction. However, I didn't see any other place with SQL Server-specific T-SQL. The rest all appeared to be MySQL-specific SQL.

I found a line in the main DB include file that is at least very close (conceptually) to what I'm looking for:

https://github.com/ProjectNami/projectnami/blob/master/wp-includes/wp-db.php#L1351

That line wraps string parameters in single quotes while the SQL is being prepared. But everything else around it still appears to be MySQL-specific, so I wasn't thinking that was truly it.

I did see this open issue: #303
but I'm not sure if that pertains to what I am experiencing.

[patrickebates]

I think it does apply. Early sites using PN did not have access to a clean implementation of UTF8 in the driver. So we have held off on making UTF8 the default, but now is probably a good time to do that for all new installs. This was already the case for sites creating using Deploy To Azure for some time now (it's set in the script which creates the Web App).

[srutzky]

Early sites using PN did not have access to a clean implementation of UTF8 in the driver.

Meaning that what I am experiencing is expected behavior when UTF-8 is not enabled? Which driver are you referring to? I saw the note you put in #303 about setting ProjectNami.UTF8 at the beginning, but why? Is there any requirement to use a _UTF8 collation as the WP DB's default collation? I see what setting ProjectNami.UTF8 does but not sure how that accounts for not prefixing strings with N unless the DB is using a _UTF8 collation, but that is a fairly recent offering. (Sorry, I'm trying to not waste your time with simple / noob questions and I do appreciate your quick responses so I've been digging around as much as I can to figure it out but there's just a few things that aren't glaringly obvious).

[patrickebates]

The PHP SqlSrv driver has a switch that enables UTF-8 mode for it's connection with the database. When enabled, I have seen issues like you describe corrected. Another symptom of this issue is things like smart quotes being stored in the database as "trash characters" but appearing correctly in both the front and back ends of the site.

[srutzky]

@patrickebates Ok, getting closer, I think. A few questions:

1. When UTF-8 mode in the PHP SQL Server driver is enabled, do the problems only go away if the DB is using a UTF-8 collation, or does this correct things pre-SQL Server 2019?
2. Regarding seeing "smart quotes" turned into other characters but come out correctly:
   1. Do you mean that the ( “ ) character would get saved in the DB as ( “ )?
   2. Does this behavior happen when the UTF-8 mode for the PHP SQL Server driver is enabled or disabled?

[patrickebates]

From my experience, UTF-8 collation is not required to solve the problems. I have seen examples where the collation might need to be set to the appropriate language page for some character sets.

Yes, that's exactly what I'm referring to for how a character is saved in the DB. That should stop when UTF-8 mode is enabled for the SqlSrv driver.

[srutzky]

@patrickebates Ok. the characters saving as different characters but coming out correctly is due to the driver sending the raw UTF-8 bytes when "UTF-8 mode" is not enabled. I'm guessing that when "UTF-8 mode" is enabled, the incoming UTF-8 bytes get converted to UTF-16 (so that the character would actually be correct when hitting SQL Server, if treated as NVARCHAR).

The fact that the DB collation plays a role in how strings are stored confirms my suspicion that the root cause of these issues is that the "prepare()" method generates SQL containing VARCHAR string literals (i.e. 'string' instead of N'string'). The only two fixes for this are:

1. Set the WP DB's default collation to a _UTF8 collation. This is easier to accomplish if done prior to loading the tables. This also requires SQL Server 2019, or Azure SQL DB (Single DB or Managed Instance). But, it doesn't require any code changes, which means there is really nothing to do in terms of this project, outside of (ideally) documenting the option.
2. Update the code to treat incoming string values are NVARCHAR, which means prefixing the generated string literals for INSERT / UPDATE / MERGE statements (or even WHERE predicates if these values are ever used in that way) with an upper-case N. I believe that Line 1351 in wp-db.php (linked at the top of this issue) is at least one, if not the only, source of these string literals. However, with the only placeholders being %d (integer), %f (float), and %s (string), if there is a need to also handle DATE, DATETIME, or UNIQUEIDENTIFIER (GUID) using %s since those types are also quoted, then simply adding N to line 1351 won't work as the upper-case N doesn't work on DATETIME / UNIQUEIDENTIFIER literals. Thoughts?
   Another potential complication with this approach is that, based on the info you provided regarding UTF-8 mode in the PHP SQL Server driver, this solution would require that UTF-8 mode be enabled.
   HOWEVER, this approach would also work for SQL Server versions 2012, 2014, 2016, and 2017 as it does not require a UTF-8 collation (technically it works with all versions of SQL Server since at least 7.0, but Project Nami doesn't support prior to 2012, so I just listed the relevant versions).

Also, can you do me a favor and try saving a post with the following character copied and pasted into the post, as is (i.e. not HTML encoded)? I am curious if it comes back as ??:

𒍅

Thanks!

[srutzky]

There were minor changes to wp-db.php since I posted this issue, so the line number (that was updated) ended up being 1355 instead of 1351.