A long time ago, I created a database to hold passwords and their respective hashes for some 16 various hash types. It has approximately 310,261,848 passwords for each type and is growing nearly every day as more password lists become available. I found a pretty quick way to generate the hashes for these wordlists and wanted to share how it is done. These hashes only work with unsalted/unpeppered passwords.
First, lets look at my table schema, which is very simple and very effective. It uses an index on the hash + password column so there can not be any two hashes+passwords that are the same. The types table is a simple lookup table that references data.type 1 to a name like DES. The primary key is on the name column. I don’t claim to be a db administrator so if you spot any errors, let me know.
[codesyntax lang=”bash”]
mysql> show create table data \G;
*************************** 1. row ***************************
Table: data
Create Table: CREATE TABLE `data` (
`hash` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`type` int(11) NOT NULL,
UNIQUE KEY `hash` (`hash`,`password`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[/codesyntax]
[codesyntax lang=”bash”]
mysql> show create table types \G;
*************************** 1. row ***************************
Table: types
Create Table: CREATE TABLE `types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[/codesyntax]
Once we have our database, we need to create a list of hashes from a wordlist and then populate the database with the hash and the type of hash. In order to create the hashes, I use hash_gen.pl which is included in the jumbo version of jtr (john the ripper). Because new hashes are added all the time, I am not going to cover its installation or usage, but will advise you to get the version from the most recent release of john jumbo. The file is located in the run directory.
In order to get the version included in john-1.7.9-jumbo-7 to work on kali linux, I had to install these dependencies using apt-get:
[codesyntax lang=”bash”]
apt-get install -y libauthen-passphrase-perl libcrypt-rc4-perl libcrypt-cbc-perl libcrypt-ecb-perl libauth-yubikey-decrypter-perl libcrypt-cast5-perl libcrypt-ciphersaber-perl libcrypt-des-ede3-perl libcrypt-dh-gmp-perl libcrypt-dsa-perl libcrypt-generatepassword-perl libcrypt-gpg-perl libcrypt-hcesha-perl libcrypt-mcrypt-perl libcrypt-nettle-perl libcrypt-openssl-dsa-perl libcrypt-openssl-random-perl libcrypt-openssl-rsa-perl libcrypt-openssl-x509-perl libcrypt-random-source-perl libcrypt-saltedhash-perl libcrypt-simple-perl libcrypt-smbhash-perl libcrypt-smime-perl libcrypt-twofish-perl libcrypt-unixcrypt-perl libcrypt-util-perl libcrypt-x509-perl librdf-crypt-perl libtie-encryptedhash-perl
[/codesyntax]
There is a chance that some of the packages above may not have been needed but I installed them for other research I was doing or for other reasons. There is also a chance that other dependencies are required that are not listed but they were already installed. There were two dependencies that had to be installed via CPAN because I could not find a suitable debian / kali linux package. They were:
[codesyntax lang=”bash”]
perl -MCPAN -e install 'Crypt::PBKDF2' perl -MCPAN -e install 'Crypt::OpenSSL::PBKDF2'
[/codesyntax]
Once you have those packages done, you simply feed hash_gen.pl a password list and the type of password you are creating. I generally group them by type, i.e. if I wanted to generate md5 passwords, I would use this command:
[codesyntax lang=”bash”]
./hash_gen.pl RawMD5 -nocomment < /path/to/word/list > /path/to/generated/hashes
[/codesyntax]
This would then create a file called /path/to/generated/hashes containing data like:
[codesyntax lang=”bash”]
u0-RawMD5:e10adc3949ba59abbe56e057f20f883e:0:0:123456:: u1-RawMD5:5f4dcc3b5aa765d61d8327deb882cf99:1:0:password:: u2-RawMD5:25d55ad283aa400af464c76d713c07ad:2:0:12345678::
[/codesyntax]
You will notice there are 7 fields in the results for each password separated by a colon. We only care about 3 of them, the first one contains the type: RawMD5, the second one contains the hash, the 5th one contains the original password. With this information, we can now create a script that will enter that data into our database. If you haven’t created a database yet, create it now….I call mine pass:
[codesyntax lang=”bash”]
mysqladmin create pass
[/codesyntax]
You will then need to create the two tables from the information provided above about table schema. Log into mysql, use the pass database and enter these commands in the mysql console:
[codesyntax lang=”bash”]
mysql> use pass; mysql> CREATE TABLE `data` (`hash` varchar(255) NOT NULL, `password` blob, `type` int(11) NOT NULL, UNIQUE KEY `hash` (`hash`,`password`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> CREATE TABLE `types` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`name`)) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
[/codesyntax]
The show tables command will then list the two tables:
[codesyntax lang=”bash”]
mysql> show tables; +----------------+ | Tables_in_pass | +----------------+ | data | | types | +----------------+ 2 rows in set (0.00 sec)
[/codesyntax]
Once you have everything set up, you have generated your hashes file using hash_gen.pl, you can now process the data using this script:
[codesyntax lang=”bash”]
#!/bin/bash
# By Ed Wiget
# This script takes a list of password hashes created by hash_gen.pl and inserts the password and hash into the pass.data table
# the format of the file read is like
# u0-RawMD5:e10adc3949ba59abbe56e057f20f883e:0:0:123456::
# u1-RawMD5:5f4dcc3b5aa765d61d8327deb882cf99:1:0:password::
# u2-RawMD5:25d55ad283aa400af464c76d713c07ad:2:0:12345678::
# 20110324 – original script
# enable next line for debugging
# set -x
# set our db name here
DB_NAME=your_db_name
# the db user
DB_USER=your_db_user_name
# the db password
DB_PASS=’your_db_users_password’
# the db host
DB_HOST=localhost
if [ “$1″ == ” ]; then
echo “What is the full path of the file to process?”
echo “i.e. /path/to/hashes/file”
read FILE_LOCATION
else
FILE_LOCATION=$1
fi
# now we have our file location, we set up a loop to process it.
while read LN; do
# we get the type and make sure it exists in the types table
# RawMD5
HASH_TYPE=`echo ${LN} | awk -F: ‘{print$1}’ | awk -F- ‘{print$2}’`
echo “hash type is ${HASH_TYPE}”
# we make sure ${HASH_TYPE} exists in the database types.
TYPES_CHECK=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “select * from types where name=’${HASH_TYPE}’;” | grep -v name | wc -l`
if [ “${TYPES_CHECK}” = “1” ]; then
echo “${HASH_TYPE} already exists in the database”
# now we get the id of the type to use in the mysql insert
HASH_TYPE_ID=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “select id from types where name=’${HASH_TYPE}’;” | grep -v id`
# and we insert the records into the database
# we get the hash from the line
NEW_HASH=`echo ${LN} | awk -F: ‘{print$2}’`
# we get the original password from LN
NEW_PASS=`echo ${LN} | awk -F: ‘{print$5}’`
# validation check of data
echo “please wait while we insert record password ${NEW_PASS} and hash ${NEW_HASH} into the db for hash type ${HASH_TYPE} with hash type id ${HASH_TYPE_ID}….”
# and we insert the records into the database
mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “insert into data (hash,password,type) VALUES (\”${NEW_HASH}\”,\”${NEW_PASS}\”,\”${HASH_TYPE_ID}\”);”
else
echo “${HASH_TYPE} does not exist in the database, we will create a new hash type”
# we insert the new hash type
mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “insert into types (name) VALUES (\”${HASH_TYPE}\”);”
# now we get the id of the type to use in the mysql insert
HASH_TYPE_ID=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “select id from types where name=’${HASH_TYPE}’;” | grep -v id`
# and we insert the records into the database
# we get the hash from the line
NEW_HASH=`echo ${LN} | awk -F: ‘{print$2}’`
# we get the original password from LN
NEW_PASS=`echo ${LN} | awk -F: ‘{print$5}’`
# validation check of data
echo “please wait while we insert record password ${NEW_PASS} and hash ${NEW_HASH} into the db for hash type ${HASH_TYPE} with hash type id ${HASH_TYPE_ID}….”
# and we insert the records into the database
mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be “insert into data (hash,password,type) VALUES (\”${NEW_HASH}\”,\”${NEW_PASS}\”,\”${HASH_TYPE_ID}\”);”
fi
done < ${FILE_LOCATION}
echo “all records have now been processed from ${FILE_LOCATION}”
[/codesyntax]
One thing to note is that using the hash as the primary key could have collisions at some point and time. I had originally intended to make the primary key the hash+password but never got around to doing it. Basically, this entire process was a learning experience anyways, but the database has been extremely helpful when doing penetration testing…especially of web applications where many developers still use unsalted passwords and simply rely on md5 or sha1 password storage.
UPDATED 20130519 – I made the data tables primary key a combination of hash and password. I made the primary key of the tables table name. This will fix any possibility of a collision on the hash or on the hash type. If you already have the old style tables done, you can fix it with these commands:
[codesyntax lang=”bash”]
$ mysql -uUSERNAME -pPASSWORD pass myqsl> alter table data modify password varchar(255); mysql> alter table data modify password int not null; mysql> alter table `data` drop key `hash` , add primary key ( `hash`, `password` ); mysql> alter table types drop key `id` , add primary key(`name`);
[/codesyntax]
Updated 20130602 – new perl script coming soon. The bash script works but it is very slow. It takes about 17 hours to insert 1 million records. This is attributed to the fact that the bash script has to open up the mysql binary process each time. I have written a perl script that does this much faster. In my testing, I was able to insert about 60 million records in 14 hours. Once I have this perl script ready…..I will post it.
Updated 20130828 – Adding the perl script. Also adding the new process to go from wordlist to db inserts.
Copy the following code into a file named import_passwords.pl
[codesyntax lang=”perl”]
#!/usr/bin/perl
# change your database name, username, password for db credentials below where it says DB_NAME, DB_USERNAME, DB_PASSWORD
use strict;
use warnings;
use DBI;
use Try::Tiny;
use FindBin qw($Bin);
use lib "$Bin";
#
# import filename?
#
if ( $#ARGV + 1 != 1 ) {
_syslog('err', "usage: $0 /path/to/File");
die();
}
my $importfile = $ARGV[0];
if (! -f $importfile ) {
_syslog('err', "$0: $importfile not accessible");
die();
}
#
# process loop
#
my $import_line = "";
my $lines_read=0;
my $db_name='DB_NAME';
my $db_host='127.0.0.1';
my $db_user='DB_USERNAME';
my $db_pass='DB_PASSWORD';
open(FILE, $importfile ) or do {
_syslog('err', "$0: $importfile not accessible");
die();
};
my $dbh = db_connect() || die( 'no db connection' );
#skip lines already imported
#if ( $skip_lines ) {
# while ( $skip_lines >= 0 && <FILE> ) {
# $skip_lines--; $lines_read++;
# }
#}
while ( $import_line = <FILE> ) {
$lines_read++;
# skip line if comment
next if $import_line =~ /^\#/;
print("$import_line\n");
#split by , characters
chomp $import_line;
my @fields = split /,/, $import_line;
#next if ( $#fields != 3 );
my $values = '';
foreach my $f ( @fields ) {
$values .= (( $values eq '' ) ? '' : ',') . $dbh->quote($f);
}
#finish SQL construction
my $sql = "INSERT INTO data (hash,password,type) VALUES ($values)";
print $sql;
# DOIT
my $result = $dbh->do( $sql );
if ( !$result )
{
_syslog( 'err', "ERROR: " . $DBI::errstr );
}
# debug - we do one record
#last;
}
#import done - clean up
close(FILE);
exit();
##############################
#
# connect to db, and select the appropriate DB
# maximum of 3 attempts, force script exit after 3rd fail
#
sub db_connect {
my $dsn = "DBI:mysql:database=" . $db_name . ";host=" . $db_host;
my $retry = 3;
while ( $retry ) {
my $dbh;
# establish the DB connection...
$dbh = DBI->connect( $dsn, $db_user, $db_pass );
if ( ! defined $dbh ) {
_syslog( 'err', "DB connection to " . $db_host . " failed." );
$retry--;
next;
}
return $dbh;
}
# if we reach this point, we're out of retries
# log error
_syslog( 'err', "$0: FATAL: connect retry limit reached. " );
return undef;
}
#
# syslog wrapper
#
sub _syslog {
my ($lvl, $msg) = @_;
print "\n[$lvl] $msg";
return 1;
}
[/codesyntax]
NEW PROCESS
* Basically you have a wordlist, you want to convert that wordlist into the hash values using hash_gen.pl
* to convert to RawMD5
[codesyntax lang=”bash”]
./hash_gen.pl RawMD5 -nocomment -count 122498916999 < WordList.txt > WordList_rawmd5.txt
[/codesyntax]
* The above creates a new file called WordList_rawmd5.txt
* We convert that file to a version used by our perl import_passwords.pl script to import passwords….
Copy the following code into a file named convert-hash_gen_2_import_passwords_format.sh
[codesyntax lang=”bash”]
#!/bin/bash
# By Ed Wiget
# This script converts a hash_gen.pl wordlist into a format suitable for import_passwords.pl
#####################################
## NOTES
#####################################
#####################################
## hash_gen examples
#####################################
# u121316-RawMD5:05f0e652fc4af073167b88a8f13be12c:121316:0:1910secure::
# u121317-RawMD5:50d2db949ac3419aa9cf7b7f18677734:121317:0:1910so72::
# u121318-RawMD5:bc0d2e8628f8d00fe165aa220b90c897:121318:0:1910sonne::
#####################################
## import_passwords.pl final examples
#####################################
# 85e2d744300881f7f57c741297787a8,01jesus01,1
# abaf595e923ce4b790d8619e1949c83f,01jetta,1
# 0bc857aac288bc39279e1d41b06eb229,01jh29,1
# This gives us
# cat masked_found_hash_gen_rawmd5.txt | awk -F- '{print$2}' | awk -F: '{print$2","$5","$1}' > /path/to/output
# 0bc857aac288bc39279e1d41b06eb229,01jh29,RawMD5
# while this gives us just the types name
# cat masked_found_hash_gen_rawmd5.txt | head -1 | awk -F- '{print$2}' | awk -F: '{print$2","$5","$1}' | awk -F, '{print$3}'
# RawMD5
# so don't forget to convert field 3 to a numeral
######################################
#####################################
## VARIABLES
#####################################
# the db user name
DB_USER=DB_USERNAME
# the db uses password
DB_PASS='DB_PASSWORD'
# the db host
DB_HOST=127.0.0.1
# the db name
DB_NAME=DB_NAME
if [ "$1" == '' ]; then
echo "What is the full path to the file to convert?"
read CFILE
else
CFILE=$1
fi
# first we should validate the types actually exists in the db
# we get the types name to query the db
TYPES_NAME=`cat ${CFILE} | head -1 | awk -F- '{print$2}' | awk -F: '{print$2","$5","$1}' | awk -F, '{print$3}'`
# now we have the types name, we can see if it exists in the db
TYPES_NAME_EXISTS=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be "select name from types where name='${TYPES_NAME}';" | grep -v id | grep -v name | wc -l`
if [ "${TYPES_NAME_EXISTS}" = "1" ]; then
echo "We already have the types ${TYPES_NAME} in the db, lets get the id now"
TYPES_NAME_ID=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be "select id from types where name='${TYPES_NAME}';" | grep -v id`
# now we have the id number, we can convert the file and inline sed the name for the id
cat ${CFILE} | awk -F- '{print$2}' | awk -F: '{print$2","$5","$1}' | sed -e "s/${TYPES_NAME}/${TYPES_NAME_ID}/g" > ${CFILE}-${TYPES_NAME}
#echo "Your file is now located at ${CFILE}-${TYPES_NAME}"
else
echo "${TYPES_NAME} does not exist in the db, lets set it up and get the id now"
mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be "insert into types (name) VALUES (\"${TYPES_NAME}\");"
# now we have the name inserted into the db, lets get its id
TYPES_NAME_ID=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} ${DB_NAME} -Be "select id from types where name='${TYPES_NAME}';" | grep -v id`
# now we have the id number, we can convert the file and inline sed the name for the id
cat ${CFILE} | awk -F- '{print$2}' | awk -F: '{print$2","$5","$1}' | sed -e "s/${TYPES_NAME}/${TYPES_NAME_ID}/g" > ${CFILE}-${TYPES_NAME}
# echo "Your file is now located at ${CFILE}-${TYPES_NAME}"
fi
echo "all is done"
echo ""
echo "Your file is now located at ${CFILE}-${TYPES_NAME}"
[/codesyntax]
* once we have the file converted, it will be named WordList_rawmd5.txt-RawMD5
* now we run the import db script like this:
[codesyntax lang=”bash”]
./import_passwords.pl WordList_rawmd5.txt-RawMD5
[/codesyntax]
Leave a Reply
You must be logged in to post a comment.