php - sqlsrv encoding

Question

My Project builds on PHP and connect to MS SQL Server. I am using sqlsrv library. The fields type in MS SQL is nvarchar. When I define the parameters for connection I also put "utf8". It is:

global $cnf;
$cnf = array();
$cnf["mssql_user"]  = "xxx";
$cnf["mssql_host"]  = "xxx";
$cnf["mssql_pw"]    = "xxx";
$cnf["mssql_db"]    = "xxx";
$cnf["CharacterSet"]    = "**UTF-8**";

When Insert records to database, for Vietnamese content and Chinese content I use:

$city = iconv('UTF-8', 'utf-16le', $post['city']);
$params = array(array($city, null, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY)));
$sql= "INSERT INTO tblCityGarden (city) VALUES(?)
$stmt   = sqlsrv_query( $this->dbhandle, $sql, $params);

It inserts data OK for Vietnamese and Chinese language (the data stored in database for Vietnamese and Chinese is correct).

However when I load the records back into web, It appears the strange character (?, �). I try some php as iconv, mb_detect_encoding, mb_convert_encoding and search many results on internet, but It cannot work. How can I display correct data

Please someone who has experiences about this issues

Have you tried using two different connection arrays to insert each of the two encoding types? So a UTF-8 connection for your standard inserts, then a separate utf-16le connection for the Chinese and Vietnamese inserts? — Derek Foulk
– Derek Foulk, Commented Oct 16, 2015 at 18:24

Derek Foulk · Accepted Answer · 2015-10-16 18:29:15Z

1

I had this same problem (�), but with single quotes, double qoutes, and "Rights Reserved" characters... Here is what I've found:

The CharacterSet specified seems to "rule all", so what you set this to will determine the encoding for the connection (as it should). I did not have ANY CharacterSet configured on my connection(s). Simply setting this resolved my issue, along with making sure that the values that were inserted into my DB were not double encoded via htmlspecialchars().
header()'s must be set before ANY output (this is really important)
headers cannot be set to something different later in the document
Sometimes trailing spaces before and/or after the closing ?> in your PHP file can cause issues (I don't use this closing tag, but I saw this mentioned a lot while searching)

I am not familiar with iconv(), and I am most certainly not experienced with encoding in general, but I solved my issue just by taking the time to check my headers and ensure they meet the above standards...

Your query parameters also look strange: $params = array(array($city, null, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY)));

I have not seen a multidimensional array passed into that argument... (just a note)

answered Oct 16, 2015 at 18:29

Derek Foulk

1,9121 gold badge19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Temitayo Over a year ago

Funny thing is ordinary htmlspecialchars() fixed my hours of troubleshooting. For everyone, if sqlsrv_query() or mssql_query() is causing some special character to be missing in my case < try wrapping the result of the rows in that powerful function. I did foreach($row as $key => $value) { if(is_string($value)) { $row[$key] = htmlspecialchars($value); } }

Derek Foulk Over a year ago

@Temitayo - I'm glad you were able to resolve your issues! I wasn't so lucky... If I remember correctly, I had accidentally performed htmlspecialchars( $value ) twice. So, basically, I had htmlspecialchars( htmlspecialchars( $value ) ) being performed on my values... I believe this was a big part of me arriving at this thread.

Collectives™ on Stack Overflow

php - sqlsrv encoding

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related