+/*
+ Find and update all database records with special chars in text fields
+ to ensure all of them are changed to html entites.
+*/
+function sanitize_database($pref, $test = false) {
+
+ if ($test)
+ error_log('Sanitizing database ...');
+
+ $tsql = "SHOW TABLES LIKE '".($pref=='' ? '' : substr($pref,0,-1).'\\_')."%'";
+ $tresult = db_query($tsql, "Cannot select all tables with prefix '$pref'");
+ while($tbl = db_fetch($tresult)) {
+ $table = $tbl[0];
+ $csql = "SHOW COLUMNS FROM $table";
+ $cresult = db_query($csql, "Cannot select column names for table '$table'");
+ $textcols = $keys = array();
+ while($col = db_fetch($cresult)) {
+ if (strpos($col['Type'], 'char')!==false
+ || strpos($col['Type'], 'text')!==false)
+ $textcols[] = '`'.$col['Field'].'`';
+ if ($col['Key'] == 'PRI') {
+ $keys[] = '`'.$col['Field'].'`';
+ }
+ }
+
+ if ($test)
+ error_log("Table $table (".implode(',',$keys)."):(".implode(',',$textcols)."):");
+
+ if (!count($textcols)) continue;
+
+ // fetch all records containing special characters in text fields
+ $sql = "SELECT ".implode(',', array_unique(array_merge($keys,$textcols)))
+ ." FROM {$table} WHERE
+ CONCAT(".implode(',', $textcols).") REGEXP '[\\'\"><&]'";
+ $result = db_query($sql, "Cannot select all suspicious fields in $table");