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.

Answer 1

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.

Answer 2

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:

sha1$8ac10f$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.