0

I am trying to write an image into an SQL Server database in binary format with no success. The image uploaded from my frontend (AngularJS) to my server (PHP) is a Base64 string and I need to then convert it to a raw binary file starting with 0x as the database requires a varBinary(max) datatype.

Everything I try seems to end up with the value wrapped in single quotes thus trying to parse it as a varchar and resulting in an error:

General error: 20018 Implicit conversion from data type varchar(max) to varbinary(max) is not allowed.

My current code snippet:

$img = (binary) '0x'.strtoupper(bin2hex($photo['PhotoImage']));

Seems to always result with single quotes and I don't know how to get around it:

PhotoImage='0x6956424F5277304B47676F414141414E53556845556741414156414141414651434141414141444D67336B464'

Update

After some playing around, I have located my problem, I just don't know how to resolve it.

This statement:

$img = (binary)('0x' . bin2hex($photo['PhotoImage']));
dd(gettype($img));

Which (to me) should be returning me a binary value, is in fact STILL returning a string and hence why my S-PROC is failing:

...
"<span class=sf-dump-str title="6 characters">string</span>"
</pre><script>Sfdump("sf-dump-1548954697")</script>
...

Also, as a small test, it seems PHP cannot declare a true binary:

$img = 5;
dd(gettype($img));

/* "<span class=sf-dump-str title="6 characters">integer</span>" */

$img = (binary)5;
dd(gettype($img));

/* "<span class=sf-dump-str title="6 characters">string</span>" */

How can I force PHP to convert this value to a true binary?

Riples
  • 1,167
  • 2
  • 21
  • 54
  • What extension do you use to connect to SQL Server? – Zhorov Nov 09 '20 at 07:01
  • If the file is base64 encoded binary when it arrives at the server, can't you simply use base64_decode()? – Snake14 Nov 09 '20 at 07:11
  • Have you tried decoding your base64 data to a `(binary)` value and then in your prepared statement specifying the binary value with the `\PDO::PARAM_LOB` data type? – AlwaysLearning Nov 09 '20 at 09:24
  • @Zhorov I use Laravel's built in connector and the pdo_dblib driver – Riples Nov 09 '20 at 23:45
  • @Snake14 The file that hits my server from my front end looks like: `/9j/4AAQSkZJRgABAQEAYABgAAD//gA7Q1JFQVRPUjogZ2Q..........jOI8RIxn/AH4//9k=` – Riples Nov 09 '20 at 23:51
  • @Zhorov I have tried to parse the value as a binary (see above), but i still get it wrapped in single quotes. The prepared statement is an S-PROC that requires a `varBinary(max)` value. Decoding the base64 value just gives me `����.......`. Basically, the `PhotoImage` value shown above will insert successfully if I remove the single quotes, so to me, something is still getting returned as a string. – Riples Nov 09 '20 at 23:57
  • @Riples I don't have enough experience with the `pdo_dblib` driver, but this [Q&A](https://stackoverflow.com/questions/57410357/insert-and-store-in-database-file-in-table-with-a-column-of-type-image/57413032#57413032) may help. – Zhorov Nov 10 '20 at 06:36
  • @Zhorov Thanks for the post. Laravel doesn't seem to support LOB or parameter binding like the PDO by the looks (or from what I can find). I'm quickly finding that Laravel doesn't support much when it comes to MSSQL and could've well been the wrong decision for me by the looks. – Riples Nov 10 '20 at 11:19
  • @Riples, I have limited experience with Laravel, but if I remeber correctly, Laravel's latest versions support `pdo_sqlsrv` (PHP Driver for SQL Server, PDO version). – Zhorov Nov 10 '20 at 11:22

0 Answers0