Updating serialised array in mysql (without unserialising?)

Question

Everything I've read says that storing serialised arrays in Mysql is a bad idea - I know that already ;) Unfortunately I'm working with an open source script that uses this method, and changing the structure isn't an option in this scenario.

Is it possible to update this URL without first unserialising?

I originally tried using replace, however it throws an error:

  $rssquery = "UPDATE config SET `array` = replace(`array`, \"http://www.oldurl.com\", \"http://www.newurl.com\") WHERE name='config'";
  $insert = $db->insert($rssquery);

Could not update UPDATE config SET array = replace('array', 'http://www.oldurl.com', 'http://www.newurl.com') as variable supplied must be an array.

Table name: config
Columns: name | array
Row Needing Updated named: config
Cell Needing Updated named: array

Any other ideas or approaches would be appreciated :) Thanks!

Answer 1

If you simply search and replace like that, you will render the serialized data ununsable. Here's what you need to do:

$old = 'http://www.google.com';
$new = 'http://www.someplace.com';

$search = 's:' . strlen($old) .':"' . $old . '"';
$replace = 's:' . strlen($new) .':"' . $new . '"';

$query = "UPDATE config SET array=REPLACE(array, '{$search}', '{$replace}');";

Replace $old and $new with your current and target url's, run the script and execute the generated $query.

Here's a pure SQL solution:

SET @search := 'http://www.original.com';
SET @replace := 'http://www.target.com';
UPDATE config SET array=REPLACE(array, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));

Note that this will replace EVERY occurrence of the search string in your serialized array. If you are looking to replace a specific key, you have to be more, huh, specific.

Answer 2

Thought I would share a tool written in PHP (script) that you can download that does a database search and replace. To quote the author from the site:

Search Replace DB version 3.0.0 (currently a BETA version) allows you to carry out database wide search/replace actions that don’t damage PHP serialized strings or objects with a user friendly interface and experience.

You can find it here: Database Search and Replace Script in PHP

I've moved an ecommerce, wordpress and other databases to a different domain and I found it invaluable. I haven't looked into the PHP script itself to look under the hood, but if they haven't obfusicated the code, you may find a gold mine of ideas and methods... lol