Description: ------------ when inserting a string that has a trailing backslash followed by a new line (either CR or LF or BOTH) the backslash and the new line is stripped by SQL Server. Instead an EXTRA check needs to be made to escape the trailing backslash with another backslash. (ie.. C:\\ instead of C:\) but ONLY if it appears at the end of a line. escaping backslashes anywhere else in the input will cause the backslash to be added twice. Documentation: http://support.microsoft.com/kb/164291 (NOTE that the document is slightly incorrect in that you do NOT replace \<cr><lf> with \\<cr><lf><cr><lf> Only once set of new line sequences is needed.. Just simply added an extra \ before the trailing \ is all that is needed.) Test script: --------------- $db = MDB2::factory('mssql://server:1433/database'); $db->query("Create table #temp (test TEXT)"); $db->query("INSERT INTO #temp VALUES (".$db->quote("Test C:\\\nhello").")"); echo $db->queryOne("SELECT * FROM #temp"); $db->query("DROP TABLE #temp"); Expected result: ---------------- echoing C:\ Hello Actual result: -------------- echoing C:Hello

Ok, I was wrong... You do need the second set of CR/LR. as it still "strips" it.. it's not a true escape where SQL Server doesn't strip it. :( So basically something along the lines of this will cover most cases. $sql_escaped = str_replace( array("'","\\\r\n","\\\n"), array("''","\\\\\r\n\r\n","\\\\\n\n"), $sql); note this doesn't handle the case of a backslash followed by a \r.. Though I'm not sure of SQL server would even accept that as a new line. Also note that I kept the type of newline consistent in the replace, otherwise the size of the text would change, which is how I ran into this in the first place as it broke a PHP serialized blob.

I'm dizzy reading that. Want to write some code and attach some patches which make it clearer?

The following patch has been added/updated: Patch Name: MDB2-escape.patch Revision: 1243108560 URL: http://pear.php.net/bugs/patch-display.php?bug=16118&patch=MDB2-escape.patch&revision=1243108560&display=1

hehe.. yeah sure.. Basically SQL server does "line folding" when it sees a backslash at the end of a line. Like what BASH does. So basically the solution is to detect the backslash - newline sequence and add an extra backslash in there (as one will be eaten by the line folding) The patch I attached uses a REGEX to check for all line ending cases (\r\n, \r, and \n) you can use a str_replace for speed if you ignore the \r case, which is very unlikely to be used, and not even sure if SQL SERver even accepts that.

What SQL Server version are you using? From the KB page you linked, this problem is only affecting * Microsoft SQL Server 6.5 Standard Edition * Microsoft SQL Server 7.0 Standard Edition * Microsoft SQL Server 2000 Standard Edition Can you confirm this?

It actually affects 2005 and up as well.. Most likely they just didn't update the KB article reflecting that. But the documentation states that's how things are escaped as well. The testing I was doing was all done on a 2005 server with a database IN 2005 mode (compatibility level 90) I currently do not have a 2008 server installed, but I would assume it's the same in 2008. Yup here is the manual entry for 2008. http://msdn.microsoft.com/en-us/library/dd207007.aspx which that actually states is a functionality of the sqlcmd or osql.. Meaning the backslash issue could really have to do with dblib.. so it would affect Sybase as well. Yup seems so.. From looking through the freetds source code it looks like a protocol of the dblib with the mssql php extension uses.

This bug has been fixed in SVN. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.