Serialize in the db ...
Results 1 to 9 of 9

Thread: Serialize in the db ...

  1. #1
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,722

    Serialize in the db ...

    Code:
    mysql> select * from my_old_table order by id desc limit 3;
    +----------+------+----------------------------------------------+
    | id       | code | data                                         |
    +----------+------+----------------------------------------------+
    |     7431 | 8936 | a%3A1%3A%7Bs%3A5%3A%22price%22%3Bi%3A1%3B%7D |
    |     7430 | 8936 | a%3A1%3A%7Bs%3A5%3A%22price%22%3Bi%3A1%3B%7D |
    |     7429 | 8936 | a%3A1%3A%7Bs%3A5%3A%22price%22%3Bi%3A1%3B%7D |
    +----------+------+----------------------------------------------+
    3 rows in set (0.00 sec)
    Ugh.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,905
    Serialised AND url-encoded! You know what's missing? base64. Oh, and XML. Needs more XML.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    PHP Code:
    $SQL "INSERT INTO my_table( id,code,data )
    VALUES( '
    $id','$code',rot13( urlencode( <value><id>$id</id><code>$code</code></value> ) ) )" 

  4. #4
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,722
    Quote Originally Posted by Weedpacket View Post
    Serialised AND url-encoded! You know what's missing? base64. Oh, and XML. Needs more XML.
    Please, strangle me now. With bacon. Although, I didn't do it. The guy's name was Al, Albert, or Alfred, or something like that, and he lives on the left coast. 'Nuff on that.

    Quote Originally Posted by traq View Post
    PHP Code:
    $SQL "INSERT INTO my_table( id,code,data )
    VALUES( '
    $id','$code',rot13( urlencode( <value><id>$id</id><code>$code</code></value> ) ) )" 
    If only this weren't, y'know, mission-critical and all that jazz.

    Code:
    mysql> UPDATE IMPORTANT_TABLE set IMPORTANT_DATUM = MD5(AES_ENCRYPT(IMPORTANT_DATUM+UNIX_TIMESTAMP()));
    Last edited by dalecosp; 03-26-2013 at 05:24 PM.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  5. #5
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,905
    To be fair, (to be generous...), if the serialised array were a semistructured bundle of name-value pairs that doesn't make a good fit with an RDBMS, then serialising said arrays would make sense - it would already be a pain to query on so you might as well query on what can be queried on, then filter further on the application side (assuming that the array's properties would even be something you'd search on).

    The alternative would be hundreds of mostly-NULL columns or dozens of tables that would need to be joined together to reassemble the array. I've got both of those situations at the moment. Oh, and fk constraints? Do me a favour. Please.

    Serialising and urlencoding it, though.... Someone wasn't aware of how to escape arbitrary data and used whatever came to hand?

    Oh, dear.

    I'm starting to empathise with these people.

    This project is starting to affect me.
    Last edited by Weedpacket; 03-26-2013 at 08:52 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  6. #6
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,432
    Quote Originally Posted by Weedpacket View Post
    Serialising and urlencoding it, though.... Someone wasn't aware of how to escape arbitrary data and used whatever came to hand?
    PHP Code:
    //strip out anything that isn't a letter or number just so we don't worry about SQL injections!
    $password preg_replace('/[^a-zA-Z0-9]/'''$_POST['password']); 
    That should improve security!

    (... said no computer scientist with an IQ at least in the double digits.)

  7. #7
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,905
    You've been reading over my shoulder, haven't you?!

    Our system is friendly to its users; it generates a default display name based on their first name, surname, (optional) middle initial (which it doesn't use), and prepends a salutation of their choice (it offers "Mr.", "Mrs.", or "Ms."). You are, however, free to change it.

    Then it goes through
    PHP Code:
    $display_name preg_replace('/[^a-zA-Z0-9 ]/i'''$display_name); 
    Yes, we do use an abstraction layer.

    I'm not sure which is more upsetting:
    1. That this is done
    2. That it hasn't been remarked upon during the past four years (which was when source control and issue tracking were introduced).
    Last edited by Weedpacket; 03-27-2013 at 05:32 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  8. #8
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,722
    Well, this is getting pretty nasty.

    NO! Not that --- not this conversation. This little bump in the road to release.

    It becomes more apparent why things run slowly. For each page load, I think they check two tables that are both formatted in this fashion and put them in the appropriate objects, check the $_POST array to see if anything should be added to the second object (and hence, the second table), then write it to the DB and then go ahead and, y'know, display stuff. So, for one page load, two queries and two calls to urldecode() and unserialize(), plus one to serialize and urlencode() and another DB write ... just for this subsystem, which, the DB shows NO ONE is currently using....

    I think I have the fellow's address. It's a good thing I think murder is an affront to God

    I also really hope I can find a fairly elegant solution to this one or I may drop a few orders of magnitude in execution time just as he apparently did.
    Last edited by dalecosp; 03-27-2013 at 04:39 PM.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  9. #9
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,722
    Quote Originally Posted by Weedpacket View Post
    To be fair, (to be generous...), if the serialised array were a semistructured bundle of name-value pairs that doesn't make a good fit with an RDBMS, then serialising said arrays would make sense - it would already be a pain to query on so you might as well query on what can be queried on, then filter further on the application side (assuming that the array's properties would even be something you'd search on).
    Snipped, but, yes --- exactly.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •