Empty string set to output parameter on stored procedure returns garbage.

[leehicks]

Here is a simple test procedure. Tested versions - Windows 2008 R2 Server (4.1.4), Ubuntu 16.04 (4.0.6).

create procedure test_empty_out @test varchar(200)='' OUTPUT
as begin
set nocount on;
set @test='';
select hello_world_column='this is a column in a single dataset';
end

If @test is set to null or " " (single space), everything returns fine, but when empty string is returned the bound memory location in the driver seems to not get set properly when the procedure call is closed out. When you try to access that bound variable, you either get junk from the heap (Windows) or PHP crashes (Ubuntu 16.04).

[Hadis-Knj]

@leehicks We are looking into this issue, Could you send a PHP repo script?

[leehicks]

A simple test script would be something like this...

$pdo = new PDO( 'sqlsrv:Server=192.168.1.1,1433;Database=AdventureWorksDW', 'user', 'pwd');
$query = "EXEC [test_empty_out] @test=:Test";
$stmt = $pdo->prepare($query);
$out = '';
$stmt->bindParam(':Test', $out, PDO::PARAM_STR | \PDO::PARAM_INPUT_OUTPUT, 200);
$stmt->execute();
$result = $stmt->fetchAll();
$stmt->closeCursor();
echo "OUT value: $out";

[meet-bhagdev]

@leehicks thanks for the repro. We are able to reproduce this and are looking into fixing it. Will keep you posted on the status in the coming days

[Hadis-Knj]

@leehicks The issue is fixed in the source code with empty bound parameters, additionally, you should also call $stmt->closeCursor(); after $result = $stmt->fetchAll(); so the result would be processed. PECL package and dlls will be available in few days.

[meet-bhagdev]

@leehicks this issue is fixed. You can get the latest release via our PECL packages. https://github.com/Microsoft/msphpsql/tree/PHP-7.0-Linux#install

For Windows, it is in the source code, we will publish the dll's soon.

[leehicks]

Just getting around to testing this after the holiday break. I no longer get the garbage output, but I am seeing that "$out" is set to null after the closeCursor() call, instead of "" (which the stored proc is actually returning in this case). All other tested values seem to return fine. I think "" should be the proper response.