Using AWK to Win Friends and Influence Women

AWK is a powerful tool, a language really, that every administrator needs to know. I recommend everyone read about awk and learn how to use it. It can really make your day when you need to do something, quick and dirty.

You will definitely win some friends with your awk skills, but probably not too many women ;-).

I recently had to add a large volume of users to a database. Each user would need a username, password, and their full name entered into the database. I was only given their first and last name. There was no interface to use in order to register these users, hence why the project fell to me.

Like any good admin, I didn’t feel like writing queries and entering made up passwords by hand. AWK came to the rescue. Here is what I was given:

[root@secure ~]# cat /tmp/test
Napoleon Ravelo
Nella Faucher
Mira Penrose
Jeanmarie Hadfield
Earnest Chillemi
Gloria Grays
Nila Carvajal
Edie Higuchi
Alfredo Baskerville
Hildegarde Bent
Bryanna Faunce
Garnett Godsey
Christene Topping
Trenton Conti
Tereasa Noell
Lara Rudnick
Lonnie Ells
Renata Brogden
Latrisha Bara
Yuonne Granado
[root@secure~]#

I decided I would use awk’s variable functionality along with /dev/urandom and tr to generate unique passwords for each user, for security. Furthermore I used the awk tolower and substr functions to generate usernames. I wanted something like:

Latrisha Bra, ojSkl32Fjk213, lbra

But in a query format. I decided to go ahead and make my dirty awk script along with a bash while loop to generate some queries!

[root@secure~]# cat /tmp/test | while read LINE; do echo $LINE | awk -v pass=`cat /dev/urandom |tr -cd "[:alnum:]" | head -c ${1:-18}` '{print "INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('"'"'" $1 " " $2 "'"'"'" ",md5('"'"'" pass "'"'"')," "'"'"'" tolower(substr($1,0,1)) tolower($2)"'"'"'" ",4,0,1);"}'; done
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Napoleon Ravelo',md5('bxZwqeZRs13udaifeG'),'nravelo',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Nella Faucher',md5('dXFxcg57IhFvYhweQ8'),'nfaucher',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Mira Penrose',md5('bB1ZZIZILctCbJOl3w'),'mpenrose',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Jeanmarie Hadfield',md5('uo4jfydvHAx8nRKeIt'),'jhadfield',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Earnest Chillemi',md5('x5ePNOKHfbzefT0peS'),'echillemi',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Gloria Grays',md5('h0GNUNQKVx7N0OTRQZ'),'ggrays',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Nila Carvajal',md5('p8D0epKBtiw465boYR'),'ncarvajal',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Edie Higuchi',md5('oqMx0RAbvoW7uhQgGk'),'ehiguchi',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Alfredo Baskerville',md5('9yB6EANilg1MxPMoec'),'abaskerville',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Hildegarde Bent',md5('yqyjy7GTXcIXS1YKRM'),'hbent',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Bryanna Faunce',md5('tXNxf3uuY8I3wrgh6B'),'bfaunce',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Garnett Godsey',md5('HBqNV7b8bODo6HXGOb'),'ggodsey',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Christene Topping',md5('fD0TSAxFLRNkqcqTR5'),'ctopping',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Trenton Conti',md5('lcp1BaQQBmPlvvWpaM'),'tconti',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Tereasa Noell',md5('7NNSYf3lbh0ZLOFTQt'),'tnoell',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Lara Rudnick',md5('BXuuSHRmZXKpY4h2B7'),'lrudnick',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Lonnie Ells',md5('fsIG0n7MoagoHshV2H'),'lells',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Renata Brogden',md5('aFZGu0LzjjDurpxd10'),'rbrogden',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Latrisha Bara',md5('KQQvumZBdrWAqN0ZzP'),'lbara',4,0,1);
INSERT IGNORE personnel(fullname,password,username,company,admin,valid) VALUES('Yuonne Granado',md5('to6eCOFhJ6CS9Eav4K'),'ygranado',4,0,1);
[root@secure ~]# 

Here is the full dirty one-liner broken down into steps:

cat /tmp/test | while read LINE; # Start our loop
do echo $LINE | # Echo each line into a pipe
# Call awk and define a password variable
# the password variable uses /dev/urandom
# and tr to generate a password, we use
# head to get 18 characters only.
awk -v pass=`cat /dev/urandom |tr -cd "[:alnum:]" | head -c ${1:-18}`
# Now we start printing the query:
'{print "INSERT IGNORE personnel(fullname,password,username,company,admin,valid)
# Now we start adding the query variables:
VALUES('"'"'" $1 " " $2 "'"'"'" ",md5('"'"'" pass "'"'"')," "'"'"'" 
# We need to make the username variable now
# we use tolower and substr to make it the
# first letter of their first name and their
# last name:
tolower(substr($1,0,1)) tolower($2)"'"'"'" ",4,0,1);"}';
done # End the loop

And this is why we should all know awk. The sample list I used here was only 20 names, imagine if it were thousands or tens of thousands, there is no way it could have been done by hand, and the awk script was quicker to make than writing a perl or php script to do the same thing. It took just a few minutes :).