{"id":729,"date":"2006-08-25T16:03:03","date_gmt":"2006-08-25T06:03:03","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/2006\/08\/25\/storing-passwords-securly-in-mysql\/"},"modified":"2006-08-25T16:03:03","modified_gmt":"2006-08-25T06:03:03","slug":"storing-passwords-securly-in-mysql","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/08\/25\/storing-passwords-securly-in-mysql\/","title":{"rendered":"Storing Passwords (securly) in MySQL"},"content":{"rendered":"

Frank talks about Storing Passwords in MySQL<\/a>. He does, however, miss something that’s really, really important<\/strong>. I’m talking about the salting of passwords.<\/p>\n

If I want to find out what\u00c2\u00a0 5d41402abc4b2a76b9719d911017c592 or 015f28b9df1bdd36427dd976fb73b29d MD5s mean, the first thing I’m going to try is a dictionary attack (especially if i’ve seen a table with only user and password columns). Guess what? A list of words and their MD5SUMS can be used to very quickly find what these hashes represent.<\/p>\n

I’ll probably have this dictionary in a MySQL database with an index as well. Try it yourself – you’ll probably find a dictionary with the words “hello” and “fire” in it to help. In fact, do this:<\/p>\n

mysql> create table words (word varchar(100));
\nQuery OK, 0 rows affected (0.13 sec)
\nmysql> load data local infile ‘\/usr\/share\/dict\/words’ into table words;
\nQuery OK, 98326 rows affected (0.85 sec)
\nRecords: 98326\u00c2\u00a0 Deleted: 0\u00c2\u00a0 Skipped: 0\u00c2\u00a0 Warnings: 0<\/p>\n

mysql> alter table words add column md5hash char(32);
\nQuery OK, 98326 rows affected (0.39 sec)
\nRecords: 98326\u00c2\u00a0 Duplicates: 0\u00c2\u00a0 Warnings: 0<\/p>\n

mysql> update words set md5hash=md5(word);
\nQuery OK, 98326 rows affected (3.19 sec)
\nRows matched: 98326\u00c2\u00a0 Changed: 98326\u00c2\u00a0 Warnings: 0
\nmysql> alter table words add index md5_idx (md5hash);
\nQuery OK, 98326 rows affected (2.86 sec)
\nRecords: 98326\u00c2\u00a0 Duplicates: 0\u00c2\u00a0 Warnings: 0
\nmysql> select * from words where md5hash=’5d41402abc4b2a76b9719d911017c592′;
\n+——-+———————————-+
\n| word\u00c2\u00a0 | md5hash\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 |
\n+——-+———————————-+
\n| hello | 5d41402abc4b2a76b9719d911017c592 |
\n+——-+———————————-+
\n1 row in set (0.11 sec)
\nmysql> select * from words where md5hash=’015f28b9df1bdd36427dd976fb73b29d’;
\n+——+———————————-+
\n| word | md5hash\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 |
\n+——+———————————-+
\n| fire | 015f28b9df1bdd36427dd976fb73b29d |
\n+——+———————————-+
\n1 row in set (0.00 sec)
\n$EXCLAMATION I hear you go.<\/p>\n

Yes, this is not a good way to “secure” passwords. Oddly enough, people have known about this for a long time and there’s a real easy\u00c2\u00a0 solution. It’s called salting.<\/p>\n

Salting is prepending a random string to the start of the password when you store it (and when you check it).<\/p>\n

So, let’s look at how our new password table may look:<\/p>\n

mysql> select * from passwords;
\n+——+——–+———————————-+
\n| user | salt\u00c2\u00a0\u00c2\u00a0 | md5pass\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 |
\n+——+——–+———————————-+
\n| u1\u00c2\u00a0\u00c2\u00a0 | ntuk24 | ce6ac665c753714cb3df2aa525943a12 |
\n| u2\u00c2\u00a0\u00c2\u00a0 | drc,3\u00c2\u00a0 | 7f573abbb9e086ccc4a85d8b66731ac8 |
\n+——+——–+———————————-+
\n2 rows in set (0.00 sec)
\nAs you can see, the MD5s are different than before. If we search these up in our dictionary, we won’t find a match.<\/p>\n

mysql> select * from words where md5hash=’ce6ac665c753714cb3df2aa525943a12′;
\nEmpty set (0.01 sec)<\/p>\n

instead, we’d have to get the salt and do an md5 of the salt and the dictionary word and see if the md5 matches. Guess what, no index for that! and with all the possible values for salt, we’ve substantially increased the problem space to construct a dictionary (i won’t go into the maths here).<\/p>\n

mysql> create view v as select word, md5(CONCAT(‘ntuk24′,word)) as salted from words;
\nQuery OK, 0 rows affected (0.05 sec)<\/p>\n

mysql> select * from v where salted=’ce6ac665c753714cb3df2aa525943a12’;
\n+——-+———————————-+
\n| word\u00c2\u00a0 | salted\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 |
\n+——-+———————————-+
\n| hello | ce6ac665c753714cb3df2aa525943a12 |
\n+——-+———————————-+
\n1 row in set (2.04 sec)<\/p>\n

mysql> create or replace view v as select word, md5(CONCAT(‘drc,3′,word)) as salted from words;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

mysql> select * from v where salted=’7f573abbb9e086ccc4a85d8b66731ac8’; +——+———————————-+
\n| word | salted\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 |
\n+——+———————————-+
\n| fire | 7f573abbb9e086ccc4a85d8b66731ac8 |
\n+——+———————————-+
\n1 row in set (2.12 sec)<\/p>\n

So we’ve gone from essentially instantaneous retreival, to now taking about 2 seconds. Even if I assume that one of your users is going to be stupid enough to have a dictionary password, It’s going to take me 2 seconds to check each user<\/span> – as the salt is different for each user! So it could take me hours just to find that user. Think about how many users are in your user table – with 1000 users, it’s over 1\/2hr. For larger systems, it’s going to be hours.<\/p>\n","protected":false},"excerpt":{"rendered":"

Frank talks about Storing Passwords in MySQL. He does, however, miss something that’s really, really important. I’m talking about the salting of passwords. If I want to find out what\u00c2\u00a0 5d41402abc4b2a76b9719d911017c592 or 015f28b9df1bdd36427dd976fb73b29d MD5s mean, the first thing I’m going … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1,13,14],"tags":[],"class_list":["post-729","post","type-post","status-publish","format-standard","hentry","category-general","category-memberdb","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-bL","jetpack-related-posts":[{"id":847,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/06\/19\/my-top-5-wishlist-for-mysql\/","url_meta":{"origin":729,"position":0},"title":"My Top 5 Wishlist for MySQL","author":"Stewart Smith","date":"2007-06-19","format":false,"excerpt":"I'm going and stealing Jay's idea (who stole it off Brian Duff... but his was for Oracle so obviously doesn't count :) So, my five wishes for MySQL Are: 5. Six-monthly release cycles Getting a release out there takes way too long. There's a variety of reasons, but seeing the\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1655,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/06\/09\/drizzle-pluggable-metadatastore-or-no-table-definition-file-on-disk\/","url_meta":{"origin":729,"position":1},"title":"Drizzle pluggable MetadataStore (or: no table definition file on disk)","author":"Stewart Smith","date":"2009-06-09","format":false,"excerpt":"My code is shaping up rather nicely (see https:\/\/code.launchpad.net\/~stewart\/drizzle\/discovery) and I'm planning to submit a merge-request for it later today. I'm about to commit code that implements a MetadataStore for the ARCHIVE engine. This means that for ARCHIVE tables, you only have the .ARZ file on disk. The table definition\u2026","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1251,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/06\/goodbye-frm-or-at-least-the-steps-to-it\/","url_meta":{"origin":729,"position":2},"title":"Goodbye FRM (or at least the steps to it)","author":"Stewart Smith","date":"2008-11-06","format":false,"excerpt":"Since before MySQL was MySQL, there has been the .FRM file. Of course, what it really wanted to be was \".form\" -\u00c2\u00a0 a file that stored how to display a form on your (green) CRT. Jan blogged earlier in the year on this still being there, even in MySQL 5.1\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":729,"position":3},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":446,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/08\/03\/fancy-shortcuts-to-mysql-bugs\/","url_meta":{"origin":729,"position":4},"title":"Fancy shortcuts to MySQL Bugs","author":"Stewart Smith","date":"2005-08-03","format":false,"excerpt":"So Elliot Murphy is talking about QuickSearch shortcut for bugs.mysql.com which is quite useful if you use Firefox. However, I'm using Epiphany (which is based on the same rendering engine, but is a bit more GNOMEy - and besides, i've been using it for a while, i have all my\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":729,"position":5},"title":"Some current MySQL Architecture writings","author":"Stewart Smith","date":"2014-09-19","format":false,"excerpt":"So, I've been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven't really found anything. I mean, there's the (huge and very detailed)\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/729","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=729"}],"version-history":[{"count":0,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/729\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=729"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=729"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=729"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}