Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

DBI & MySQL Login Test

by swordfish (Initiate)
on Aug 25, 2009 at 03:23 UTC ( #790964=perlquestion: print w/ replies, xml ) Need Help??
swordfish has asked for the wisdom of the Perl Monks concerning the following question:

Hi PERL Monks,
I'm new to the DBI module and new to perlmonks.org. The DBI module is really nice.
You can do Database queries with MySQL, among others, but my problem is trying to match my query values with the user input.
This is my query below, but needs help.

### DECLARE VARIABLES my $uname = $FORM{'req_username'}; my $pword = $FORM{'req_password'}; ### DEFINE A MySQL QUERY my $sql = qq{SELECT * FROM $tablename WHERE username = '$uname' AND password = '$pword'}; my $sth = $dbh->prepare($sql); ### EXECUTE THE QUERY $sth->execute(); ### TEST THE USERNAME AND PASSWORD if(username ne '$uname' && password ne '$pword') # this does not work

Where do I test the username and password validity?
my validation check that I made does not work as intended.
I need some help with validation of the login form. Can anybody help me out?

Comment on DBI & MySQL Login Test
Download Code
Re: DBI & MySQL Login Test
by Your Mother (Canon) on Aug 25, 2009 at 03:55 UTC

    I can help you learn a painful lesson. Make this your next username: Robert'); DROP TABLE Users; --.

    The actual problem in your code is the single quotes. They make the $ literal so the variable is not interpolated. Next to address the bigger problem with little Bobby Tables above, read the stuff on placeholders in the DBI docs. You should *never* do SQL like your example. The docs, and any number of nodes here, will explain why in detail.

Re: DBI & MySQL Login Test
by AnomalousMonk (Abbot) on Aug 25, 2009 at 03:57 UTC
    if(username ne '$uname' && password ne '$pword')
    Single-quotes do not interpolate, so the string  '$uname' above is literally  'dollar-u-n-a-m-e' and not whatever the  $uname lexical holds. Likewise  '$pword'.

    The other thing I wonder about is just what  username and  password are? Function calls? Metasyntactic variables? It's always best to post actual code, especially when you say "this does not work".

      swordfish:

      Additionally, in this case the answer isn't "switch to using double quotes". Since you have a variables, just use them without quotes.

      ...roboticus
Re: DBI & MySQL Login Test
by astroboy (Chaplain) on Aug 25, 2009 at 04:03 UTC

    If you're checking logins against a user table, you could count the number of rows that match your u/p combination

    my $sql = q{ select count(*) from app_users where username = ? and password = ? }; if (($dbh->selectcol_arrayref($sql, undef, $username, $password)->[0] +== 1) { # We've logged in } else { # login error }

    Of course, you probably shouldn't store your passwords in plain text. Assuming you've got some sort of one way encryption going on (using Perl's crypt, or a MD5 or SHA module), you could simply replace the test with

    if (($dbh->selectcol_arrayref($sql, undef, $username, my_crypt_call($p +assword))->[0] == 1)

    By the way, don't interpolate variables in your SQL, or you'll make your login susceptible to a SQL injection attack. Always use placeholders

    Update: corrected syntax

Re: DBI & MySQL Login Test
by Sewi (Friar) on Aug 25, 2009 at 06:21 UTC
    As others said before: You (as many, many others) suffered from the biggest *SQL problem: It's a language itself and it could be hijacked.

    I experienced cases where placeholders didn't work depending on the OS, type and version of DB client libs, DB server software and following SQL statements. Here is my solution, assuming you already checked that both $uname and $pword are not empty:

    if ($sth->selectrow_array('SELECT COUNT(*) FROM '.$Tablename. 'WHERE username = 0x'.unpack('H*',$uname). ' AND password = 0x'.unpack('H*',$pword))) { # Successful login } else { # Oops, something failed }
    What happens?
    First: Be really,really,really sure that $tablename is a configured value! If this source is placed into a sub (which may get $tablename as parameter), it would be wise to do a regular expression-check on the value before using it at all!
    Second, let's look at the SQL. Assuming
  • $tablename = "users";
  • $uname = "user";
  • $pword = "123";
  • our SQL statement looks like this:
    SELECT COUNT(*) FROM users WHERE username = 0x75736572 AND password = +0x313233
    You'll notice that no SQL injection is possible any longer, because everything which is read from the user is converted to hex which couldn't harm the SQL string.
    This statement requests the number (COUNT(*)) of users matching this username and password. Assuming your "username" column is unique, a value of 0 says "There is no user with this password" while a COUNT of 1 matching row says "I found a user with this username and that password" - which is what you want.
    The latter is easy: Instead of preparing a statement, executing it and fetching the result (which was missing in your post), you could also combine all three in one command. selectrow_array returns an ARRAY containing the results for the SELECTed columns. In this case only one: The result of the COUNT.

    Last note: If you've got other things to fetch from the user DB, you could also combine this:

    my ($UserID,$UserState) = $sth->selectrow_array('SELECT ID,State FROM +'.$Tablename. 'WHERE username = 0x'.unpack('H*',$uname). ' AND password = 0x'.unpack('H*',$pword)); if ( ! defined($UserID)) { # Oops, no UserID means no user with matching username/password was f +ound } elsif ($UserState eq 'LOCKED') { # Oops, this user is not allowed to login } else { # ok, everything right, the user is validated }

    (Everything shown here is based on Sybase SQL products, but should also work on mySQL and other SQL servers. As I don't use mySQL mysqlf, I can't test it, sorry.)

      Hmm, that's one way of protecting against SQL injection, I guess. But it means that you'd need to run this kind of query for all SQL queries that use external data.

      And it means any internal users who simply want to query the db - using Crystal Reports, Business Objects or a myriad of other tools, now can't.

        Sorry, but you're wrong :-)
        I'm using the HEX representation only between the Perl script and the SQL-Server. You could still do a
        SELECT LastLogin FROM Users WHERE Username="astroboy"
        Note the " around your name, they make this a string.
        Try this with your SQL-Server:
        SELECT 1 WHERE "astroboy"=0x617374726f626f79

        PS: You could also use SQL commands to play with your string, because the SQL server trade it as a plain string, not a list of HEX-codes:

        SELECT 1 FROM Users WHERE LOWER(Username)=LOWER(0x617374726f626f79) AN +D Password=0x617374726f626f79
        This would match astroboy, ASTROBOY and AstroBoy in the DB, but not 0x617374726f626f79 or 0x617374726F626F79.

      Hello Sewi,
      Your code example of preventing SQL injection is cool.

      On my website, I want to allow users to login, but prevent any hackers from getting my subscribers usernames and passwords.
      I don't have a need to print out the username and password.
      They must remain hidden out of sight.

      As I understand it, in your SQL example you're letting SQL validate for user input. Is that correct?

      Can you elaborate on the error checking? I would like to check if a user enters the form with incorrect data.
      How do you print an error message?

        if ($uname eq '') { print "Please enter a username"; } elsif ($pword eq '') { print "Please enter a password"; } elsif ($sth->selectrow_array('SELECT COUNT(*) FROM '.$Tablename. 'WHERE username = 0x'.unpack('H*',$uname). ' AND password = 0x'.unpack('H*',$pword))) { # Successful login print "You're not logged in"; } else { print "Username or password wrong"; }
        This code doesn't give you any information if the password is wrong or the username doesn't exist, it only says "ok" or "not ok", but this is what I prefer for login forms.
        If you have different error messages for "user does not exist" and "password wrong", I could start guessing a username (as long as I get the "user does not exist" message). Then, once a valid username is known, guess the password for this user. Using one combined error message says: Maybe you got a non-existing user, maybe a wrong password, doesn't matter.

        (You need to replace the "print" lines with whatever_generates_user_output_on_your_system.)

Re: DBI & MySQL Login Test
by SFLEX (Chaplain) on Aug 25, 2009 at 08:58 UTC
    I would do it this way.
    $uname = $dbh->quote($uname); $pword = $dbh->quote($pword); my $tablename = 'members_pass'; my @row = (); # no need for the if statement, we have asked that question in MySQL my $sth = $dbh->prepare("SELECT * FROM `$tablename` WHERE `username` = $uname AND `password` = $pword LIMIT 1 ;"); $sth->execute; @row = $sth->fetchrow; $sth->finish; print "@row" if @row;


    Update: there was an error in the code. roboticus++

    What did the Pro-Perl programmer say to the Perl noob?
    You owe me some hair.
Re: DBI & MySQL Login Test
by roboticus (Canon) on Aug 26, 2009 at 10:33 UTC
    swordfish:

    You've already received a mess of good answers, so I won't belabor those points. I'm just going to offer a suggestion: Specifically, don't use '*' as a column list for a SELECT statement. It'll bite you more often than it will help you. The problems are:

    • Table changes will change your result sets, often requiring code edits.
    • You'll often be reading much more data than you need, increasing network usage.
    • You may also be pulling back information you already know (username and password in this case), further impacting network usage.
    • You can unnecessarily transmit confidential information that you don't need (again username and password).
    • A brief examination of your code won't show you which table columns are in use, making database alteration a bit more difficult.

    Generally, request what you need, and only what you need.

    ...roboticus

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://790964]
Approved by AnomalousMonk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-11-29 09:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (204 votes), past polls