save image in mssql database as varbinary(max) without conversion

Question

Our company's ERP software stores employees' pictures as blobs in our MSSQL database.

My task is to find a way to store pictures to the same database table in a way that they can be shown inside the ERP program.

Try #1

$content = file_get_contents('my_path\my_file.png');
INSERT INTO my_table VALUES (CAST('{$data}' AS varbinary(max)))

Produces this error:

SQLSTATE[IMSSP]: An error occurred translating the query string to UTF-16

Checking my encoding:

mb_internal_encoding() is UTF-8, however
mb_detect_encoding($content, 'UTF-8, ISO-8859-1', true) is ISO-8859-1.

Try #2
Converting content encoding to UTF-8:

$content = file_get_contents('my_path\my_file.png');
$data = iconv('', 'UTF-8//TRANSLIT', $content);
INSERT INTO my_table VALUES (CAST('{$data}' AS varbinary(max)))

DOES work for a small black square image.
Trying this on a small red square image however produces this error:

iconv(): Detected an illegal character in input string

Try #3 (a/b/c)
Lots of ways to translate or simply get rid of those illegal characters:

$data = iconv('', 'UTF-8//IGNORE', $content);
$data = base64_encode($data);
$data = mb_convert_encoding($data, 'UTF-8', 'ISO-8859-1');

Allow of them allow us to save to the database, but it doesn't help us because the data are changed and the ERP program can't read them.

Other options
We've thought about the
BulkColumn FROM OPENROWSET(Bulk‘ C: \temp\ nextup.jpg’, SINGLE_BLOB) AS BLOB
way to insert a file into the database, but dismissed it as it requires us to first store the file on the database server's disk, which we don't want to do.

The question
So then the question remains: how to get an uploaded image file into our UTF-16 varbinary field, without modifying the contents?

You are storing binary data (an image) so text encoding (UTF-8, UTF-16, etc) should be irrelevant here and you should certainly not apply conversion like when you use `iconv`. The database should store the content as pure binary without touching it at all (I know nothing about MSSQL but the generic idea of mixing pure binary data with textual encoding schemes looked very strange to me). — Patrick Mevzek, Sep 04 '18 at 16:57
@PatrickMevzek you're absolutely right. This seems to go wrong where Laravel uses PDO. — martintern, Sep 07 '18 at 10:18

Zhorov · Answer 1 · 2018-09-05T15:03:31.890

This simple example demonstrates how to insert image into varbinary(max) column in SQL Server and then get the image back and save it to disk. Example uses PHP Driver for SQL Server. In your case it's good to use parameterized queries.

Table:

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

# Insert image using CONVERT()
$image = file_get_contents('image.jpg');
$sql = "INSERT INTO ImageTable (ImageData) VALUES (CONVERT(varbinary(max), ?))";
$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;
}

# Insert image using SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY)
$image = file_get_contents('image.jpg');
$sql = "INSERT INTO ImageTable (ImageData) VALUES (?)";
$params = array(
    array($image, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY))
);
if ($stmt === false) {
    echo "Error insert (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Get image from first row and save it again on disk. 
$sql = "SELECT ImageData FROM ImageTable";
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
if (sqlsrv_fetch($stmt)) {
    $image = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
    file_put_contents('image2.jpg', $image);
}

# End
echo 'Image inserted.'
?>

Using your #Insert image part (I use Laravel for the #Connection) with separate parameters, the error message i receive changes to: `PDOException: SQLSTATE[IMSSP]: An error occurred translating string for input param 2 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page`. So that's still the same problem I think? (`CAST` or `CONVERT` doesn't make a difference..) — martintern, Sep 05 '18 at 12:24
@martintern How exactly looks your code? Can you post the part that gives errors in question or as comment? Thanks. — Zhorov, Sep 05 '18 at 12:30
@martintern If you don't use parameters and try to put the content of PHP variable into SQL statement ($data, $content), then this can be a problem. Updated answer. — Zhorov, Sep 05 '18 at 15:04
My code looked like `\DB::statement($query)`, using Laravel's database connection method. And that is what seems to be the problem, because using your connection and just this sql `"UPDATE my_table SET PictureData = CONVERT(varbinary(max), ?) WHERE PictureCode = 124"` everything works:-D Now on to investigate where exactly the problem is but at l(e)ast I can start saving my pictures so thank you so much! — martintern, Sep 06 '18 at 09:23

score 0 · Answer 2 · answered Sep 14 '18 at 10:29

Laravel uses the PDO class to access the database. By default it interprets all attributes as strings, which leads to the conversion error. One way to fix that is to instruct PDO to expect binary using setAttribute():

$pdo = \DB::connection('my_conn')->getPdo();
$sql = "INSERT INTO my_table VALUES (CONVERT(varbinary(max), ?));"
$stmt = $pdo->prepare($sql);

$stmt->setAttribute(\PDO::SQLSRV_ATTR_ENCODING, \PDO::SQLSRV_ENCODING_BINARY);

$stmt->bindValue(1, file_get_contents($file);
$stmt->execute();

Thanks @zhorov for putting me on the right track. Just added this answer to use more of Laravel.

score -2 · Answer 3 · answered Sep 04 '18 at 16:59

-2

Just a thought.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" >

Also you could try executing this query right after connecting to the db :

"SET NAMES 'utf8'"

answered Sep 04 '18 at 16:59

Dragon Kyn

94
1
8

`"SET NAMES 'utf8'"` produces this error: `[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]'NAMES' is not a recognized SET option.` Could this be a MySql command instead of MSSQL? – martintern Sep 06 '18 at 11:53

save image in mssql database as varbinary(max) without conversion

3 Answers3

Linked