Strip Special Characters Directly from MySQL

Posted on 2007-09-29
Last Modified: 2013-12-13
Hello is there a query to find and strip all special characters from my database?  I made a backup and want to see what happens.  I'm having major issues w/ my site and I will deal w/ the Descriptions/Titles that get messed up from this.  


$sql = "UPDATE Products SET  "(Special Character)" = '";

Thanks for your time...
Question by:smoothcat11
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you will need to update table per table, and specify the expression per column:

    $sql = "UPDATE Products SET yourfield = REPLACE ( yourfield, 'special character' = '') ";
    LVL 27

    Accepted Solution

    This is not done directly in MySQL, but if you have PHP available, you can put the problem fields through the php function htmlentities().  This will convert all special characters to their html & code equivalents.  

    This will keep the special characters in your titles and descriptions, at the cost of a few extra bytes.  It means they will display/print correctly, but not risk any malicious code.

    //connect to database
    $result = mysql_query("SELECT Key, ProblemField FROM Table");
    while($row = mysql_fetch_array($result))
        $key = $row["Key"];
        $newvalue = htmlentitles( $row["ProblemField"], ENT_QUOTES);
        mysql_query("UPDATE Table SET ProblemField='$newvalue' WHERE Key='$key' ")

    Thereafter, when you enter something into your database, just pass it through htmlentities() first.

    $safestring = htmlentities($inputstring, ENT_QUOTES);

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    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.

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now