How to store and retrieve extended ASCII characters in MSSQL

Question

I was surprised that I could not find a direct answer to this question by doing a search.

I have a PHP web application that introduces the user. Due to the nature of the application, users can often use extended ASCII characters (aka "ALT codes").

My specific problem at the moment is the ALT 26 code, which is the right arrow (→). This will be accompanied by other text that will be stored in one field (for example, 'this→that' ).

My column type is NVARCHAR.

Here is what I tried:

• I tried to do no transformations and simply inserted the value as usual, but the value gets like thisâ??that .

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

• I tried to do the above conversion and then added N to the specified value, but I get the same error message. The query looks like this: UPDATE myTable SET myColumn = N'this !that' .

• I tried to remove the UCS-2 transform and just add N before the quoted value, and the query works again, but the value is saved as thisâ that .

• I tried using utf8_decode($value) in PHP, but then the arrow is simply replaced by a question mark.

So can anyone answer the (seemingly simple) question of how I can save this value in my database and then restore it as it was originally printed?

I use PHP 5.5 and MSSQL 2012. If any question about the driver / OS version comes into play, this is a Linux server connecting via FreeTDS. There is no way to change this.

Answer 1

You can try base64 to encode input, it's pretty trivial to process PHP base64_encode() and base64_decode() , and it should handle what your users ever threw at it.

(edit: you can also do base64 encoding on the SQL Server side . This is not like what should be responsible for imho, but it is an option.)

Answer 2

It seems your freetds.conf is wrong. To support Unicode, you need the TDS protocol version> = 7.0. See more details .

Change your freetds.conf :

 [global] # TDS protocol version tds version = 7.4 client charset = UTF-8 

Also make sure PHP is configured correctly:

 ini_set('mssql.charset', 'UTF-8'); 

Answer 3

How do you have thisâ†'!that , ASCII copy of the UTF-8 encoding this→!that .

Putting everything in order immediately after the proposals made here will not worry the whole problem. Especially if you have already broken the data in your tables, which are likely to be.

You need to start a new search to find the right path.

• Create a new table with at least one nvarchar column, say TestTable(Column1)
• Create an empty UTF-8 encoded request for hard-coded inputs during tests - a PHP file. test.php
• In freetds.conf be sure to add the client charset = UTF-8 parameter in the [global] or [WhateverYourServerNameIs] section. This is the character set that will be used for communication between FreeTDS and SQL Server.

test.php

 <?php // character set to be used in response header('Content-Type: text/plain; charset=utf-8'); // charset to be used in communication between PHP driver and FreeTDS. ini_set('mssql.charset', 'UTF-8'); // make the connection $conn = mssql_connect("ServerNameSpecifiedInFreetdsDotConf", "username", "passw0rd"); // select database mssql_select_db("DatabaseName", $conn); // insert something immediately // do not forget to use the N prefix, may cause problems with mssql_query if omitted $insertQuery = mssql_query("Insert Into TestTable(Column1) Select N'this→that';", $conn); // list all the records $query = mssql_query( "Select * From TestTable", $conn ); while ($row = mssql_fetch_array($query)) echo $row["Column1"].PHP_EOL; mssql_close($conn); // close the connection ?> 

Run test.php several times and see what happens. If you see this→that both the PHP response and SQL Server Management Studio, that means you're all set up.

If you still get unexpected results, let us know.

Answer 4

The accepted answer seems to do the job; yes, you can encode it to base64 and then decode it again, but then all applications using this remote database must change and support the fields that base64 will encode. I believe that if there is a remote MS SQL Server database, there may be another application (or applications) that can use it, so the application should also be modified to support both simple and base64 encoding. And you will also have to process both plain text and base64 text.

I searched a bit and I found how to send UNICODE text to MS SQL Server using MS SQL and PHP commands to convert UNICODE bytes to HEX numbers.

If you go to the PHP documentation for mssql_fetch_array ( http://php.net/manual/ru/function.mssql-fetch-array.php#80076 ), you will see comments a good solution that converts text to UNICODE HEX values ​​and then sends this HEX data directly to MS SQL Server as follows:

Convert Unicode text to HEX data

 // sending data to database $utf8 = 'Δοκιμή με unicode → Test with Unicode'; // some Greek text for example $ucs2 = iconv('UTF-8', 'UCS-2LE', $utf8); // converting UCS-2 string into "binary" hexadecimal form $arr = unpack('H*hex', $ucs2); $hex = "0x{$arr['hex']}"; // IMPORTANT! // please note that value must be passed without apostrophes // it should be "... values(0x0123456789ABCEF) ...", not "... values('0x0123456789ABCEF') ..." mssql_query("INSERT INTO mytable (myfield) VALUES ({$hex})", $link); 

Now all the text is actually stored in the NVARCHAR database NVARCHAR correctly, like UNICODE, and all you need to do to send and save it as plain text and not encode.

To get this text, you need to ask MS SQL Server to send back the UNICODE encoded text as follows:

Unicode text extraction from MS SQL Server

 // retrieving data from database // IMPORTANT! // please note that "varbinary" expects number of bytes // in this example it must be 200 (bytes), while size of field is 100 (UCS-2 chars) // myfield is of 50 length, so I set VARBINARY to 100 $result = mssql_query("SELECT CONVERT(VARBINARY(100), myfield) AS myfield FROM mytable", $link); while (($row = mssql_fetch_array($result, MSSQL_BOTH))) { // we get data in UCS-2 // I use UTF-8 in my project, so I encode it back echo '1. '.iconv('UCS-2LE', 'UTF-8', $row['myfield'])).PHP_EOL; // or you can even use mb_convert_encoding to convert from UCS-2LE to UTF-8 echo '2. '.mb_convert_encoding($row['myfield'], 'UTF-8', 'UCS-2LE').PHP_EOL; } 

MS SQL table with UNICODE data after INSERT

Output result using a PHP page to display values

I'm not sure you can get to my test page here, but you can try to see the results: http://dbg.deve.wiznet.gr/php56/mssql/test1.php