Solved

mysqli::real_escape_string creates \r\n problems?

Posted on 2014-01-08
9
1,596 Views
Last Modified: 2014-01-09
I have a form where the users use TinyMCE to fill out some information, and that form is then POSTed, and stored in a database.
The problem appears to be that mysqli::real_escape_string is turning carriage return line feeds into escaped versions of themselves, and storing them. Well... that is part of the design. But when I re-read the database, and run stripslashes() on the data as it is read from the DB, I get output like this:

     Here si some good HTML code
     rn
     But you can see
     rn
     that there are annoying
     rn
     "rn" on blank lines between everything.

How can I store HTML in the DB, and retrieve it without having this \r\n problem?
0
Comment
Question by:DrDamnit
  • 5
  • 3
9 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 39767156
You can use htmlentities() function to change all the special characters to their HTML & code equivalents.  This will store the codes rather than the special characters themselves.

It is just as safe (in my opinion, safer) than mysql_real_escape_string() and will print exactly the same.

http://us1.php.net/manual/en/function.htmlentities.php  is the function documentation.

http://www.ascii.cl/htmlcodes.htm  shows you the HTML codes.

The only drawback is that it will take 4-5 bytes in your database to store each special character.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39767175
OK. I did that.

I am now using $b = htmlentities($b)

Then storing that in the DB as it is rendered with htmlentities().

When I retrieve it from the DB, I do:

$output = $html_entity_decode($b);

Then:
echo $output.

Here's what I get:
screenshot
It's printing the literal HTML on the screen. (I am not using <pre>).
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39767177
Expected Behavior / Actual Behavior / Steps to reproduce are already in the page (This is a our bug tracker).
The code beneath those heading is getting echo'd out.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39767240
I find all of that very confusing.  First, the mysqli::real_escape_string function escapes the binary values of '\r' and '\n', not the text representation.

Why are you running 'stripslashes' when you did not run 'addslashes'?  When you use mysqli::real_escape_string function, you are supposed to receive the un-escaped version back from the query.

Why are you sending '\r' and '\n' to an HTML page that does not recognize them as control codes?  A PHP or javascript string function will recognize them but HTML won't.

http://us1.php.net/manual/en/mysqli.real-escape-string.php
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39767296
@Dave

I am just as confused as you are.

This is some legacy code that I wrote nearly 10 years ago.

I *think* at the time, I was using mysql_escape_real_string() to prevent sql injection attacks.

The problem now is that I get decent HTML from TinyMCE POSTed to the script, it is loaded into a class that handles this. The class uses mysqli::real_escape_string() prior to storing the data int he DB.

stripslashes is (I guess) supposed to reverse what mysqli::real_escape_string is doing. But from the tone of your comment (as well as the question, "why are you using stip slashes when you didn't use addslashes), it is wholly unneeded?
0
 
LVL 32

Author Closing Comment

by:DrDamnit
ID: 39767330
Your comment got me going in the right direction. I ended up using addslashes and stripslashes.

the htmlentities() suggestion did not work because oddball characters were causing some of the SQL queries to fail.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39767345
Glad you got it sorted out.  Thanks for the points.

You might try a version without addslashes and stripslashes.  I don't think you need them.  mysqli::real_escape_string is not supposed to need reversing, it is supposed to 'automatically' be taken care of by the 'mysqli' driver.  I know that I'm not adding anything to my code to do that.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39769347
@Dave:

I tried it both ways, and while i have not put the data from the POST into a file so I can look at it in a hex editor to see what's actually going, real_escape_string was definately adding the \r\n when inserting HTML from TinyMCE into the DB. It was clear as day in the column when I look at it directly in MySQL workbench.

What's interesting, is that this code has been in use and production for a decade in hundreds of applications. (It's part of a code generator we use to abstract databases), and it has worked flawlessly for 10 years.

It must have something to do with how TinyMCE is interacting with it.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39769384
"real_escape_string" is supposed to add those escape sequences on INSERT... and translate them back to the original characters on SELECT.  So yes, you should see them in the database when using "real_escape_string" before INSERT.  

TinyMCE could be messing up the sequence if it is trying to recode or escape the characters itself before the "real_escape_string".
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question