Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Script to convert special characters to html entities?

Posted on 2011-09-28
6
Medium Priority
?
507 Views
Last Modified: 2012-06-21
Hi All,

Historically I've added content to my database without converting special characters to html entities. How would I go about converting these special characters to html entities thoughout the whole database? Would I need to write a script that loops through each table and then each row to replace the characters in question or is there an easier way.

If a script is required please could you show an example of how to loop between the tables and rows because the loop will need to use and index or reference of some sort to handle the tables without using there saved names.  

Thank you
0
Comment
Question by:Mulith
  • 3
  • 2
6 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36716715
I would not do this if I were you.  Instead, use htmlentities() in the display layer.  There are many reasons why you do not want to mung your data base.  One of them is that the htmlentities() function increases the length of the strings.  Thus there is a risk that you will cut the data off when you try to put it back into your tables.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36716783
But if you are unconvinced and want to mung your data base, here are the general ideas you will need to implement.

SHOW TABLES to get the list of tables, then an iterator to access each table
SHOW COLUMNS FROM the list of tables to get "Field" and "Type" which are the names and definitions of the columns, then an iterator to access each column
SELECT the data in the column
Apply htmlentities() to the data
Compare the strlen() value of the new data to the length given in the "Type"
Issue a warning message if the new data is longer than the length of the column.

Once you are satisfied that you will not cut off your strings (or that the cutoffs do not matter) you can add the UPDATE statements to replace the contents of the data base tables.  You probably want to make a back up, because htmlentities() cannot be run twice on the same string.  You probably want to LOCK TABLES during the pervasive update.
0
 
LVL 1

Author Comment

by:Mulith
ID: 36717162
The problem ray is that when I try moving the content via mysql dump in SSH to another server I get a question mark where speacial characters used to appear.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:nemws1
ID: 36717693
That shouldn't be happening (MySQL is pretty smart about handing all sorts of types of data).

Sounds like a character encoding mismatch.  Ie.  You're table is using UTF-8 characters, but you're dumping out your data as LATIN-1 (or something similar).

When you look at the dump file (add --skip-extended-insert to your mysqldump command so you can see the rows more easily), are the characters already messed up, or do they look correct in the dump?
0
 
LVL 1

Accepted Solution

by:
Mulith earned 0 total points
ID: 36718208
Managed to sort it by adding --default-character-set=latin1 as advised on http://www.orthogonalthought.com/blog/index.php/2007/05/mysql-database-migration-and-special-characters/ 
0
 
LVL 1

Author Closing Comment

by:Mulith
ID: 36902259
Did a bit of reading and found this solution
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

580 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