title: Saving files to varbinary(max) field?
body: <p>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:</p>
  
  <p>I created this table:</p>
  
  <pre><code>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)
     );
  </code></pre>
  
  <p>And then I try to add content like this:</p>
  
  <pre><code>$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.'")';
  </code></pre>
  
  <p>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):</p>
  
  <blockquote>
    <p>[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 '����'.</p>
  </blockquote>
  
  <p>Now I figure this is a problem with the content of the file breaking the SQL but I have <strong>NO CLUE</strong> 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.</p>
  
  <p>(Yes I know I haven't protected myself from attacks here, but right now I'm just trying to get the basics to work)</p>