MSSQL and inserting binary files

[luthien]

I'm trying to store a binary file (picture) using a ODBC-connection to MS SQL Server.

$fp=fopen($bin, 'r');
$contents = fread ($fp, $bin_size);
$query_string="update TABLE ";
$query_string.="set bin='$contents' ";
$query_string.="where ID=1 ";

The problem consists of the fact that I cannot use addslashes() on MSSQL. I've tried str_replace("'", "''", $contents); but to no avail.

The error message I get is as follows:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string 'ÿØÿà'., SQL state 37000 in SQLExecDirect

The field in the database is 'binary'. I've tried 'varbinary' as well.

Is there anyone out there that can help me with this problem?

Yours, Jan Aage Eriksen

[[deleted]]

Why can't you use addslashes?

[luthien]

vincent wrote:

Why can't you use addslashes?

addslashes() adds slashes.

Microsoft SQL Server use a single quote instead. (inherited from Sybase)

Yours, Jan Aage Eriksen

[[deleted]]

Hmm.. well, addslashes does more than just add slashes before quotes, it also adds slashes before any other characters that might be interpreted as "end of query".
So your string replacement command does only half of the work.

[luthien]

vincent wrote:

So your string replacement command does only half of the work.

Hmm...didn't think of that. :-(

Do you have any suggestions as to what I should do to put the single quote in front of NUL (the null byte)?

Yours, Jan Aage Eriksen

[luthien]

$query_string="update IMAGE_TREE ";
$query_string.="set Name='$title', ";
$query_string.="Description='$description'";
if(is_uploaded_file($bin)){
$fp=fopen($bin, 'r');
$contents = fread ($fp, $bin_size);
$contents = str_replace("'", "''", $contents);
$contents = str_replace("\"", "'\"", $contents);
$contents = str_replace("\", "'\", $contents);
$contents = str_replace(chr(0), "'".chr(0), $contents);
$query_string.=", ";
$query_string.="bintype='$bin_type', ";
$query_string.="bin=CONVERT(varbinary, '$contents') ";
} else {
$query_string.=" ";
}
$query_string.="where ID=$edit_id ";
$query_update=odbc_exec($connection, $query_string);

---

And I still get the error message:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'ÿØÿà'., SQL state 37000 in SQLExecDirect

---

I have to use convert on $contents, as it thinks it a varchar.

Yours, Jan Aage Eriksen

[Anon]

Hi,

I got the same problem and after a lot of search i got answer in this forum but when i tried to insert i am getting errors like ACCESS VIOLATION... why whats wrong with me ?

Jan Aage Eriksen wrote:

$query_string="update IMAGE_TREE ";
$query_string.="set Name='$title', ";
$query_string.="Description='$description'";
if(is_uploaded_file($bin)){
$fp=fopen($bin, 'r');
$contents = fread ($fp, $bin_size);
$contents = str_replace("'", "''", $contents);
$contents = str_replace("\"", "'\"", $contents);
$contents = str_replace("\", "'\", $contents);
$contents = str_replace(chr(0), "'".chr(0), $contents);
$query_string.=", ";
$query_string.="bintype='$bin_type', ";
$query_string.="bin=CONVERT(varbinary, '$contents') ";
} else {
$query_string.=" ";
}
$query_string.="where ID=$edit_id ";
$query_update=odbc_exec($connection, $query_string);

---

And I still get the error message:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'ÿØÿà'., SQL state 37000 in SQLExecDirect

---

I have to use convert on $contents, as it thinks it a varchar.

Yours, Jan Aage Eriksen

[Anon]

Did you ever get help with the issue of adding binary data to MSSQL? I also had that problem and was not able to resolve it. Thanks.