FAQ
Edit report at http://pear.php.net/bugs/bug.php?id=16117&edit=1

ID: 16117
Updated by: aharvey@php.net
Reported By: eddie at omegaware dot com
Summary: MSSQL escape doesn't take into account trailing
backslashes
-Status: Open
+Status: Closed
Type: Bug
Package: DB
Operating System: CentOS 5
Package Version: 1.7.13
PHP Version: 5.2.9
-Assigned To:
+Assigned To: aharvey
-Roadmap Versions:
+Roadmap Versions: 1.7.14RC2
New Comment:

-Status: Open
+Status: Closed
-Assigned To:
+Assigned To: aharvey
-Roadmap Versions:
+Roadmap Versions: 1.7.14RC2
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.




Previous Comments:
------------------------------------------------------------------------

[2009-04-20 22:21:42] urkle

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.

------------------------------------------------------------------------

[2009-04-17 20:58:13] urkle

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 = DB::connect("mssql://server:1433/database");
$db->query("CREATE TABLE #temp (f1 TEXT)");
$db->query("INSERT INTO #temp VALUES ("
.$db->quoteSmart("Test C:\\\nHello")
.")");
echo $db->getOne("SELECT f1 FROM #temp");
$db->query("DROP TABLE #temp");

Expected result:
----------------
echoing

C:\
Hello

Actual result:
--------------
echoing

C:Hello

------------------------------------------------------------------------

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedDec 24, '10 at 6:05a
activeDec 24, '10 at 6:05a
posts1
users1
websitepear.php.net

1 user in discussion

Aharvey: 1 post

People

Translate

site design / logo © 2022 Grokbase