Creating an "anti-botting" system by adding values into the database

Question

I have no clue on how to make this to work, so I'm just looking for ideas currently, let me explain what I want to make; Let's say I have a button named "click". Everytime I press this button, it adds 1 to the database, so when I have pressed it 10 times, it holds the value "10" in the database column named "clicks". This is already made though. I just need something that can check how fast I was pressing the button the 10 times to make an anti botting system. So I thought that making a column in the database named "time" this "time" column resets itself every 10 seconds and adds 1 to the column everytime the "click" button is pressed. Then I can make something in my PHP script like this: If time > 200 DELETE USER etc.

So if a someone should be able to get the "time" column on 200 before it resets itself, it must be a bot pressing since it is not human-like to press that many times in only 10 seconds.

I have read something about TIMESTAMP and INTERVAL but I cannot get it to woth the way I thought. I simply just need a column that will delete all data inside it, after a certain time.

Any suggestions are welcome! Thanks in advance.

I've come this far with the code:

if (isset($_POST['djahff'])) { // The button
require_once('connectvars.php'); // The DB connection
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    $idsession = $_SESSION['id'];
    $result = mysqli_query($dbc, "INSERT INTO pbclickslogin SET cps=1, id='$idsession' ON DUPLICATE KEY UPDATE cps=cps+1"); // Adding 1 to the column "cps" where username=$username

    $data = mysql_query("SELECT TIMESTAMPDIFF(SECOND, NOW(), mark) > 10 FROM pbclickslogin WHERE username='$username'") // Checking if the difference from mark and now is 10 seconds I assume?
    or die(mysql_error());
while($info = mysql_fetch_array( $data )) {
$result = mysqli_query($dbc, "UPDATE pbclickslogin SET cps='0', mark=NOW() WHERE username='$username'"); // Updating cps and mark because the difference from mark and now was 10 seconds.
 }
}

The site updates everytime the button is pressed, it's a button made with input type "submit".

Uhmm I didn't really understand you fully Algomorph but I assume it was because of my code posting mistakes. Sorry I am still new on this forum. Anyway I guess I have an update statement here etc.? Are there still the same problems as you mentioned before?

Ps. Thanks for noticing me about the forum format

I have now added a little more code:

if ($info[0] == 1) {
    $result = mysqli_query($dbc, "UPDATE pbclickslogin SET cps='0', mark=NOW() WHERE username='$username'"); // Updating cps and mark because the difference from mark and now was 10 seconds.
}

The code however, never Update now. I tried with ($info['0'] == 1) aswell but with no luck unfortunatly. Besides that I don't really understand this now, what is $info[0]? Is it finding something inside the database that is 0 or something? I usually only use $info when calling on columns. Thanks again man :)

Another few changes were added, the code we've got now is as followed:

if (isset($_POST['djahff'])) { // The button

require_once('connectvars.php'); // The DB connection
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    $idsession = $_SESSION['id'];
    $result = mysqli_query($dbc, "INSERT INTO pbclickslogin SET cps=1, id='$idsession' ON DUPLICATE KEY UPDATE cps=cps+1"); // Adding 1 to the column "cps" where username=$username

    $data = mysql_query("SELECT TIMESTAMPDIFF(SECOND, mark, NOW() ) > 10 FROM pbclickslogin WHERE id='$idsession';") // Checking if the difference from mark and now is 10 seconds I assume?
    or die(mysql_error());
while($info = mysql_fetch_array( $data )) {
if ($info[0] == 1) {
    $result = mysqli_query($dbc, "UPDATE pbclickslogin SET cps='0', mark=NOW() WHERE id='$idsession'"); // Updating cps and mark because the difference from mark and now was 10 seconds.
}
 }
}

to Algomorph: Why did you change NOW() ) > 10 to NOW() ) > 1000 by the way? Plus: The problem I am having at the moment is that when I press the button "djahff" it updates the timestamp "mark" but not the integer "cps".

Thanks :-) - We're getting closer!

Answer 1

if (isset($_POST['djahff'])) { // The button

require_once('connectvars.php'); // The DB connection
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    $idsession = $_SESSION['id'];
    $result = mysqli_query($dbc, "INSERT INTO pbclickslogin SET cps=1,       id='$idsession' ON DUPLICATE KEY UPDATE cps=cps+1"); // Adding 1 to the column "cps" where username=$username

    $data = mysql_query("SELECT TIMESTAMPDIFF(SECOND, NOW(), mark) > 10 FROM pbclickslogin WHERE username='$username'") // Checking if the difference from mark and now is 10 seconds I assume?
    or die(mysql_error());

while($info = mysql_fetch_array( $data )) { $result = mysqli_query($dbc, "UPDATE pbclickslogin SET cps='1', mark=NOW() WHERE username='$username'"); // Updating cps and mark because the difference from mark and now was 10 seconds. } }

Here is the code that I understanded from what Algomorph told me about. The problem is just that the user clicks on the button "djahff" a lot times past these 10 seconds. And everytime the button is pressed, the "mark" column updates back to what the time is now.

Besides that everytime works fine :) Any suggestions?
@Algomorph

• Peter

Answer 2

I'm not sure which DBMS you're using, but you could do exactly what you described the following way. Make two columns:

• integer [clicks]
• timestamp [mark]

When the user clicks, check [mark] and compare it to current date & time. If there is a more than 10 second difference, set [clicks] to 1 and mark to the current date & time (NOW() function in mysql, current_timestamp() function in PostgreSQL).

Otherwise, check [clicks]. If it exceeds 200, kick the user out. Otherwise, increment it by one.