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

ID: 16118
Updated by: l.alberton@quipo.it
Reported By: eddie at omegaware dot com
Summary: MSSQL escape doesn't take into account trailing
backslashes
Status: Open
Type: Bug
Package: MDB2
Operating System: CentOS 5
Package Version: 2.4.1
PHP Version: 5.2.9
Roadmap Versions:
New Comment:

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?


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

[2009-05-23 20:57:27] urkle

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.

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

[2009-05-23 20:56:00] urkle

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

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

[2009-05-23 19:28:44] doconnor

I'm dizzy reading that.

Want to write some code and attach some patches which make it clearer?

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

[2009-04-20 22:21:29] 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 21:07:14] 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 = 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

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

Search Discussions

  • Urkle at Sep 27, 2009 at 6:51 pm
    Edit report at http://pear.php.net/bugs/bug.php?id=16118&edit=1

    ID: 16118
    Updated by: urkle@outoforder.cc
    Reported By: eddie at omegaware dot com
    Summary: MSSQL escape doesn't take into account trailing
    backslashes
    Status: Open
    Type: Bug
    Package: MDB2
    Operating System: CentOS 5
    Package Version: 2.4.1
    PHP Version: 5.2.9
    Roadmap Versions:
    New Comment:

    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.


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

    [2009-09-27 11:13:50] quipo

    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?

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

    [2009-05-23 20:57:27] urkle

    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.

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

    [2009-05-23 20:56:00] urkle

    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

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

    [2009-05-23 19:28:44] doconnor

    I'm dizzy reading that.

    Want to write some code and attach some patches which make it clearer?

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

    [2009-04-20 22:21:29] 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.

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

    The remainder of the comments for this report are too long. To view
    the rest of the comments, please view the bug report online at
    http://pear.php.net/bugs/bug.php?id=16118

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedSep 27, '09 at 9:14a
activeSep 27, '09 at 6:51p
posts2
users2
websitepear.php.net

2 users in discussion

Urkle: 1 post L Alberton: 1 post

People

Translate

site design / logo © 2022 Grokbase