How to store and retrieve extended ASCII characters in MSSQL

Question

I was surprised that I was unable to find a straightforward answer to this question by searching.

I have a web application in PHP that takes user input. Due to the nature of the application, users may often use extended ASCII characters (a.k.a. "ALT codes").

My specific issue at the moment is with ALT code 26, which is a right arrow (→). This will be accompanied with other text to be stored in the same field (for example, 'this→that').

My column type is NVARCHAR.

Here's what I've tried:

1. I've tried doing no conversions and just inserting the value as normal, but the value gets stored as thisâ??that.

2. I've tried converting the value to UCS-2 in PHP using iconv('UTF-8', 'UCS-2', $value), but I get an error saying Unclosed quotation mark after the character string 't'.. The query ends up looking like this: UPDATE myTable SET myColumn = 'this�!that'.

3. I've tried doing the above conversion and then adding an N before the quoted value, but I get the same error message. The query looks like this: UPDATE myTable SET myColumn = N'this�!that'.

4. I've tried removing the UCS-2 conversion and just adding the N before the quoted value, and the query works again, but the value is stored as thisâ that.

5. I've tried using utf8_decode($value) in PHP, but then the arrow is just replaced with a question mark.

So can anyone answer the (seemingly simple) question of, how can I store this value in my database and then retrieve it as it was originally typed?

I'm using PHP 5.5 and MSSQL 2012. If any question of driver/OS version comes into play, it's a Linux server connecting via FreeTDS. There is no possibility of changing this.

Answer 1

You might try base64 encoding the input, this is fairly trivial to handle with PHP's base64_encode() and base64_decode() and it should handle what ever your users throw at it.

(edit: You can apparently also do the base64 encoding on the SQL Server side. This doesn't seem like something it should be responsible for imho, but it's an option.)