0

I have exactly the same application hosted in two different shared servers (one Windows and other Linux), both connecting in the same SQL Server database using PDO.

Somehow in one of the servers (the Linux one) all special chars that comes from SQL Server (like é, ç, õ) are being replaced by � .

The DB charset is Latin1_General_CI_AS and i'm sending the following header to the browser :

<meta charset="UTF-8">

Here is my connection to PDO :

$DBH = new PDO('dblib:host=111.111.111.11;dbname=mydbname;charset=utf8','myuser','mypass')

My question is : how can i find out what is happening here and why the same app runs fine in one server but not in another. Maybe is some configuration in php.ini or in the PDO object ?

I've downloaded a plugin for Chrome that allows you to change the page's character encoding. When i change it to 'Windows-1252', the data coming from the database are displayed correctly, BUT any other element on page that is generated dynamically by PHP gets the special chars replaced by symbols like ç and ã .

Please do not mark this question as duplicated, because it's not ; i can't change the DB charset to UTF-8. And because the application runs fine in the Windows server, i think there is something i can do to fix the issue in the Linux server, besides messing with the database configuration (which i don't have access at the moment).

Any ideas ?

Thanks !

delphirules
  • 6,443
  • 17
  • 59
  • 108

2 Answers2

2

Besides client encoding, which you did already by setting <meta charset="utf-8"/>, you will have to set your connection encoding as well (for both applications).

For PHP, the accepted answer in this post suggests you do the following:

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

Edit:

For PDO, you can pass it in the connection string. Example:

$DBH = new PDO('mssql:dbname=spr_bank;host=hostname;charset=utf8', $userDB, $passwordDB);

Note that this won't change your actual table to UTF-8. This will only convert charsets for your connection, making it read/write the right characters. Even if it is a Latin1_General_CI_AS table/database.

CarlosCarucce
  • 3,420
  • 1
  • 28
  • 51
  • Problem is, i'm already sending the charset=utf8. I just updated the question. Somehow in one server it works fine, in the other no... – delphirules Oct 09 '18 at 17:39
  • 1
    @delphirules Just after connecting, try to execute the following: `$DBH->query("SET names UTF8")->execute();` – CarlosCarucce Oct 09 '18 at 17:54
  • Tried, but i get this error : 'SQLSTATE[HY000]: General error: 20018 'names' is not a recognized SET option. [20018] (severity 15) [SET names UTF8]' – delphirules Oct 09 '18 at 17:58
1

Here is what fixed my issue :

  1. Set up /your/local/freetds.conf file:
[sqlservername]
host=111.111.111.11
port=1433
tds version=7.0
client charset=UTF-8

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

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

  2. 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');

delphirules
  • 6,443
  • 17
  • 59
  • 108
  • 1
    Glad that you've find out. Just for information: `ini_set('mssql.charset', 'UTF-8');` is equivalent to setting it directly inside the ini file (or conf in this case). Thanks for sharing your solution tho. – CarlosCarucce Oct 09 '18 at 19:12