PHP PDO load image into MS SQL Server 2012

Question

I'm trying to upload image into SQL Server using PDO with the same code as I do it into MySQL with no success.

I thought it will be simple as in MySQL but Microsoft makes sure I'll suffer.

the error I'm receiving is: SQLSTATE[IMSSP]: An error occurred translating string for input param 3 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

I've create this small runnable example (2 files 'upload.php' and 'test.html'). In order to use MySQL the parameter should be $db = 'mysql'

php file content named 'upload.php':

<?php

$db = 'mssql'; // $db = 'mysql';

$config = array(
    'mysql' => array(
        'dsn'       => 'mysql:host=localhost;dbname=',
        'user'      => 'root',
        'password'  => ''
    ),

    'mssql' => array(
        'dsn'       => 'sqlsrv:Server=localhost;Database=',
        'user'      => '',
        'password'  => ''
    )
);

try {
    // connect to database
    $pdoTest = new PDO($config[$db]['dsn'].'test', $config[$db]['user'], $config[$db]['password']);
    $pdoTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // insert image to table (with additional some info)
    $queryIn = $pdoTest->prepare("
        INSERT INTO     tbl_image (username, type, image)
        VALUES          (:username, :type, :image);
    ");
    $queryIn->bindValue(':username',  $_POST['username']);
    $queryIn->bindValue(':type',      $_FILES['image']['type']);
    $queryIn->bindValue(':image',     file_get_contents($_FILES['image']['tmp_name']));
    $queryIn->execute();

    // retrieve image and type from table
    $queryOut = $pdoTest->prepare("
        SELECT          type, image
        FROM            tbl_image
        WHERE           username = :username
    ");
    $queryOut->bindValue(':username',  $_POST['username']);
    $queryOut->execute();
    $row = $queryOut->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        // send image back to browser
        header('content-type: '.$row['type']);
        echo $row['image'];
    }
}
catch (PDOException $e) {
    echo $e->getMessage();
}

html file content named 'test.html':

<!DOCTYPE html>
<html class="no-overflow">
    <head>
        <meta charset="UTF-8">

        <title>Load image to SQL Server 2012</title>
    </head>

    <body>
        <form enctype="multipart/form-data" action="upload.php" method="POST">
            <input type="text" name="username" autofocus="">
            <input type="file" name="image" accept="image/jpeg">
            <input type="submit" name="submit">
        </form>
    </body>
</html>

MySQL definition:

database name     'test'
table name        'tbl_image'
field #1          'username varchar(255)'
field #2          'type varchar(255)'
field #3          'image blob'

SQL Server 2012 definition:

database name     'test'
table name        'tbl_image'
field #1          'username nvarchar(255)'
field #2          'type nvarchar(255)'
field #3          'image varbinary(MAX)'

I also tried some older examples for SQL Server with no success:

$datastring = file_get_contents($_FILES['image']['tmp_name']);
$data       = unpack("H*hex", $datastring);
$image      = '0x'.$data;

Any idea?

score 6 · Accepted Answer · answered Sep 25 '13 at 13:04

Hi this is the solution code for mssql

<?php

$db = 'mssql';

$config = array(
    'mysql' => array(
        'dsn'       => 'mysql:host=localhost;dbname=',
        'user'      => 'root',
        'password'  => ''
    ),

    'mssql' => array(
        'dsn'       => 'sqlsrv:Server=localhost;Database=',
        'user'      => '',
        'password'  => ''
    )
);

try {
    // connect to database
    $pdoTest = new PDO($config[$db]['dsn'].'test', $config[$db]['user'], $config[$db]['password']);
    $pdoTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // insert image to table (with additional some info)
    $queryIn = $pdoTest->prepare("
        INSERT INTO     tbl_image (username, type, image)
        VALUES          (:username, :type, :image);
    ");
    $queryIn->bindParam(':username',  $_POST['username']);
    $queryIn->bindParam(':type',      $_FILES['image']['type']);
    $queryIn->bindParam(':image',     file_get_contents($_FILES['image']['tmp_name']), PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
    $queryIn->execute();

    // retrieve image and type from table
    $queryOut = $pdoTest->prepare("
        SELECT          type, image
        FROM            tbl_image
        WHERE           username = :username
    ");
    $queryOut->bindParam(':username',  $_POST['username']);
    $queryOut->execute();
    $queryOut->bindColumn(2, $image, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
    $row = $queryOut->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        // send image back to browser
        header('content-type: '.$row['type']);
        echo $row['image'];
    }
}
catch (PDOException $e) {
    echo $e->getMessage();
}

This only works for windows, as the `PDO::SQLSRV_ENCODING_BINARY` constant is only available in sqlsrv driver, not the dblib driver. Still looking for a solution in unix... — Steven M, Sep 19 '16 at 19:48

score 0 · Answer 2 · answered Jul 03 '17 at 16:44

After having implemented teach me's working solution I stumbled onto another using PHP's sqlsrv_connect and sqlsrv_query.

    $serverName = "<your_server_name>";
    $dbName = "<your_database>";
    $multipleActiveResultSets = "True";

    // Since UID and PWD are not specified in the $connectionInfo array,
    // The connection will be attempted using Windows Authentication.
    $connectionInfo = array( 
        "Database"=> $dbName, 
        "MultipleActiveResultSets"=> $multipleActiveResultSets        
        );

    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    $query = "INSERT INTO <your_table> (image) VALUES (?)";

    $stmt = sqlsrv_query( $conn, $query, [file_get_contents($_FILES['image']['tmp_name']]);

    if( $stmt === false ) {
        die( print_r( sqlsrv_errors(), true));
    }

PHP PDO load image into MS SQL Server 2012

2 Answers2

Linked