Store Information In MySQL Database with Python

Question

I am working on a huge project. I have been working on it for a while now, and decided to "up" the security on the way the software handles data. I already know how to encrypt and decrypt the data strings using DES encryption, but what I am not sure about is where to put that encrypted data. I would like to store everything in a MySQL database, but haven't quite figured out how to work with the database. I have done some Googling, but to no prevail.

I need to store the following for each account:

Username
Password
Sec. Question
Sec. Answer
Email
List of keywords
List of web URLs

I think storing this information would be like creating tables in the database, but I'm not sure. Maybe a table for the user, then more tables for the rest inside the table for the user? I am not sure how to work with MySQL databases from Python, so any help will be greatly appreciated.

Sorry for the late edit, I just realized I needed to clean it up a little.

• icktoofay: Please hash, not encrypt passwords, and salt the hashes at that.
• Zac Brown: Using MD5? I heard that was good, but not decryptable. I need to be able to read the raw data again, for the password recovery feature.
• Alex Martelli: You can find a huge number of excellent Python+MySQL tutorials by google.com/search?q=python+mysql+tutorial -- have you looked at a few of them, and what was missing to get you going?
• FogleBird: Forget password recovery, just let them reset the password after answering the question.
• Zac Brown: Well, I wasn't actually sure how it all worked from the Python perspective. I know about how MySQL works, but not sure how to "interact" with the database from Python. I also wasn't sure how I would separate the accounts, but thanks to Alex JL's comment below, I have a good idea of how to separate the accounts. I"m still not sure how to do the simple things, like connect, write the data to the proper tables and then read it back later. ------------ Sorry For The Edit, I missed A Comment ---------------- I think I like that idea, FogleBird. I will do it that way!

Answer 1

Here's an example of what the schema could look like:

user
 user_id (PK)
 username (char)
 password (char)
 security_question_id (FK)
 security_answer (char)
 email_address (char)
security_question
 security_question_id (PK)
 question (char)
keyword
 keyword_id (PK)
 keyword (char)
user_keyword
 user_keyword_id (PK)
 user_id (FK)
 keyword_id (FK)
url
 url_id (PK)
 user_id (FK)
 url (char)
PK = Primary Key
FK = Foreign Key
char = varchar of some max length that you define

Assumptions:

• There is a standard list of security questions to choose from.
• A lot of users may have the same keyword, so they're put in their own table.
• URLs are more unique, so just store the url and user_id together. If you want, you could change this to the shared pattern the keywords use.
• Nothing is nullable, all fields are required.

As I commented, I recommend hasing passwords (with a salt). No need to be recoverable, they can reset the password. I've mimicked Django's password style in the past:

sha18ドルac10f$a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

That's: hash method, salt and password hash, delimited by $ characters. You can just generate a random string as salt. Add it to the password before hashing. Store a string like that one shown in the password field. To test a password for correctness, extract those 3 fields, append the salt to the user-entered password, apply the hash and compare to the hash (3rd field) in the database. If they match, the password is correct.

I would personally use SQLAlchemy.

• Zac Brown: I thank you for the effort I know was put into this post, but I'm still not sure how to put this into working code. I have been reading a small tutorial about using the MySQLdb module here, kitebird.com/articles/pydbapi.html#TOC_1 , but so far I'm not where I need to be.
• FogleBird: You need to break down the problem into smaller questions. We can't just write the code for you.
• Zac Brown: Ok, here is my main problem. I found some great tutorials about how to use MySQLdb module, but when trying to import the module, It says that it can't find <b><i>_mysql</i></b> module. I did some Googling, but couldn't seem to find the file. Any ideas? Now, as far as the password encryption, I was using DES, is this not good?

Answer 2

You have two choices, essentially:

• Use the mysqldb module
• Use an Object Relation Manager (ORM) like SQLAlchemy

Do you know how to make a table, in general?

For mysql, you'd make your example DB by logging in, creating a database, selecting (use) that database, then

create table account(
 username varchar(32),
 password varchar(128),
 sec_question varchar(512),
 sec_answer varchar(128),
 email_address varchar(128)
 );

The list or URLs and keywords would be best done as separate tables, though describing that is out of the scope of this answer!

Note that's not a comprehensive or the best way to do your table necessarily, and not the way you'd probably create it with an ORM, but is just an example.

To create a secure and credible password system, you're going to need to do a lot of research on that. I'm sure people around here would be happy to help you understand hashing, etc.

Here's a link to a good article on using the MySQLdb module.