PHP mySQL Recursive Replace String (Entire DB) + Sanitizing User Input
I've been working on a store front website and a CMS (Content Management System) that I created for a client and I learned the hard way that I should have incorporated some sort of input sanitation (oops!). Long story short, I thought it would best to leave direct HTML input enabled in my CMS to facilitate stuff like lists, line breaks, images, etc (::Face-Palm::). However I was still really noobish and didn't consider what those little &'s and whatnot would do to the actual HTML. Since the client had already entered a lot of data for her products, starting over was not an option. The Solution: Build a PHP script that would recursively replace every instance of the offending character. Please note that I made some changes to the the following script in an effort to clean it up for this post, so there is a slight chance that it could throw an error or have an unexpected result.
//Set variables: $dbName = "myDB"; $find = "&"; $replace = "&"; //Return Tables: $result = mysql_list_tables($dbName) or die(mysql_error()); $num_rows = mysql_num_rows($result); //Iterate over tables: for ($i = 0; $i < $num_rows; $i++) { $table = mysql_tablename($result, $i); //Output table name: echo "Table: ".$table . ":<br/>"; //Return fields: $result2 = mysql_query("select * from $table limit 1"); $numOfCols = mysql_num_fields($result2); //Iterate over fields: for($i2=0; $i2<$numOfCols; $i2++){ $res = mysql_query("select * from $table"); $fieldName = mysql_field_name($res, $i2); //Output field name: echo " ".$fieldName . "<br/>"; mysql_query("UPDATE $table SET $fieldName = replace($fieldName,'$find','$replace')") or die(mysql_error()); } } //Clean up memory: mysql_free_result($result); mysql_free_result($result2);
Pretty simple huh? Just replace the variables ($dbName, $find, $replace) and include your SQL connection info/credentials in the beginning of the script and you should be good. The DB I used this on wasn't very big, so this might not be practical for larger DBs - if you do use this on a larger DB make sure that you increase PHP's timeout and memory allocation. Also, one could include multiple find/replace in arrays and iterate through those by replacing lines 3 and 4 with:
$find = array("&","<",">"); $replace = array("&","<",">");
And replace line 27 with a foreach loop to iterate through the $find array:
foreach($find as $key => $val){ mysql_query("UPDATE $table SET $fieldName = replace($fieldName,'$find[$key]','$replace[$key]')") or die(mysql_error()); }
Again, the above has not been tested so I'm not to blame for anything here. As with any piece of code you get from the internet - TEST IT FIRST! Just setup a dummy DB + tables and check it out. While this issue resulted in a nice little learning experience and a piece of code that will surely be useful in the future, it could have been easily avoided. There are many different methods built into PHP to sanitize user input and even more user created scripts. I would suggest that anytime you have a string going into a table you use the mysql_real_escape_string() method. The sort of sanitation that this function provides is crucial as it helps eliminate the potential for an SQL Injection attach. To clean up strings for HTML use htmlspecialchars(). I could have used for my CMS as it would have replaced all of the 'special' characters with HTML codes, but that wouldn't have been ideal, considering I wanted to allow HTML input but still prevent my client from using characters that could break the website. Ah, but the hindsight is strong within me - so strong that I actually went back to rectify the issue (after I had to run this script about 3-4 more times, lol). The ultimate solution would have (and is), to of used something similar to CKEditor or similar WYSIWIG (What You See Is What You Get) text/HTML editor, as on of my other problems was the fact that I was trying to retain HTML capabilities for a client that didn't know any HTML!
