UPDATE VARBINARY(MAX) column in SQL Server table with $_FILE image upload

Question

I've been looking all across the internet for help on this and have found nothing.

Basically I need to know how to update a SQL Server VARBINARY(MAX) column with the hex of an image uploaded from a HTML form. The database is in a different place to the HTML form, so move_uploaded_file in PHP then OPENROWSET (BULK ...) in SQL doesn't work (unable to find the file).

I also tried doing file_get_contents on the uploaded $_FILE['name_']['tmp_name'], then used unpack("H*hex") and put the result of that into the SQL column with a "0x" prepend, but that crashes, saying it needs to be converted from a VARCHAR to a VARBINARY. When I convert it, the code runs and the column is populated, but the image is malformed.

No idea what to do next. Pls help.

Answer 1

Solution:

This is a basic approach using PHP Driver for SQL Server:

Table creation (T-SQL):

CREATE TABLE [dbo].[ImageTable] (
    [ImageData] varbinary(max) NULL
)

PHP:

<?php
# Connection
$server = 'server\instance,port';
$database = 'database';
$uid = 'user';
$pwd = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $uid,
    "PWD" => $pwd
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Update image using CONVERT()
$image = file_get_contents('image.jpg');
$sql = "UPDATE ImageTable SET [ImageData] = CONVERT(varbinary(max), ?) WHERE (yor_update_condition)";
$params = array(
    array($image, SQLSRV_PARAM_IN)
);
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

# End
echo 'Image updated.'
?>