$wpdb doesn't like to store arrays

Question

I'm attempting to store an array that looks like the one below, into a cell in a database using the code below the sample array. Unfortunately, WordPress doesn't seem to want to save it, and continually throws a database error (like the one on the bottom). Changing the order of the data in the array changes the location of the error, which is not tied to any particular character.

Sample Array (the actual array is a bit different):

$data = array();
$data['twitter'] => array( 'key1' => '64 digit long string', 'key2' => '63 digits');
$data['facebook'] => array( 'key1' => '64 digit long string', 'key2' => '63 digits');

It should be noted the column this is being inserted to is set to varchar 1000 (thus not an issue of the data being too long)

Sample Insertion code:

$serialized_data = maybe_serialize($data);
$fire = $wpdb->query( $wpdb->prepare( "UPDATE $table_name SET oauth = %s WHERE email = %s",  $serialized_data, $email ) );

Sample Error: (note the real payload has been swapped out for security reasons, but the demo data is anatomically correct. Same format, same type of characters. Just randomized)

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wp_custom_4' WHERE email = '[email protected]' SET tasks = 'a:1:{s:7:\"twitte' at line 1]
UPDATE 'wp_custom_4' WHERE email = '[email protected]' SET tasks = 'a:1:{s:7:\"twitter\";a:4:{s:11:\"oauth_token\";s:50:\"128310837-uY4xWwegwegmwIUBxWWf7gilcqQWRGWRGWzeV3tU26N\";s:18:\"oauth_token_secret\";s:45:\"MPfJMJULgjD8JwoyAsZUEHRWRGPXMrqMgzBOyvVFPtm5s\";s:7:\"user_id\";s:9:\"1282424237\";s:11:\"screen_name\";s:9:\"sampleuser\";}}'

This also isn't a consequence of the serialization method employed. I've attempted using serialize(), json_encode(), and also not serializing it at all

Answer 1

Don't store serialised PHP data in the database! It can be a major security risk!

When the contents of the value are deserialised, any objects get recreated and their constructors and wakeup methods run, this can be used to launch an attack.

Additionally, that data can't be search replaced, because PHP serialised strings contain values for data lengths that don't get changed, generating fatal errors when deserialised if modified

So instead:

• Consider JSON instead
• Store separate values as separate values, multiple rows, separate columns etc

Answer 2

This seems to work for some reason using data that a prepared query doesn't like:

$wpdb->update( 
    $table_name, 
    array( 
        'oauth' =>  $serialized_data,
    ), 
    array( 'email' => $email ), 
    array( 
        '%s',
    ), 
    array( '%s' ) 
);