Insert binary data into SQL Server using PHP

Question

I have a varbinary(MAX) field in a SQL Server 2005 database. I'm trying to figure out how to insert binary data (ie. an image) into that field using PHP. I'm using ODBC for the connection to the SQL Server database. I have seen a number of examples that explain this for use with a MySql database but I have not been able to get it to work with SQL Server. Thanks.

Permana · Answer 1 · 2015-11-11T01:07:02.183

20

function prepareImageDBString($filepath)
{
    $out = 'null';
    $handle = @fopen($filepath, 'rb');
    if ($handle)
    {
        $content = @fread($handle, filesize($filepath));
        $content = bin2hex($content);
        @fclose($handle);
        $out = "0x".$content;
    }
    return $out;
}

usage:

$out = prepareImageDBString('/img/myimg.png');
mssql_query("INSERT INTO MyTable(MyImage) VALUES($out) ");

MyImage is SQL Server field where the type is image

edited Nov 11 '15 at 01:07

answered Apr 04 '11 at 03:37

Permana

1,972
2
33
51

1

Definite +1. This should be choosen answear. There are many cases, when using Blob is justified (easier to backup 1 database file, instead of 10 milions of really small files for example). Just one remark - function lacks return at the end. – baron_bartek Nov 10 '15 at 12:20

score 3 · Answer 2 · edited May 23 '17 at 10:31

3

I won't say it's a bad practice, it depends on how big is the image and how your application use it.

If file below 256K in size, store in db is more efficient; More than 1 mb, store in file-system is recommended.

Storing images in SQL Server?

edited May 23 '17 at 10:31

Community

1
1

answered Apr 08 '14 at 07:48

KingBowen

240
3
9

TravisO · Accepted Answer · 2017-05-02T20:10:50.080

-4

The simple answer is: stop what you're doing.

You don't want to store binary files inside a database unless you have some very specific security issues. Instead you want to store their filenames (possibly rename the files to prevent confliction) and then store that in the database. If security is an issue, then put them in a non web folder and use your code to retrieve the file and only serve the file if the user has access to it.

Storing images or files inside a database is a waste of file space, a mis-use of databases, and not letting things do what they do best; the filesystem knows files, the database knows data.

edited May 02 '17 at 20:10

answered Mar 27 '09 at 17:24

TravisO

9,406
4
36
44

what about a gzdeflated text string? binary compressed text data seems right at home in a database. – chiliNUT Apr 21 '14 at 15:36
9

He didn't ask IF he should - he asked HOW he can do it. You should answer the OP's question - then - if you must, add your thoughts/opinions. – Losbear Apr 26 '17 at 16:15
3

While this is good advice, it doe snot help the people who google similar issues looking for a solution. Not all binary data is an image or file. – dmarra Apr 27 '17 at 21:10
2

It doesn't answer the question asked. People often work with legacy databases where things already stored as binary data. – Vedmant Jun 14 '17 at 10:38
3

This should not be the accepted answer, this does not answer the question. There are plenty of reasons to store data in a database as opposed to a file system. This "answer" does not answer the question. Please refer to Permana's answer if you come across this question in the future. – eragon2262 Oct 15 '19 at 21:22

Insert binary data into SQL Server using PHP

3 Answers3

Linked