Nik has asked for the wisdom of the Perl Monks concerning the following question:

Hello, iam trying to check if a user already exist in a databse and he didnt try to resubmit registraton data from the same ip address withing the last 5 mins. I was successfull only to the first part. Can you plz help with the 3nd part? i am storing tha date and time of the registrtaion in a mysql datatime filed. Here is the code: 
#**********CHECK IF USER EXISTS & DID NOT REPOST & ADD USER TO DATABAS
+E*********
$select = $dbh->prepare( "SELECT username, date FROM users WHERE usern
+ame=?" );
$select->execute( $username );

**here how to check if he just resubmitted data multiple times in the 
+last 5 mins withing the same ip address or his maybe just an automate
+d script? I wan tto avoid registering hi a 1000 times :-)

my $user  = $username;
my $realm = "You Must Be Registered In Order To Post!";
my $pass  = $password;

open(FILE, ">>path/to/password/file") or die $!;
     print FILE "$user:$realm:" . Digest::MD5::md5_hex("$user:$realm:$
+pass") . "\n";
close(FILE);

$select = $dbh->prepare( "INSERT INTO users (username, password, email
+, date, host) VALUES (?, ?, ?, ?, ?)" );
$select->execute( $username, $password, $email, $date, $host);

[download]

Replies are listed 'Best First'.
Re: Dont allow multiple registrations or automated script problem
by ptum (Priest) on Oct 20, 2006 at 16:11 UTC

    So, assuming you store the user's information in your database on the first registration attempt, all you need to do is select from that same table with a where clause that identifies the host and a range of timestamp values. In Oracle, that would be something like this:

    "SELECT * from USERS where host = ? and date >= (sysdate - 0.00139)"

    [download]

    If you get any rows, then someone has tried to register from that same host in the last 5 minutes. If you just want to prevent the same username, add 'username' in the where clause. By the way, that's a really bad name for a date column.

[reply]
[d/l]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Dont allow multiple registrations or automated script problem
by samtregar (Abbot) on Oct 20, 2006 at 17:39 UTC
[reply]
Re: Dont allow multiple registrations or automated script problem
by smammy (Novice) on Oct 20, 2006 at 16:03 UTC

    How about this? (Not tested.)

    $sth = $dbh->prepare("SELECT COUNT(*) FROM users WHERE host=? AND date
+>DATE_SUB(NOW(), INTERVAL 5 MINUTE)");
$sth->execute($host);
$row = $sth->fetchrow_arrayref
    or die "some kinda error";

if ($row->[0]) {
    # there's already a user in the database which was
    # registered from this IP, less than five minutes ago.
    # fail in some way.
}

    [download]
[reply]
[d/l]
      Thank you that worked but i wonder also if i should let generally people to register from the same ip address that another user used to register in the past.
      What do you think?
[reply]

        I think you should. Consider a few scenarios:

        • My wife and I share a router, which shares an Internet connection. That means that while we are two unique users, we will appear to the outside world to have the same IP address.
        • Consider users of larger ISPs such as AOL, where often all users within a particular region will share the same IP address from the perspective of the outside world. If you allow only one registration per IP address, you'll block a lot of other people.
        • Consider dialup users, who get a different IP address every time they dial in to their ISP. That's a whole different problem. Here, the same person could register, hang up, dial in again, and re-register with his new IP. Oh, and guess what. The next time another user from that ISP dials in and gets an IP already used by the first registrant, he will be denied registration access! Two problems in one.

        Dave

[reply]
Re: Dont allow multiple registrations or automated script problem
by grep (Monsignor) on Oct 20, 2006 at 16:11 UTC
    It depends on how you stored the date and time. Is the field a MySQL DATETIME or TIMESTAMP? Or did you store epoch seconds in a INT field? If you have a choice, for a simple compare like this I would store epoch seconds and then you can test for 5 minutes easily. 
    my $sth = $dbh->prepare($your_sql_statement);
my $row = $sth->fetchrow_hashref;
print "not good\n" if ($row->{date} + (60*5) < time);
$sth->finish;

    [download]

    If you'll be doing queries on the 'date' field (more than sorting). Then move over to the DATETIME or TIMESTAMP data type, where it will be good for the DB to know it's a time and can do SQL things with it.



    grep
    One dead unjugged rabbit fish later
[reply]
[d/l]
      Actually i forgot to mention that my mysql date field its od type 'datetime' and not epoch or timestamp because as you said inddeed iam making other things as well with time except of comparing.
      I dont know wthough it the filed has to be datetime or timestamp, perhpas ouc an give an idea.
      Now that it is of a datetime type how would i check of the 5 mins delay?
[reply]
        Follow smammy's solution. If a DB can do it internally in SQL, it will almost always be faster than trying to pull it out and working on it.


        grep
        One dead unjugged rabbit fish later
[reply]

Back to Seekers of Perl Wisdom