Archive for September, 2005

Pitfalls of MySQL’s TIMESTAMP field…

Published by bobgerman on September 14, 2005 in Information Technology. 0 Comments

I was recently tasked to perform some modifications to a backend system written by another developer who is no longer available. Best practices dictate generating a complete database backup before performing any database operations, which is fortunate, because he left some Easter eggs in his design which I did not anticipate — he used MySQL TIMESTAMP fields.

I can see his intent — his intent was to save coding by setting a timestamp automatically for each record when inserting it into the database. The TIMESTAMP field he used was named “date,” which I have another problem with — it’s not a specific-enough field identifier. I learned by investigating the rest of the code that “date” means “date this record was added.” What he did not anticipate was the possibility of adding new fields to the database, which was my task.

Not realizing he had used TIMESTAMP fields, I simply created a new column in the database to house the new information needed by the client’s application updates. Imagine my surprise when all of the “date” fields changed to today’s date. Because that’s what TIMESTAMP fields do in a MyISAM table — update any time the record itself is modified. Ack. Surely I can imagine reasons for using such a field, but only when that behavior is specifically desired or required, as in a “last modified” field. Using that type of field for a “date added” field is absolutely insane, IMHO. YMMV.

Protecting Membership Sites

Published by bobgerman on September 9, 2005 in Information Technology. 0 Comments

Those of you running members-only pay sites, especially adult sites, I’m going to let you in on a little secret. YOU HAVE AN ENEMY. Your enemy is, in essence, socialism. There are folks out there that believe that pay sites should be free, and they are sharing your passwords. What? You don’t believe me? Check out VikingPasses. There are dozens of sites like this, and most adult webmasters will get victimized by them at least once. Smart webmasters won’t let it happen again.

Fortunately, foiling these farkers is pretty easily accomplished. If you’re lazy or have no coding skills or resources to fall back on, just hook up with Pennywize — for a small monthly fee, they will protect your site from password sharers and brute-force password attacks.

If you’ve got the coding expertise but just don’t know what to do with it, here are some clues. You want to set two thresholds:

  • Login attempts on too many accounts from the same IP (ban the IP)
  • Logins from a single account from too many IPs (disable the account and force a re-authentication with known personal information).

    Both of these thresholds should be monitored over a relatively short time period, so a reasonable solution would be to

  • Create a login table containing the fields (login, ip, timestamp)
  • Upon login attempt, add an entry to this table and clear any data more than x minutes old (use your judgement to set this threshold)
  • Query for thresholds - If a threshold is reached, ban the ip or disable the account

    Don’t set your thresholds too low, or your legitimate users checking from home and/or work and/or AOL proxies, etc. will be blocked. When you get hit by a password sharing website, you will know it. If you don’t act on it right away, your bandwidth bill will suffer, and your server performance will degrade.

    Some savvy webmasters use these password sharing sites as marketing tools. You could create a false members-only area, resource-limited or redirected to a server that can handle the bandwidth, and get this false members-only area listed on a password sharing site, driving tons of traffic to your site, offering them discounts, popups and perhaps some free content. Word to the wise, though — Folks shopping for passwords on password sharing sites are obviously in the mindset to get something for nothing, so the chance of them spending $ on your site is pretty low.