0

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 = 'chriscct7@gmail.com' SET tasks = 'a:1:{s:7:\"twitte' at line 1]
UPDATE 'wp_custom_4' WHERE email = 'sample@gmail.com' 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

  • Your query is in the wrong order- UPDATE SET WHERE, not UPDATE WHERE SET. – Milo Jun 7 '14 at 19:15
  • I noticed this before I posted but I forgot to use the update query. They are in the correct order when throwing the error – Chris Jun 7 '14 at 19:21
  • I've updated the post to reflect this – Chris Jun 7 '14 at 19:22
  • I don't think you should be escaping the table name, that would probably break things. – Milo Jun 7 '14 at 19:28
  • 2
    but it puts the table name in single quotes, which is not valid as far as I know. – Milo Jun 7 '14 at 19:59
0

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' ) 
);
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.