Framework: Disabling SQL Server binary-encoding of uniqueidentifier columns

[mitchdav]

• Laravel Version: 5.6.21
• PHP Version:

PHP 7.1.16 (cli) (built: Apr 12 2018 03:28:19) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

• OS Version: macOS High Sierra 10.13.4
• Homebrew Version:

Homebrew 1.6.2
Homebrew/homebrew-core (git revision 662c2; last commit 2018-04-25)

• Database Driver & Version: SQL Server 4.3.0 connecting to SQL Server 2008 R2 (10.50.2550.0)

| phpinfo() Section | Option | Value |
|-------------------|--------------------------------------|-------------------------------------------|
| odbc | Active Persistent Links | 0 |
| | Active Links | 0 |
| | ODBC library | unixODBC |
| | ODBCVER | 0x0380 |
| | ODBC_INCLUDE | -I/usr/local/opt/unixodbc/include |
| | ODBC_LFLAGS | -L/usr/local/opt/unixodbc/lib |
| | ODBC_LIBS | -lodbc |
| | odbc.allow_persistent | On |
| | odbc.check_persistent | On |
| | odbc.default_cursortype | Static cursor |
| | odbc.default_db | _no value_ |
| | odbc.default_pw | _no value_ |
| | odbc.default_user | _no value_ |
| | odbc.defaultbinmode | return as char |
| | odbc.defaultlrl | return up to 4096 bytes |
| | odbc.max_links | Unlimited |
| | odbc.max_persistent | Unlimited |
| PDO | PDO drivers | dblib, mysql, odbc, pgsql, sqlite, sqlsrv |
| PDO_ODBC | ODBC Connection Pooling | Enabled, strict matching |
| pdo_sqlsrv | ExtensionVer | 4.3.0 |
| | pdo_sqlsrv.client_buffer_max_kb_size | 10240 |
| | pdo_sqlsrv.log_severity | 0 |
| sqlsrv | ExtensionVer | 4.3.0 |
| | sqlsrv.ClientBufferMaxKBSize | 10240 |
| | sqlsrv.LogSeverity | 0 |
| | sqlsrv.LogSubsystems | 0 |
| | sqlsrv.WarningsReturnAsErrors | On |

Description:

When querying SQL Server uniqueidentifier columns, Laravel returns a binary-encoded value by default, which requires a manual CONVERT(NVARCHAR(36), newid()) to retrieve it in the original UUID format.

Is there a way to disable this behaviour? The queries are being run as part of a reporting tool, and thus not going through Eloquent.

I've confirmed this is happening for queries run through Laravel only, as running queries using the native sqlsrv functions returns the original UUID format.

The SQL being tested is:

SELECT newid() AS 'raw', CONVERT(NVARCHAR(36), newid()) AS 'varchar';

Similar issues have been reported:

• https://laracasts.com/discuss/channels/eloquent/eloquent-dont-return-a-guid-from-sql-server
• https://laracasts.com/discuss/channels/eloquent/sql-server-uniqueidentifier-generated-by-newsequentialid

I am unsure if these items are related:

• https://github.com/laravel/framework/pull/22279 (MySQL)
• https://github.com/laravel/ideas/issues/350 (MySQL)
• https://github.com/laravel/ideas/issues/645

Steps To Reproduce:

To test the query outside of Laravel, create a new PHP file named test-without-laravel.php and copy the contents from here, filling in the database details in lines 7-13. Then just run php test-without-laravel.php, and it will output a result similar to this:

array(2) {
    ["raw"]=>
  string(36) "935A4BDE-0815-4CD9-9D10-49C109B0C0E3"
    ["varchar"]=>
  string(36) "4C5BB149-6B91-4F83-B442-5D6BBFD596E3"
}

To test the query inside of Laravel, create a new project, and then add a new command using php artisan make:command TestSQL. Add the new command to your app/Console/Kernel.php, and then copy the contents from here into the app/Console/Commands/TestSQL.php file. Fill in your credentials to the .env file, and then just run php artisan test-sql, and it will output a result similar to this:

object(stdClass)#569 (2) {
["raw"]=>
  string(16) "]�Za�F��k�U<@�"
["varchar"]=>
  string(36) "9F38B88D-099A-4E6D-8091-2E46EE52B203"
}

If you need any further information, please let me know - thanks.

[hakanersu]

Im hope i understand correctly; you can provide options with pdo attributes.

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'localhost'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'options' => [
        PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER => true,
    ],
    'prefix' => '',
],

[mitchdav]

Hi @hakanersu, that seems to work perfectly - thank you!

I do wonder what is causing the difference in output between plain PHP and Laravel specifically though? Is this behaviour that can be applied as standard for all SQL Server/Laravel users?

Thanks again.

[staudenmeir]

This is an issue with the database driver, not with Laravel. test-without-laravel.php uses sqlsrv_connect() instead of the PDO connection used by Laravel (a completely different technique). You would have to compare the Laravel result with a "raw" PDO connection.

The query you posted works for me with the latest pdo_sqlsrv driver.

[driesvints]

Please see the answer above.

[patchido]

@hakanersu @staudenmeir I am unable to make it work with my setup. To be able to use MSSQL i had to install the driver like yum install php72-mssql. is that the same driver you used?

'sqlsrv' => [
            'driver' => 'sqlsrv',
           'host' => env('DB_HOSTSQL', 'localhost'),
            'port' => env('DB_PORTSQL', '1433'),
            'database' => env('DB_DATABASESQL', 'forge'),
            'username' => env('DB_USERNAMESQL', 'forge'),
            'password' => env('DB_PASSWORDSQL', ''),
            'charset' => 'utf8',
            'options' =>[
                PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER => true,
            ],
            'prefix' => '',
            'prefix_indexes' => true,
        ],

[staudenmeir]

I'm using the drivers from https://github.com/microsoft/msphpsql.

[patchido]

@staudenmeir As i am using AWS i have no idea which of the install instructions i should use.

[hakanersu]

@patchido Can you check php.ini file. Is extension uncommented?

[patchido]

@patchido Can you check php.ini file. Is extension uncommented?

which extension? i think it is or i wouldn't be able to query the DB