Saving files to varbinary(max) field?

Question

I've been at this all day and I just can't seem to figure it out. Our client wants us to save their uploaded files into a table in our database (mssql). There is no restriction as to what kind of files they could upload so following the limited knowledge that I had I did a bit of googling and tried the following:

I created this table:

CREATE TABLE files
  (
    id int NOT NULL PRIMARY KEY IDENTITY,
    name varchar(256),
    content varbinary(max) NOT NULL,
    type varchar(30) NOT NULL,
    size int NOT NULL
    table_id INT NOT NULL FOREIGN KEY REFERENCES myTable(id)
   );

And then I try to add content like this:

$newfileName = $_FILES['uploadfile']['name'];
$newtmpName  = $_FILES['uploadfile']['tmp_name'];
$newfileSize = $_FILES['uploadfile']['size'];
$newfileType = $_FILES['uploadfile']['type'];

//need to get the content of the file
$fp = fopen($newtmpName, 'r');
$file_content = fread($fp, filesize($newtmpName));
$file_content = $file_content;
fclose($fp);

$sql = 'INSERT INTO files ([name], [content], [type], [size], [table_id]) VALUES ("'.$newfileName.'",CAST("'.$file_content.'" AS varbinary(max)),"'.$newfileType.'","'.$newfileSize.'","'.$table_id.'")';

but it just doesn't work... I can upload txt files no problem but anything else will just break. I get errors like the following (which are different with every file):

[42000][105] [Microsoft][SQL Server Native Client 10.0][SQL Server]Unclosed quotation mark after the character string '��'. [42000][102] [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '��'.

Now I figure this is a problem with the content of the file breaking the SQL but I have NO CLUE how to deal with them (or how to convert them into a string that won't break the sql). Any help would be greatly appreciated as I am completely lost with this.

(Yes I know I haven't protected myself from attacks here, but right now I'm just trying to get the basics to work)

@Hamish: I'm using the sqlsrv driver. @jnpcl: That's something I forgot to remove (it's not in my code right now). I was basically just experimenting since a lot of my errors had to do with quotes so I wanted to see if it would solve the problem. — Gazillion, Feb 16 '11 at 20:15
and you'll spend another week trying to show it on a web page — Your Common Sense, Feb 16 '11 at 20:17
@Col. Shrapnel: I had already created the script to download the files once they're saved to DB and after having encoded the binary data, like Hamish mentioned below, I was able to download them no problem. I figure some bugs might come up but so far so good :) — Gazillion, Feb 16 '11 at 20:19

score 2 · Answer 1 · answered Jun 14 '12 at 13:42

In my case with Microsofts sqlsrv-Driver by far the easiest way was working with params! That avoids using quoting in your binary data and it gets into the database just as it...

Code-Snippet:

$sql = "INSERT INTO tablename (binaryImageField) VALUES (CAST (? AS varbinary(max)))";
$params = array($binaryImageData);
sqlsrv_query($con,$sql,$params);

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

2

Have a look at: How to escape strings in SQL Server using PHP?

edited May 23 '17 at 10:31

Community

1
1

answered Feb 16 '11 at 20:13

Hamish

22,860
8
53
67

Thank you! That's what I was looking for. You have no clue how long I've been hacking at this, working with files isn't my forte :P I will make this into the right answer as soon as the site lets me. – Gazillion Feb 16 '11 at 20:17

score 0 · Answer 3 · answered Feb 16 '11 at 20:16

0

Try using fopen's binary flag (b):

$fp = fopen($newtmpName, 'rb');
$file_content = fread($fp, filesize($newtmpName));

Also, I'm not sure you should addslashes your data. I never worked with MSSQL in PHP, so I can't tell.

answered Feb 16 '11 at 20:16

netcoder

66,435
19
125
142

Yeah addslashes was old code that I forgot to remove from the example that isn't in there right now. I'll edit my question to remove in case it can help someone out. Just out of curiosity I added the binary flag and it didn't do the trick. Maybe there was other stuff to do but since my problem is now fixed I'm going to let it rest and move on to bigger fish :) – Gazillion Feb 16 '11 at 20:22

Saving files to varbinary(max) field?

3 Answers3