Dont allow multiple registrations or automated script problem

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]

Comment on Dont allow multiple registrations or automated script problem Download Code

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 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]

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

CGI::Application

CGI::Application::Plugin::RateLimit

-sam

[reply]

Re: Dont allow multiple registrations or automated script problem
by grep (Monsignor) on Oct 20, 2006 at 16:11 UTC

epoch seconds

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]

A reply falls below the community's threshold of quality. You may see it by logging in.


Welcome to the Monastery
	PerlMonks