Issues Inserting Files into MSSQL - solved

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Issues Inserting Files into MSSQL - solved

Post by Bigun »

I've written a function to close single quotes:

Code: Select all

function mssql_escape_string($string_to_escape) //Cleans URL variables for MSSQL use
{
    $replaced_string = str_replace("'","''",$string_to_escape);
    return $replaced_string;
}
And I've opened the file cleansed it with that function:

Code: Select all

//Open and sanitize file
    $fp = fopen($filename, 'r');
    $content = fread($fp, filesize($filename));
    $content = mssql_escape_string($content);
Then I try to insert it:

Code: Select all

//Form query
    $query = "INSERT INTO Images(Filecontent, AlternateText, ReleaseID) VALUES('$content', '$filetitle', '$releaseid')";
    
    mssql_query($query);
And I get this error:

Code: Select all

Warning: mssql_query(): message: Unclosed quotation mark after the character string 'ÿØÿà'. (severity 15) in D:\Inetpub\wwwroot\managepressreleases\functions.php on line 413 Warning: mssql_query(): message: Incorrect syntax near 'ÿØÿà'. (severity 15) in D:\Inetpub\wwwroot\managepressreleases\functions.php on line 413 Warning: mssql_query(): Query failed in D:\Inetpub\wwwroot\managepressreleases\functions.php on line 413
I have been googling for a good hour and cannot come up with any results. Can you guys help me? I'm at my wit's end.

*EDIT*

And please don't suggest saving the file in another location instead of inserting into a database, I have my reasons for doing this. Thank you.
Last edited by Bigun on Thu Feb 18, 2010 9:53 am, edited 1 time in total.
lshaw
Forum Commoner
Posts: 69
Joined: Mon Apr 20, 2009 3:40 pm
Location: United Kingdom

Re: Issues Inserting Files into MSSQL

Post by lshaw »

You are not actually escaping the '?

why not replace ' with \' ?
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

Wouldn't addslashes() do the same thing?
lshaw
Forum Commoner
Posts: 69
Joined: Mon Apr 20, 2009 3:40 pm
Location: United Kingdom

Re: Issues Inserting Files into MSSQL

Post by lshaw »

Yes, so why do you need to replace ' with ' ' ?
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

Very odd.... I tried saving the query to a file:

Code: Select all

$query = "INSERT INTO Images(Filecontent, AlternateText, ReleaseID) VALUES('$content', '$filetitle', '$releaseid')";
    $fp = fopen('C:\Program Files\PHP\uploaddir\query.sql', 'w');
    fwrite($fp, $query);
    fclose($fp);
    
    mssql_query($query);
I then opened the file and tried copying it into the MS-SQL Manager Query Window, and it only pasted this:

Code: Select all

INSERT INTO Images(Filecontent, AlternateText, ReleaseID) VALUES('ÿØÿà
 
 
 
 
 
 
 
Almost like it ignored the rest of what I had copied.

I have attached the file in question.

*edit*

What extensions are allowed? I've tried sql, txt, php, and no extension at all.
Last edited by Bigun on Thu Feb 18, 2010 9:25 am, edited 1 time in total.
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

lshaw wrote:Yes, so why do you need to replace ' with ' ' ?
Because of MSSQL syntax
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

I've tried addslashes before and it came up with a massive error, I'll pastebin it:

http://pastebin.com/f4503736
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

Ok... now we might be getting somewhere:

I'll post a snippet of the query after using addslashes():

Code: Select all

INSERT INTO Images(Filecontent, AlternateText, ReleaseID) VALUES('ÿØÿà\0JFIF\0\0`\0`\0\0ÿá\0Exif\0\0II*\0\0\0\0\0\0\0\0\0\0ÿÛ\0C\0ÿÛ\0CÿÀ\0w,\"\0ÿÄ\0\0\0\0\0\0\0\0\0\0\0\0\0\0  ÿÄ\08\0!\0\"12#ABQ3Raq$SbrC‚‘’ÿÄ\0\0\0\0\0\0\0\0\0\0\0\0 ÿÄ\0=\0\0!1\0A\"Qa2q#B‘¡ÁRbr±Ñáð3ñC‚’¢ÿÚ\0\0\0?\0mŠ*>É“Ø*(4¢É#ò ÅÙëPºÕrËÁoT7ÃÐ$«( ¨£cë¯IÄ…ƒl»ª’Ib¨³µÙ\'âê¹ë\0…PÕVÕaiÔl»»%P0«RMõ ‘Ñ*3Ý   –.Er׊ã7Ö6UGƒ1ñVf¹»Æ×¬ßØ$+¡ ÀÜ·³1䲩.Ï\'Ê>Ûì-goÉx³¹€«õ÷ýÑëRnX¬„Û„+nBÒP)À&ÅñìXŽ½Ê¯!IüyeZe]Uo‚s_ëŽMÝ´Œ’$„Æ©(}¦N@¥®qñÂÜcMnÛ|Þ¯†¹\\t›¶÷tu偨‚QÇÓRÒ°: ¨žI¬m}”[j&ÔòÄ-Še+«
\0Ù¯êÎKÙbE\";#oÆí‚X˜è@÷ªþúË|‚©HTE©d4Õ°j ÈþÁ$tæ ‰,†VýÌöî\"Ž@¢£CŽ–55ܲ‹q®kkà6È•~­ßX?lÊ8ÑT C9¾xþ‚ý–ÿ\0à†
Xö·%~‰*ªikÕ‰_$Ÿ¾°a™X¹÷.xEµPºýUYµn\0kD’À–
¾6¦Ö˜€Bµþ@†-Å€BôÖA\\[“>?ïáãåéP€D÷6ÊRJÂO“?C¥  cÃ)$–bmê¨-.ÃА6]IfÇF;D« *iJ–$(°z$z^Ê_ªä4Á£õ&‹êHSDŽÄ\\Yþ‰þ:«ð)€,ä”Ô–Ð}µ—eõ¶V7hF¤Ý좙2¿¶è
!ñ¹å¾:[Â5w
{KûYž:MÅ™˜6‘dšä½7СÈ7|WZXºêê-E7”””¨`A,qd’?¡ï\"5ª’ŒmŽÃAøÙÿ\0ù31­~þºD2©P=~Å ¤%Þ¤p-µ&ïÚ¾ùé²cFW1¤¨((æþ×þ1e9IŒZ°VÙ¨Ù%ºýýºW×Rº8e’0€    $7\'×ò$ý’Ä“Ö謈”HúÈú°@¼‘JP–Wbb´\'îºjîà MÉ ,l
Ú»]½l¸æˆ}(§]…³f‰i^I
ûÿ\0t¦ý…¬7„n–1¬pÔn^|[ý:’ÖCÂ-¾Pæò;_†Ü9U:€wR@Öœlªáöà±_⢸*\0PBF=h[–àZ»<+e
lÒH$j-oBþ¡ÀSD‘+±ûUZNkŠï޶2ìŸÆßàÀ    ]û°¨1}¹cdè t“ŒâGc,1²è³    i«jÌx¬ôÔŽÒÈ«¥¬IUÅ\'Ü
 
Looks like the addslashes() takes care of every character except single-quotes. The next to last character is a single-quote, and according to MSSQL's query window, that's where it is failing.

Is there a list somewhere of what addslashes() looks for? I probably need to make a special function to handle inserting binary data into MSSQL.

*edit*

for some reason the board isn't showing this, but there is a backslash before the single-quote.
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL

Post by Bigun »

Got it...

I hope this will help other people who want to insert files into MSSQL with PHP.

I've written a function that will clean binary files for input into MSSQL:
function mssql_clean_file($filetoclean) //Clean file for MSSQL consumption
{
    $filetoclean = addslashes($filetoclean);
    $cleanedfile = str_replace("\'","''",$filetoclean);
    return $cleanedfile;
}
*edit*

Forgive the lack of code tags, but with the code tags, the backslashed/single-quote was not showing up.
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Issues Inserting Files into MSSQL - solved

Post by Bigun »

No, not solved... it did insert the data, but did not display. There's a trick I found by googling a little bit more:

Code: Select all

//Open and prepare file
    $fp = fopen($filename, 'r');
    $datastring = fread($fp, filesize($filename));
    $content = unpack("H*hex", $datastring);
Then when you insert it:

Code: Select all

$query = "INSERT INTO Images (AlternateText, ReleaseID, Filecontent) VALUES('$filetitle', '$releaseid', 0x".$content['hex'].")";
The field type in MSSQL is set to image. With all of that, the image is stored properly.

Now it's solved.

:banghead:
Post Reply