0

I have an SQL database that I access using Python and pgdb. I plan on opening access to a group of users, so I would like to incorporate a login function that takes in a username and password that is checked against the database's information on the user. I don't use Python that much, so this has taken me a considerable amount of time with little results. I've found helpful threads on this using PHP but not in Python unfortunately, so I decided to start this thread.

Below is my code. Basically, I would like a login function that returns true if the user exist with a given password. The code below is unresponsive.

Am I setting up the .execute incorrectly with the parameter references? Or is the if statement wrong? 

def Login(username,password):

    cursor.execute('select %s from db') % username
    dbuser = cursor.fetchone()
    if dbuser == username:
        cursor.execute('select %s from db') % password
        dbpass = cursor.fetchone()
        if dbpass == password:
            return "True"
       else:
            return "Password is incorrect."

   else:
       return "Username is incorrect."

Any help is greatly appreciated! Sorry for the newb question...

Captain Murphy
  • 815
  • 3
  • 15
  • 23
  • Don't forget to encrypt the passwords. – mikerobi Mar 22 '12 at 20:16
  • 1
    Better to generate and store a hashed value of a salt and password, then compare hashed values at login. More info: http://www.dshield.org/diary.html?storyid=11110, python example: http://stackoverflow.com/questions/9594125/salt-and-hash-a-password-in-python – Bryan Mar 22 '12 at 20:50

2 Answers2

1

Three things...

First, the SQL you're using doesn't make any sense. Basic syntax is select columns from table where stuff is true. You are trying select <a username> from table where the username is supposed to be a column name which doesn't make any sense. I presume you were trying to something more like select username from db where password=%s

Second, telling the user that the username doesn't exist allows someone to build a list of all the valid user names through trial and error. Instead, tell them generically that the credentials don't match and don't tell them which part is broken.

Third, you are embedding your user input (username and passwords are input) directly into your SQL. Please do some reading on SQL Injection and use parameterized queries instead to prevent your users from doing very very bad things to your site.

Edit

One final consideration. This all implies that you're storing passwords in plain text in your table. Do some research on password hashing as well.

gfortune
  • 2,389
  • 12
  • 12
  • Thanks for the response. The column names are also "username" and "password". All I'm really looking for is a simply login function for about half a dozen people to access a non-sensitive database. – Captain Murphy Mar 22 '12 at 19:59
  • Right, but the code you provided is putting the user input for username and password in as the column names. aka, `select gfortune from table`. That's clearly not going to work. You're probably looking for something like `select username from table where username='gfortune' and password='blah blah'`. SQL Injection definitely comes into play though. – gfortune Mar 22 '12 at 20:03
  • Thanks! But I'm really just looking for a basic function that works, no need for password hashing, etc. For example, how do I turn the select username from table where username='gfortune' into code that is usable within a function? That seems to be my main problem. Instead of searching the string 'gfortune', I'd like it to search the function input "username". – Captain Murphy Mar 22 '12 at 20:16
  • 2
    Use a parameterized query. If you're not comfortable with SQL in general, please read a basic primer on SQL. Second google link for "pgdb parameterized query" is pretty useful... http://cs-people.bu.edu/mp/cs105c/lab07/python_db.pdf Keep in mind there is some expectation that you will put in some effort on your part when we point you in the right direction. – gfortune Mar 22 '12 at 20:33
1

You are attempting to select a COLUMN from a database, that is equal to the username variable passed to the Login function.

Your syntax is utterly incorrect.

Read this PostgreSQL link on SQL Select for the correct syntax.

Essentially, what you need to do is select the full column (by column name) from your database, and add in a WHERE clause that uses or matches the username variable

cursor.execute('SELECT username,password FROM db WHERE username=%s') % (username)

Disclaimer: The above is insecure code for SQL injection prevention. It is only shown to help you understand the correct syntax to use, in place of what the current question has asked.

PenguinCoder
  • 4,174
  • 24
  • 37