Skip to: Navigation | Content | Sidebar | Footer


Weblog Entry

Security, not Obscurity

May 19, 2005

Lesson learned: remove, don’t rename.

Don’t ever rely on security through obscurity, they say.

You know, they might just be right.

I’m giving TSEP a try to replace the limited Movable Type search box currently driving this site. Not only is it picking up all sorts of old archived files I had completely forgotten about, my heart absolutely sunk as I realized I had turned on PHP parsing just before it ran across an archived file that runs this little snippet of code:

$queryWipe = "DROP TABLE IF EXISTS submissions";
$queryCreate = "CREATE TABLE submissions (
  submissions_id mediumint(8) NOT NULL auto_increment,
  name varchar(48) NOT NULL default '',
  email varchar(40) NOT NULL default '',
  url varchar(128),
  nationality varchar(64),
  cssfile varchar(255),
  zipfile varchar(255),
  title varchar(64),
  windows varchar(255),
  mac varchar(255),
  notes text,
  status tinyint unsigned,
  archived tinyint unsigned,
  date datetime,
  PRIMARY KEY  (submissions_ID)
) TYPE=MyISAM";

require_once ('fetch-mysql-connect.php'); 

$result1 = @mysql_query ($queryWipe);
$result2 = @mysql_query ($queryCreate);

For those unfamiliar with SQL syntax, this sets up the data table for my Zen Garden manager by first erasing whatever exists. Essentially, it resets the database. (For those familiar with SQL syntax, yes I know I could stand for a huge dose of normalization, which is coming, but beside the point right now.)

You have no idea how much of a relief it was to open the file and find I had previously commented out the last two lines as so:

echo "Dangerous! Don't actually RUN this, you idiot.";

// $result1 = @mysql_query ($queryWipe);
// $result2 = @mysql_query ($queryCreate);

Two lessons learned here.

One: always delete the file with sensitive data or code. Don’t rename it, don’t move it into an ./archive directory (which I had done with this one). Get it right off the server. I was extremely lucky in this case, and you’re probably smarter than me with scripts like this anyway, but it’ll sure save you the mini-heart attack I just had at the very least, if not prevent actual damage.

Two: backup, backup, backup. I had done so recently, but still. If you haven’t figured out how to backup a MySQL database yet, either use phpMyAdmin if you have the option, or this also does the trick provided you have ssh access to your remote server. (Make sure it’s all on one line, it’s broken here for spacing):

mysqldump -add-drop-table -u {your username} 
  -p {your database name} > {backup filename}.sql

And this restores it (again, all one line):

mysql -u {your username}
   -p {your database name} < {backup filename}.sql

Since I’m the kind of person who always likes a good example to go along with my syntax, here’s what that might look like in case you’re the same way:

mysqldump -add-drop-table -u dave -p zengarden_manager > zengarden.sql

…Where dave is my username on the mysql server in question, zengarden_manager is the name of the database, and zengarden.sql is the file the database gets backed up to. You’d be prompted for a password after running the command. Make sure your path is currently located in the directory you want the file to end up, or else specify a full path in front of the file, maybe something like so: ~/backups/zengarden.sql


Mark Rowe says:
May 19, 01h

I think you may have intended for the mysql backup command to be ‘mysqldump -u …’ rather than ‘mysql -u …’.

Dave S. says:
May 19, 01h

Whoops, just caught that myself. Thanks.

Mark Rowe says:
May 19, 01h

The command for restoring the dump should still be ‘mysql -u …’ though ;-)

J Lane says:
May 19, 02h

mysqldump isn’t bad for small databases, but for larger ones, you may want to try a “mysqlhotcopy”. It’s actually written for database backup.

http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html

Sorry, been hanging out with the MySQL documentation team a bit much lately ;-)

Dave S. says:
May 19, 02h

Right. That one we’ll blame on my not having actually done a restore yet…

Josh Dura says:
May 19, 02h

Great post. When I was redesigning my blog, I just renamed the MT directory, thinking it would just not run (for some odd, stupid reason). Well, I awoke the next morning, with my old site back. Apparently, MT rewrites your files every night (stupid rebuild), and thus, it overwrote the TextPattern index, htaccess, etc.

Lesson learned, MT deleted :)

May 19, 02h

Oooh, just think of what could have happened if you never backed up your database - and that the script actually executed.

For future safety I sure hope you backup the garden often, keep every submission in the .zip/.rar/.whatever you recieved them in. Do you? That garden is a great source of inspiration and a nice view when my desktop is dull and gray.

8
Joey says:
May 19, 02h

For those on hosts without SSH access, you can backup the database in phpMyAdmin by selecting the DB and choosing the Export tab.

Dave S. says:
May 19, 02h

Arve, don’t worry, the site itself is safe. The database currently drives only the submission process, after that they’re written out to static files which exist in three locations (four if you count my external Firewire backup drive). It’ll take more than a DB mistake on my part to pull the site offline, I made sure of that. (Because, well, look how easy it is for me to make a mistake like that…)

May 19, 02h

I use phpMyAdmin (http://www.phpmyadmin.net) to backup or maintain a mySql database. It’s simple and also has a backup-function with many options.

May 19, 02h

Ouch, that woulda sucked. Another thing I find a lot of ppl doing is having a file on their server to output their phpinfo() settings. Things like that can be just as dangerous. You’re better off just SSHing into your server to check settings, rather than creating a script that runs in the browser.

Another thing to think about but probably doesn’t get done a lot, is to code for an assurance (are you sure you want to do this? prompt) before any DELETES, ALTERS are shot off. Just for a lil overkill. I don’t do it myself.

May 19, 03h

Ditto the phpMyAdmin suggestions. Whenever I get a new MySQL database, I always make sure I have the ol’ “pma” running as well. It’s a snap to install and makes all database operations painless and SSH-free.

May 19, 03h

Hi Dave

I always find it handy to include the date in the dumped database name e.g.

mysqldump -add-drop-table -u {your username}
-p {your database name} > {YYYYMMDD}{backup filename}.sql

or

mysqldump -add-drop-table -u dave -p zengarden_manager > 20050520zengarden.sql

Cheers
Bruce

14
Albert says:
May 19, 03h

\ works to make multiline commands and with a fixed width layout you can be almost sure that it shows up on the number of lines you intend.
mysqldump -add-drop-table -u dave \
-p zengarden_manager > zengarden.sql

Dante says:
May 19, 04h

Why not just use Google to search your site? I use a variation of this method here. It’s really simple and effective.

In any case, this is a great lesson. Thanks for the heads up.

Keith says:
May 19, 04h

It’s always a good idea to setup a cron job to run your backups automatically too. That way you can have a daily, weekly or monthly backup.

You could even go so far as to write a script to email the backup to you, instead of storing it on your server.

May 19, 05h

I would recommend that everyone back up their databases on their server, as well as at least one more location. I made the mistake of only backing up on my server, and then the hard drive failed. Fortunately for me, Google, MSN Search, and Yahoo! Search had indexes of all my pages.

(It still sucked re-building the database even though I had the data.)

Tony says:
May 19, 07h

These are great suggestions. I almost had the same thing happen to me a few months ago.

Luckily for me, I created a PHP script that backups my database every day it is accessed (to avoid unnecessary, redundant copies). The backup file is stored on the server and the permissions are changed immediately, to block any attempt to delete it. Ever since I had this problem, I’ve included this code in all web projects I’ve done for my clients. I strongly recommend that everyone else do the same.

May 19, 08h

I’ve been pretty lazy and just used the Full Backup feature built into CPanel. By running an FTP server on my local desktop, CPanel can log right in and download it to me.

Sometime I hope to come upon (or create) a more sophisticated PHP-based backup tool that can be cronned. It would be so much better to grab the DB nightly, but avoid the much larger photo directy until it’s been a few weeks.

For those with SSH on their server, one of the best ways to keep a backup completely up to date is with rsync. You can just keep a complete local copy that syncs itself every few hours. (and then locally do versioning or whatever)

20
Mats says:
May 19, 11h

Talking about building databases, I have a favorite tool, on windows though, and it is DDT (like the name :P) http://gnuwin.epfl.ch/apps/DDT/en/

I like to look at my database before i build it and this tool is sooooo lightweight it’s almost silly.

Great job Dave, in general. :D

May 20, 06h

Couple more suggestions:

Down with php, try rubyonrails http://rubyonrails.com

Happiness on a keyboard.

Also, svn.
http://subversion.tigris.org/

Great for when I delete my site by accident or if I want to go back to before I saved all those changes that didn’t quite turn out as planned.

22
Nahan Eror says:
May 20, 10h

Hey Dave, I second subversion! Keeping everything in version control lets you just delete those files instead of renaming them because you know you’ll always have it in a previous revision. If you want it, it’s just a one line command to get it back, but it’s not in your working copy mucking everything up. I keep all of my personal files and code in subversion and keep the subversion DB backed up, and I never worry about deleting anything. :)

May 20, 10h

Rather than either shuffling around or deleting, I’d move such a file out of the document root.

You might also want to consider adding the file to a compressed archive before deleting it; that keeps it around, but prevents it from doing any harm.

May 20, 12h

MySQl AB provide an admin tool on their site which, if you can connect remotely, does scheduled backups amongst many other things.

The downside is connecting remotely requires attention to security. The plus side is that your back up can be geographically distant from the original and you don’t have to remember to do it.

Dan says:
May 23, 04h

And the Ruby on Rails fanatics move in… it’s almost like Star Wars II only without all the cool explosions. ;)

Keith has the right idea regarding using Cron and email to ensure timely and effective backups. It can be a pain depending on several factors, but it’s well worth the time. And I’m with Dave on this one… backups, backups, backups. You’d think after 12 years in IT I’d have driven this through my thick skull when it comes to my own data. Oh, I always back up my clients data… often redundantly. My stuff??? Naaaahhh….

May 23, 07h

Backups are a very good thing, but doing them manually is not. You can automate a MySQL backup quite easily with something like AutoMySQLBackup: http://sourceforge.net/projects/automysqlbackup/, which will also e-mail it to you.

27
Kevin says:
May 23, 09h

Dave, you have made my day. First the Zen garden setting me free.. and now a good laugh. I never thought I would get into the whole blogging scheme but it appears I am in love. :P

Cheers

Eric says:
May 23, 12h

We’re all guilty, now and then. I’ve done virtually the same thing, more times than I can count. In fact, I think it’s about time to run another backup…

Zeerus says:
May 24, 06h

Hi Dave.

I know next to nothing about PHP, but this sounds pretty serious. I’ll be redesigning my site soon, and will have it running on TextPattern. I’m using CPanel X, is there any way to have my database automatically backed up, besides using the backup feature manually?

I have seen the CRON Jobs feature, but haven’t used it yet.

May 24, 10h

I know all too well how that “sunken heart” feeling is after running a script that wasn’t supposed to be run.

You could always automate your SQL backup by using a cron job with mysqldump or mysqlhotcopy, that way you’re not relying on memory. Personally, I never backup my databases, and it’s out of laziness. I suppose this article could be my motivation.

PS. It’s always good to have 2 copies of the SQL dump. One on the server, one somewhere else. I had a hard drive failure once and though I had been backing up the databases, the backups were all on that drive.