PHP + SQL Server - How to set charset for connection?

Question

I'm trying to store some data in a SQL Server database through php.

Problem is that special chars aren't converted properly. My app's charset is iso-8859-1 and the one used by the server is windows-1252.

Converting the data manually before inserting doesn't help, there seems to be some conversion going on.

Running the SQL query 'set char_convert off' doesn't help either.

Anyone have any idea how I can get this to work?

EDIT: I have tried ini_set('mssql.charset', 'windows-1252'); as well, but no result with that one either.

mssql.charset is only available with FreeTDS. Does this config parameter show up in phpinfo() on your server? — VolkerK, Aug 24 '09 at 14:12
Yes, it does show up. Phpinfo also states that the MSSQL Library version is FreeTDS. local value: windows-1252 master value: no value — Maurice, Aug 24 '09 at 14:58

score 25 · Accepted Answer · edited Apr 12 '17 at 07:31

Client charset is necessary but not sufficient:

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

I searched for two days how to insert UTF-8 data (from web forms) into MSSQL 2008 through PHP. I read everywhere that you can't, you need to convert to UCS2 first (like cypher's solution recommends). On Windows SQLSRV said to be a good solution, which I couldn't try, since I am developing on Mac OSX.

However, FreeTDS manual (what PHP mssql uses on OSX) says to add a letter "N" before the opening quote:

mssql_query("INSERT INTO table (nvarcharField) VALUES (N'űáúőűá球最大的采购批发平台')", +xon);

According to this discussion, N character tells the server to convert to Unicode. https://softwareengineering.stackexchange.com/questions/155859/why-do-we-need-to-put-n-before-strings-in-microsoft-sql-server

score 6 · Answer 2 · edited Mar 06 '12 at 17:34

6

I had the same problem and ini_set('mssql.charset', 'utf-8') did not work for me. However, it worked in uppercase:

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

edited Mar 06 '12 at 17:34

answered Mar 06 '12 at 10:49

Jaime Neto

61
1
1

score 5 · Answer 3 · answered Feb 05 '15 at 22:55

5

If ini_set('mssql.charset', 'UTF-8'); doesn't help AND you don't have root access to modify the system wide freetds.conf file, here's what you can do:

1. Set up /your/local/freetds.conf file:

[sqlservername]
    host=192.168.0.56
    port=1433
    tds version=7.0
    client charset=UTF-8

2. Make sure your connection DSN is using the servername, not the IP:

'dsn' => 'dblib:host=sqlservername;dbname=yourdb

3. Make FreeTDS to use your local freetds.conf file as an unprivileged user from php script via env variables:

putenv('FREETDSCONF=/your/local/freetds.conf');

answered Feb 05 '15 at 22:55

Attila Fulop

6,861
2
44
50

1

Wasted 3 hours on this. Thank you! – Yarimadam Sep 06 '18 at 20:27
1

Thank you, i wasted 3 DAYS on this issue ! o/ – delphirules Oct 09 '18 at 18:49

score 5 · Answer 4 · answered Feb 13 '12 at 11:34

5

I suggest looking at the following points:

Ensure that the columns that you're storing the information in are nchar or nvarchar as char and nvarchar don't support UCS-2 (SQLServer doesn't store in UTF-8 format btw)
If you're connecting with the mssql library/extension for PHP, run: ini_set('mssql.charset', 'utf-8'); as there's no function with a charset argument (connect, query etc)
Ensure that your browsers charset is also set to UTF-8

answered Feb 13 '12 at 11:34

Matthew Setter

2,397
1
19
18

Also, if you're using Zend_Db, then don't forget about the charset parameter when initialising the connection:...params.charset = "utf8" – Matthew Setter Feb 13 '12 at 12:20

score 4 · Answer 5 · answered Aug 20 '13 at 13:13

If you are using TDS protocol version 7 or above, ALL communications over the wire are converted to UCS2. The server will convert from UCS2 into whatever the table or column collation is set to, unless the column is nvarchar or ntext. You can store UTF-8 into regular varchar or text, you just have to use a TDS protocol version lower than 7, like 6.0 or 4.2. The only drawback with this method is that you cannot query any nvarchar, ntext, or sys.* tables (I think you also can't do any CAST()ing) - as the server refuses to send anything that might possibly be converted to UTF-8 to any client using protocol version lower than 7.

It is not possible to avoid converting character sets when using TDS protocol version 7 or higher (roughly equivalent to MSSQL 2005 or newer).

On a Linux system you can set this TDS protocol version in `freetds.conf` by specifying the following options under the parameter grouping `[global]` -- `tds version = 8.0` `client charset = UTF-8` See: https://gist.github.com/johnkary/6643856 — John Kary, Sep 20 '13 at 21:03

score 3 · Answer 6 · answered Dec 17 '18 at 08:14

3

In my case, It worked after I added the "CharacterSet" parameters into sqlsrv_connect() connection's option.

$connectionInfo = array( 
        "Database"=>$DBNAME,
        "ConnectionPooling"=>0,
        "CharacterSet"=>"UTF-8"
);
$LAST_CONNECTION = sqlsrv_connect($DBSERVER, $connectionInfo);

See documentation here : https://learn.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2017

answered Dec 17 '18 at 08:14

Donovan P

591
5
9

This works in my case, SQL Server 2014 and PHP 7.3 running on Windows Server – eduardo a Mar 22 '19 at 17:00

score 2 · Answer 7 · answered Nov 03 '14 at 13:32

For me editing this file:
/etc/freetds/freetds.conf
...and changing/setting 'tds version' parameter to '7.0' helped. Edit your freetds.conf and try to change this parameter for your server configuration (or global).

It will work even without apache restart.

score 2 · Answer 8 · answered Mar 15 '11 at 17:55

I've had luck in a similar situation (using a PDO ODBD connection) using the following code to convert the encoding before printing output:

$data = mb_convert_encoding($data, 'ISO-8859-1', 'windows-1252');

I had to manually set the source encoding, because it was erroneously being reported as 'ISO-8859-1' by mb_detect_encoding().

My data was also being stored in the database by another application, so I might be in a unique situation, although I hope it helps!

score 2 · Answer 9 · answered Mar 01 '19 at 08:16

I did not notice someone to mention another way of converting results from MSSQL database. The good old iconv() function:

iconv (string $in_charset, string $out_charset, string $str): string;

In my case everything else failed to provide meaningful conversion, except this one when getting the results. Of course, this is done inside the loop of parsing the results of the query - from CP1251 to UTF-8:

foreach ($records as $row=>$col) {
    $array[$row]['StatusName'] = iconv ('CP1251', 'UTF-8' , $records[$row]['StatusName']);
}

Ugly, but it works.

score 0 · Answer 10 · answered Aug 24 '09 at 15:33

0

Can't you just convert your tables to your application encoding? Or use utf-8 in both?

I don't know whether MSSQL supports table-level encodings, though.

Also, try the MB (multibyte) string functions, if the above fails.

answered Aug 24 '09 at 15:33

Sandro Gržičić

313
4
17

Unfortunately, I can't. For it is not my database, and it's not my framework either. Converting special characters to entities is not an option either, since there is some non-web software connected to this db aswell. I'm used to converting with iconv, but I'll give MB a try. – Maurice Aug 24 '09 at 16:52
Same results with mb. It seems there is some automatic conversion done while sending data to db. – Maurice Aug 25 '09 at 11:07

score 0 · Answer 11 · answered Jul 31 '15 at 12:13

0

You should set the charset with ini_set('mssql.charset', 'windows-1252') before the connection. If you use it after the mssql_connect it has no effect.

answered Jul 31 '15 at 12:13

achos

1
1

Maja S. · Answer 12 · 2017-03-24T09:55:40.303

0

Just adding ini_set('mssql.charset', 'UTF-8'); didn't help me in my case. I had to specify the UTF-8 character set on the column:

$age = 30;  
$name = utf8_encode("Joe");    

$select = sqlsrv_query($conn, "SELECT * FROM Users WHERE Age = ? AND Name = ?",
    array(array($age), array($name, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('UTF-8')));

edited Mar 24 '17 at 09:55

answered Mar 24 '17 at 09:49

Maja S.

1
1

score -4 · Answer 13 · answered Aug 24 '09 at 13:32

-4

You can use the mysql_set_charset function: http://it2.php.net/manual/en/function.mysql-set-charset.php

answered Aug 24 '09 at 13:32

mck89

294
2
5

4

mysql_set_charset is for MySQL, but the question mentions mssql (sql server) two times. – VolkerK Aug 24 '09 at 13:49

PHP + SQL Server - How to set charset for connection?

13 Answers13

Linked