Converting Serialized Objects to JSON Objects

Published by John on December 18, 2018 Under PHP

If you are doing a data migration, sometimes you need to convert data stored in the database from one type to another. The most common time is when you are converting data that was serialized to a json object. I have written about this before and created a script for converting wordpress serialized data to a json object, but recently while working on a Magento 2 Migration, I found it was necessary to do this for a table outside of wordpress. So, this is the non-wordpress version of that script.

Note that I did copy the is_serialized function from wordpress, as this seems to work pretty well. And, for security reasons, you should probably not run this on data that you don’t trust or is user-supplied.

Since this was created for a different site, you will need to make some updates to get this working with your particular database/table schema. However, it should provide a decent starting point if you need to bulk update serialized objects to JSON objects.

Before using this, backup your data!

No really, make a backup of your mysql database, don’t use this on a production site, and test that it works with your server!

ini_set('display_errors', 1);
set_time_limit(0);

$dbname='your_database';
$username = 'your_database_user';
$password = 'your_password';
$table_name = "your_table_name";
$update_column_name = "your_column";
$id_column_name = "your_id_column";

try {
	
   $dbh = new PDO("mysql:host=localhost;dbname={$dbname}", $username, $password);
   
   $results = $dbh->query("SELECT * from {$table_name}");   
   
   echo "Found: {$results->rowCount()} results\n";
   
   foreach($results as $row){
	   
	   $id = $row[$id_column_name];
	   $update_column = $row[$update_column_name];
	   $update_column_new = false;

	   echo "Checking {$id_column_name}={$id}\n";
	   
	   if(is_serialized($update_column)){		   
		   $update_column_new = unserialize($update_column);
		   $update_column_new = json_encode($update_column_new);		   
	   }   

	   if(!empty($update_column_new)){
			
			echo "Updating {$update_column_name}\n";
			$sql = "UPDATE {$table_name} SET {$update_column_name}=? WHERE {$id_column_name}=?";
			$stmt= $dbh->prepare($sql);
			$stmt->execute([$update_column_new, $id]);		
			
		} else{
			echo "No Changes\n";
		}
}
   
   
} catch (Exception $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

/* Copied from WordPress's Functions.php */

function is_serialized( $data, $strict = true ) {
	// if it isn't a string, it isn't serialized.
	if ( ! is_string( $data ) ) {
			return false;
	}
	$data = trim( $data );
	if ( 'N;' == $data ) {
			return true;
	}
	if ( strlen( $data ) < 4 ) {
			return false;
	}
	if ( ':' !== $data[1] ) {
			return false;
	}
	if ( $strict ) {
			$lastc = substr( $data, -1 );
			if ( ';' !== $lastc && '}' !== $lastc ) {
					return false;
			}
	} else {
			$semicolon = strpos( $data, ';' );
			$brace     = strpos( $data, '}' );
			// Either ; or } must exist.
			if ( false === $semicolon && false === $brace )
					return false;
			// But neither must be in the first X characters.
			if ( false !== $semicolon && $semicolon < 3 )
					return false;
			if ( false !== $brace && $brace < 4 )
					return false;
	}
	$token = $data[0];
	switch ( $token ) {
			case 's' :
					if ( $strict ) {
							if ( '"' !== substr( $data, -2, 1 ) ) {
									return false;
							}
					} elseif ( false === strpos( $data, '"' ) ) {
							return false;
					}
					// or else fall through
			case 'a' :
			case 'O' :
					return (bool) preg_match( "/^{$token}:[0-9]+:/s", $data );
			case 'b' :
			case 'i' :
			case 'd' :
					$end = $strict ? '$' : '';
					return (bool) preg_match( "/^{$token}:[0-9.E-]+;$end/", $data );
	}
	return false;
}

To use, update the $dbname, $username, and $password variables at top with the respective values for your MYSQL database.

Then, update the $table_name, $update_column_name, and $id_column_name with the respective values for the table column that you are updating. Make sure that $id_column_name is a unique identifier, like “ID”, for the table, as this will be used to update the values in database.

No Comments |