Solved

mysqli::real_escape_string creates \r\n problems?

Posted on 2014-01-08
9
1,488 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now