Garbled GUIDs when selecting from a MS SQL DB in PHP?

Question

I'm using PDO to select from an MS SQL DB, but I get the same results when using mssql_connect(). Some of the fields containing GUIDs (e.g. "F6465061-8E9C-4EA2-9DFD-65B9598AA7FA") are outputting as gibberish, while the other fields come through just fine. Is this some kind of encoding issue? At the DB level, or in my querying?

The display is fine in NaviCat for SQL Server:

Output in NaviCat for SQL Server

But when I run the same query with PHP, the output looks like this:

[0] => Array
(
    [0] => ƒg   C0³O™qúŠ˜M²
    [1] => Michael Mizrachi
    [2] => œÆ:²ùöL‚‹!hØåq?
    [3] => Player
    [4] => åBÐÜ·ÀjF‡æá:í%§
    [5] => )ÛjÀÛ†‹E“l7åô
    [6] => Jan 11 2008 06:13:54:657PM
    [7] => Dec 28 2008 01:17:29:733PM
)

Here's my PHP code, in case it helps:

try {
    $dbh = new PDO('dblib:host='.$sql_host.';dbname='.$sql_db, $sql_user, $sql_pw);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = file_get_contents("player.sql");
$stmt = $dbh->query($query);

do {
    $rowset = $stmt->fetchAll(PDO::FETCH_NUM);
    if ($rowset) {
        print_r($rowset);
    }
} while ($stmt->nextRowset());

Any ideas??

Here's a chunk of the SQL query:

BEGIN

    DECLARE @id uniqueidentifier = '{18096783-3043-4FB3-9971-FA8A1A984DB2}';
    DECLARE @version int;

    SELECT
         i.[ID]
        ,i.[Name]
        ,i.[TemplateID]
        ,t.[Name] as [TemplateName]
        ,i.[MasterID]
        ,i.[ParentID]
        ,i.[Created]
        ,i.[Updated]
    FROM
        [dbo].[Items] i (nolock)
    JOIN
        [dbo].[Items] t (nolock)
        ON t.[ID] = i.[TemplateID]
    WHERE
        i.[ID] = @id
    SELECT
        @version = MAX(Version)
    FROM    
        [dbo].[VersionedFields] (nolock)
    WHERE
        [ItemId] = @id

http://stackoverflow.com/questions/279170/utf-8-all-the-way-through — Mihai, Oct 18 '14 at 22:26
@Mihai is this a utf-8 issue? i tried specifying it for the pdo object and for php with default_charset, but it didn't really clear anything up. just made it a different kind of garbled. — Skwerl, Oct 18 '14 at 22:36
Well your solution does the same thing converts your values to unicode.But I dont know the details well enough to tell you why directly converting in sql works but not in php. — Mihai, Oct 18 '14 at 22:51

score 4 · Answer 1 · answered Jan 06 '17 at 04:22

4

I know this is an old post, but I was still encountering this issue until recently. The cause of this issue in PHP 7 is that you have to instruct PDO to convert the GUIDs from binary to a string.

Here is how I resolved his issue.

    /** @var \PDO $pdo */
    $pdo->setAttribute(\PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER, true);

answered Jan 06 '17 at 04:22

TylersSN

1,391
3
21
39

Solution proposed by @TylersSN should be the one, but I can't just get it working. It looks like that attribute is just ignored. – Massimiliano Arione Jun 06 '17 at 08:39
This worked for me, added this to AppServiceProvider::register(): `$pdo = \DB::connection('sqlsrv')->getPdo(); $pdo->setAttribute(\PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER, true);` – AVProgrammer Nov 28 '17 at 19:02

score 1 · Answer 2 · answered Oct 18 '14 at 22:45

A friend on Twitter offered this solution:

BEGIN

    DECLARE @id uniqueidentifier = '{18096783-3043-4FB3-9971-FA8A1A984DB2}';
    DECLARE @version int;

    SELECT
        convert(nvarchar(255),i.[ID])
        ,i.[Name]
        ,convert(nvarchar(255),i.[TemplateID])
        ,t.[Name] as [TemplateName]
        ,convert(nvarchar(255),i.[MasterID])
        ,convert(nvarchar(255),i.[ParentID])
        ,i.[Created]
        ,i.[Updated]

Output:

[0] => Array
    (
        [0] => 18096783-3043-4FB3-9971-FA8A1A984DB2
        [1] => Michael Mizrachi
        [2] => 153AC69C-F9B2-4CF6-828B-2168D8E5713F
        [3] => Player
        [4] => DCD042E5-C0B7-466A-87E6-E13AED1D25A7
        [5] => C06ADB29-86DB-458B-936C-37E58F141CF4
        [6] => Jan 11 2008 06:13:54:657PM
        [7] => Dec 28 2008 01:17:29:733PM
    )

Garbled GUIDs when selecting from a MS SQL DB in PHP?

2 Answers2