Php with MSSQL display raw data from varbinary field

Question

I am trying to display the raw data from a varbinary field in SQL server in php. I want to return exactly what I have in SQL Server (0x00000etc.) but it seems to be doing some sort of conversion and returning to me something like )T¡òaýCž«V°Ø‘©O

Hopefully this makes sense to someone.

Thank you

Durstin

What are you viewing the output in? Do you want to display it in hexadecimal format? – Kemme

Answer 1

Data printed out is always interpreted as character data when outputted in a browser. If you want the exact HEX or BIN data representation, you will need to convert it either when you SELECT the data using: HEX()

SELECT HEX(mydata) as hexdata FROM mytable ...

And when you output it, it will now be a string of HEX characters. I think there is an equivalent for binary format which would output 0s and 1s but i'm not sure...

If you can't convert the data at the mysql level (there can be tons of reasons) then you can use the PHP equivalent bin2hex:

echo bin2hex($mydata['mybinarydata'];

The docs for bin2hex can be found at: http://www.php.net/bin2hex

Good luck

Shemeka

I guess to elaborate a bit further, I have images (physical images) That are saved as this varbinary field .jpg so the image on the image server is 0x0123etc.jpg and I am needing to use the data from the server to find the filename. – Durstin

Answer 2

Safer solution to avoid sql injection and utf8 conversion errors:

public function setFileContentAttribute($value)
    {
        if ('sqlsrv' === config('database.default')) { // todo or some other way to check if its sql server connection
            return $this->attributes['file_content'] = DB::raw('CONVERT(VARBINARY(MAX), 0x' . bin2hex($value) . ')');
        }
        return $this->attributes['file_content'] = $value;
    }

Tiffinytiffy