Security, not Obscurity

May 19, 2005 1PM PST

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