Can post please be stored in nvarchar?

Question

Can post please be stored in nvarchar?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16017 More actions · Answer 1

might be worth checking filenames of attachments - could also happen that someone adds a filename with special characters just in case it does not go through same code

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

frederico_fonseca wrote:

might be worth checking filenames of attachments - could also happen that someone adds a filename with special characters just in case it does not go through same code

Good call. I suppose an image / movie / whatever file could be named "👾.png". Thom, are you able to test that as well?

Thanks, Solomon....

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

Thom A wrote:

Yep, I amended everything I could. Noticed no loss of characters, so it appears that everything does go through that one line that you highlighted for change.
Thanks again!

Excellent. I posted the PR last night:

Prevent Unicode conversion to 8-bit code page in wp-db.php

And I just checked the schema and can't find any columns of the following datatypes:

DATE
TIME
DATETIMEOFFSET
UNIQUEIDENTIFIER
SQL_VARIANT
DATETIME (all "timestamps" are stored as DATETIME2)

There are only 11 date columns (technically 14, but 3 of them have a duplicate in GMT), and some appear to be non-updatable (e.g. "registered_date").

So that reduces the number of potential issues.

Looking at the schema I found other areas that should be tested, though I am not asking you, Thom, to do that as you have already tested a lot. I'm just listing them here (and will copy to the PR) as notes.

Some dates that might not have been tested:

Email User signing up / registering (signups table)
User activating their registration (signups table)
WordPress follower registration (registration_log table, I think)
Updating a user (users table)
Creating / Updating a link (links table)
Updating blog metadata / appearance / layout / etc (blog_versions table)
Creating / updating blogs on a multi-site installation (blogs table)

Some string columns that might not have been tested:

Terms (are these Categories? they have a "slug" and a taxonomy that includes a "description") (terms and term_taxonomy tables)
Comments: "author" (comments table)
Links: "name", "description", and "notes" (links table)
Options? "name" (options table)
Posts: "name" (different from "title"), "excerpt", "password" (posts table)
Users: "login", "password", "nicename", "display_name" (users table)
Signups (WordPress users, I think): "title" (their blog title, I think) (signups table)

And, yer quite welcome 🙂 .

Take care, Solomon...

This reply was modified 3 years, 2 months ago by Solomon Rutzky.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

The PR I posted back in January ( https://github.com/ProjectNami/projectnami/pull/422 ) has recently been merged, so that makes the fix official.

Steve (or whoever from RedGate is looking at this issue): this issue of non Windows-1252 characters not saving correctly can be fixed by merely adding an upper-case "N" to the PHP code that writes the SQL to be executed. Please see my comment earlier in this thread for the specific line to look for and placement of the character:

https://www.sqlservercentral.com/forums/topic/can-post-please-be-stored-in-nvarchar#post-3818854

The point being: this issue can be fixed without needing to do a full upgrade of Project Nami. The PR was nothing more than the addition of that single character, and Thom A made this change on his system 9 months ago and has not experienced any problems.

I will also update my original forum thread here with this update, or at least a pointer to this post.

Thanks and take care, Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR