Somebody uploaded some bad data (serialized php) into my database and I’m trying to figure out how to clean it up using a MySQL query.
The bad data looks like this:
a:1:{i:0;s:7:“Robert”;}
I’ve tried this pair of queries but my database keeps giving me the all too familiar, “You have an error in your SQL syntax;”
UPDATE profile_values
SET value
SUBSTRING_INDEX(value
, ‘"’, -2) WHERE fid = 1 AND uid = 22;
UPDATE profile_values
SET value
TRIM(TRAILING ‘";}’ FROM value
) WHERE fid = 1 AND uid = 22;
I built these queries using these examples from the MySQL reference:
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
-> ‘mysql.com’
mysql> SELECT TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’);
-> ‘barx’
Any direction anyone can give would be greatly appreciated.
Kevin