sql server - PHP, ORM, MSSQL and Unicode, is it possible to make these work together?

Question

If you are saving unicode data to MSSQL you need to save into a column type nvarchar (et. al) and you must prefix SQL STRING LITERALS with capital N. If you are using prepared statements, then you do not need to prefix values with N.

insert into tbl (col) values (N'hello')

If you are using an ORM like ZF DB, then you are using PDO to conect to MSSQL (or you are deploying on Windows, in which case you're using SQLSRV or PDO_SQLSRV and everything will work and my question doesn't make sense anymore).

If you are using any PDO variant that connects to mssql (sybase, dblib, etc) on Linux then you do not get real prepared statements, only emulated.

If you have emulated prepared statements, at the wire level, the SQL is completely written out with only string literals and this means that you must prefix any potential unicode value with N.

1. Is it at all possible to automatically prepend any char value with N with ZF?
2. Are there any ORMs/TableGateway libs that allow this sort of value manipulation?
3. Is there any way to enable server side prepared statements from Linux?
4. Does anyone actually run production PHP from Linux code and have it run against MSSQL? (if yes, how do you deal with Unicode?)

EDIT: I can get unicode to save to MSSQL from Linux, but upgrading FreeTDS protocol to 7/8 seems to break all frameworks/libraries/orms because of the absolute requirement to alter what would be normal SQL with the N prefix. I'm not sure how anyone on the Internet who claims that upgrading to protocol version 7/8 fixed their unicode problems actually does anything besides hand write every SQL statement in a non-portable way. It seems that staying on Freetds 4.2 is the best way to deal with Unicode/UTF-8 and mssql.

Answer 1

Update: The driver is no longer in preview. MS has provided official instructions for the now released version: https://www.microsoft.com/en-us/sql-server/developer-get-started/php-ubuntu

The instructions below are now out of date as MS has pulled the preview driver download.

---

Well, there is the ODBC driver provided by Microsoft. That should provide proper behavior in this regard. See the end of the post for how I tested its behavior (in a preliminary manner). It was tested against an Azure SQL Database V12.

How to install the Microsoft SQL ODBC driver on Ubuntu 16.04

This was tested on the fresh Ubuntu 16.04 Azure instance that was based on the Ubuntu 16.04 Azure image provided by Canonical. After logging in, I switched to the root user using sudo -i, then:

apt-get update
apt-get -y install atool make build-essential libc6 libkrb5-3 libgss3 e2fsprogs openssl equivs
wget https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz
atool -x msodbcsql-13.0.0.0.tar.gz
rm msodbcsql-13.0.0.0.tar.gz

pushd msodbcsql-13.0.0.0/
./build_dm.sh --accept-warning | tee build_dm_result.txt
command=$(cat build_dm_result.txt | grep "Run the command" | cut -d"'" -f2)
rm build_dm_result.txt
sh -c "$command"
popd

echo "/usr/lib64" > /etc/ld.so.conf.d/microsoft-lib64.conf
ldconfig

pushd msodbcsql-13.0.0.0/
./install.sh  install --accept-license

Test it

Replace the server and the credentials in the following command with your own.

sqlcmd -S somedatabase.database.windows.net -U someuser -P somepassword

You should be able to issue SQL commands at this point. Okay, let's get it working with php.

Use it with php

We must make sure the libodbc1 package is not installed and that it will not get installed, as that would used by php instead of our custom complied one, and that would lead to encoding issues.

cat > libodbc1<<EOL
Section: misc
Priority: optional
Standards-Version: 3.9.2

Package: libodbc1
Version: 9999
Description: fake pkg, so that we satisfy the dependency of php7-odbc, so that we can keep our custom built libodbc
EOL

equivs-build libodbc1
dpkg -i libodbc1_9999_all.deb
rm libodbc1
rm libodbc1_9999_all.deb

apt-get install php7.0-odbc php7.0-cli

At this point, you should have it available as an ODBC Driver.

Testing its behaviour

Create a php file, test.php with UTF-8 encoding, and with the following content. Replace the server, the database and the credentials in the connection string with your own.

<?php

$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=tcp:somedatabase.database.windows.net,1433;Database=somedatabase;Uid=someuser@somedatabase;Pwd=somepassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;');

$str = 'árvízt?r? tük?rfúrógép, and... 你好，世界';

$pdo->prepare("DROP TABLE test")->execute();
$pdo->prepare("CREATE TABLE test(a NVARCHAR(MAX))")->execute();
$stmt = $pdo->prepare("INSERT INTO test VALUES(?)");
$stmt->bindParam(1, $str);
$stmt->execute();

$stmt = $pdo->prepare("SELECT * FROM test");
$stmt->execute();
$data = $stmt->fetchall();
var_dump($data[0][0]==$str); //Returns true

$stmt = $pdo->prepare("SELECT * FROM test WHERE a=?");
$stmt->bindParam(1, $str);
$stmt->execute();
$data = $stmt->fetchall();
var_dump($data[0][0]==$str); //Returns true

Running this with php -f test.php shows that we get back the string without any corruption. Also, the string looks good from SQL Server Management Studio too. I observed following query on the Performance Insight page of the Azure Portal: (@P1 nvarchar(max))INSERT INTO test VALUES(@P1), so prepared statements were obviously used, so I assume that it could handle your (and my) scenario.

(This post was of great help while trying to get this to work: http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ Thanks boris!)