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

What are you viewing the output in? Do you want to display it in hexadecimal format? — Marcus Adams
– Marcus Adams, Commented Feb 2, 2012 at 16:34

Mathieu Dumoulin · Accepted Answer · 2012-02-02 16:28:38Z

5

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

answered Feb 2, 2012 at 16:28

Mathieu Dumoulin

12.3k7 gold badges47 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arkansasonline Over a year ago

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.

Joel Harkes · Accepted Answer · 2020-02-25 13:18:27Z

0

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;
    }

answered Feb 25, 2020 at 13:18

Joel Harkes

11.7k3 gold badges49 silver badges71 bronze badges

Collectives™ on Stack Overflow

Php with MSSQL display raw data from varbinary field

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related