[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1805
  • Last Modified:

Strip Special Characters Directly from MySQL

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.  

EG:

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

Thanks for your time...
0
smoothcat11
Asked:
smoothcat11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to update table per table, and specify the expression per column:

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

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
0
 
Cornelia YoderArtistCommented:
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);

http://us2.php.net/manual/en/function.htmlentities.php

http://www.ascii.cl/htmlcodes.htm
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now