Open source software security

Posting to MySQL with TextPad

30 November -0001

I learned how to do something pretty nifty today and figured I’d share. I own a copy of TextPad - meaning I actually paid for it because it’s just so darned useful. I’d highly recommend it for anyone who has to do any work with text files on a windows machines. The fact that you can record macros alone makes it well worth the cost in my book.

However, you can do other cool stuff with TextPad. For instance, if you have a MySQL database running locally (for development for instance) and you’re writing up a data model you can actually hook TextPad into MySQL and execute the text file into the MySQL database. This means that you can document the data model as you actually create it. This is wonderful news for anyone that has had to spec up a data model, then create it, then reverse engineer a text document that describes the model.

To do this you first create a batch file to hook TextPad into MySQL. Create a blank text document in TextPad and save it in C:\ as mysql_hook.bat or something. Avoid calling the file mysql.bat since mysql is actually the command to access MySQL and you don’t want to get confused (since windows will treat mysql.exe and mysql.bat as plain mysql). Next edit this file so it reads:

mysql -u username -pPassword < %1

replacing ‘username’ and ‘Password’ with the appropriate values. Note that there is no space after the -p flag. Also note that you probably want to utilize an account with the power to create tables and do other admin tasks, so doing this on anything other than a test machine is asking for trouble (since the account username and password are laying around your system in plain text).

Once you’re done with this file Open the Configure menu in TextPad. Then select ‘Preverences’ and click on Tools. Then click the ‘Add’ button on the right. Select ‘Program’ and navigate to your batch file. Next click ‘Apply’. Now you should see your MySQL batch file in the list of tools to the left. Next you’ll want to edit the defaults for the new entry just to make sure the ‘Capture Output’ checkbox is checked.

You should be ready to go at this point. Open up a new document and type in some SQL. Note that you’re going to want to have a ‘use database;’ command as well. For instance you could try:

use test;
show tables;

To get the contents of the file to run with MySQL simply select ‘Tools’ and click on the new program you’ve added. You will probably also notice a hotkey assignment for your new addition :) . Go ahead and select your new program and you should get a listing of all the tables in test.