Serialize in the db ...

dalecosp

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. 🙁

Weedpacket

Serialised AND url-encoded! You know what's missing? base64. Oh, and XML. Needs more XML.

traq

$SQL = "INSERT INTO my_table( id,code,data )
VALUES( '$id','$code',rot13( urlencode( <value><id>$id</id><code>$code</code></value> ) ) )"

dalecosp

Weedpacket;11025651 wrote:
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.

traq;11025653 wrote:

$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.

mysql> UPDATE IMPORTANT_TABLE set IMPORTANT_DATUM = MD5(AES_ENCRYPT(IMPORTANT_DATUM+UNIX_TIMESTAMP()));

😃

Weedpacket

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.

bradgrafelman

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

//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.)

Weedpacket

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

$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:

That this is done
That it hasn't been remarked upon during the past four years (which was when source control and issue tracking were introduced).

dalecosp

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. :mad:

dalecosp

Weedpacket;11025669 wrote:
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.