Before running any of the queries here, I strongly recommend backing up your database (using
phpMyAdmin or the Coppermine plugin "
backup/restore").
To convert your current referer fields to proper characters, you can use the following MySQL query (using a tool like phpMyAdmin):
DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode` $$
CREATE FUNCTION `url_decode`(original_text text) RETURNS text CHARSET utf8
BEGIN
declare new_text text default null;
declare pointer int default 1;
declare end_pointer int default 1;
declare encoded_text text default null;
declare result_text text default null;
SET new_text = replace(original_text,'+',' ');
SET pointer = LOCATE("%", new_text);
while pointer <> 0 && pointer < (char_length(new_text) - 2) do
SET end_pointer = pointer + 3;
while mid(new_text, end_pointer, 1) = "%" do
SET end_pointer = end_pointer+3;
end while;
SET encoded_text = mid(new_text, pointer, end_pointer - pointer);
SET result_text = convert(unhex(REPLACE(encoded_text, "%", "")) using utf8);
SET new_text = REPLACE(new_text, encoded_text, result_text);
SET pointer = LOCATE("%", new_text, pointer + char_length(result_text));
end while;
return new_text;
END $$
DELIMITER ;
UPDATE `cpg_hit_stats` SET referer = url_decode(referer)
where you replace in the last line
cpg_hit_stats with your table name, e.g.
cpg145_hit_stats. Do the same for the _vote_stats table, e.g.
cpg145_vote_stats.
The above code worked for me on one database with over 45,000 rows.
On another database (on a different web host), I got an error mentioning
DELIMITER ; UPDATE . . .. For this one, I used the following code in phpMyAdmin while also changing the Delimiter to
$$ in phpMyAdmin's box just below the query box:
DROP FUNCTION IF EXISTS `url_decode` $$
CREATE FUNCTION `url_decode`(original_text text) RETURNS text CHARSET utf8
BEGIN
declare new_text text default null;
declare pointer int default 1;
declare end_pointer int default 1;
declare encoded_text text default null;
declare result_text text default null;
SET new_text = replace(original_text,'+',' ');
SET pointer = LOCATE("%", new_text);
while pointer <> 0 && pointer < (char_length(new_text) - 2) do
SET end_pointer = pointer + 3;
while mid(new_text, end_pointer, 1) = "%" do
SET end_pointer = end_pointer+3;
end while;
SET encoded_text = mid(new_text, pointer, end_pointer - pointer);
SET result_text = convert(unhex(REPLACE(encoded_text, "%", "")) using utf8);
SET new_text = REPLACE(new_text, encoded_text, result_text);
SET pointer = LOCATE("%", new_text, pointer + char_length(result_text));
end while;
return new_text;
END $$
UPDATE `cpg_hit_stats` SET referer = url_decode(referer) WHERE referer REGEXP '%'
replacing the table in the last line as before. The number of rows affected here was over 185,000. I ran the same script a second time and it affected about 400 rows, then a third with 1 row, then nothing after that. I'm not sure why it didn't process all the rows the first time. I wasn't generating new bad referer fields I don't think. I didn't have enough time to investigate so I just mention it here in case it's useful for anyone.
As I said above, make sure to back up your database first.
If the scripts don't work, please don't ask here. You'll need to consult the
MySQL documentation.
The function above comes from
Lukas Oberhuber.
