Hello,

I record the time and IP addresses each time a form is submitted, successfully or unsuccessfully. So the database table for form submission consists of ID, DATETIME, and IPADDRESS.

I've been trying to code on limiting the form usage but I've been unsuccessful.

This is what I'm trying to accomplish:

The form can be submitted successfully once per IP address per hour;
and, it can be submitted unsuccessfully thrice per IP address per hour (i.e. 3 failed attempts).

Could anyone please help me with this?

Thanks

    Are you aware of that IP addresses can be shared between users. This means that once success per IP and hour doesn't necessarily mean one success per user.

    SELECT COUNT(ip) AS submit_count, success
FROM some_table
WHERE ip = @some_ip
GROUP BY success
      johanafm;10989924 wrote:

      Are you aware of that IP addresses can be shared between users. This means that once success per IP and hour doesn't necessarily mean one success per user.

      SELECT COUNT(ip) AS submit_count, success
FROM some_table
WHERE ip = @some_ip
GROUP BY success

      I understand that. What do you suggest would be a better way of doing it?

      Btw, your code does not consider the hourly time limit that I'm trying to impose.

      Thanks

        fr600;10989928 wrote:

        I understand that. What do you suggest would be a better way of doing it?

        The only way you can be certain in this regard is to have them create accounts so that you can keep track of them. And well, that only goes so far as to let you know it's one success / 3 failures per account, not person. But if you limit one account per email address, you will reduce this happening.
        You might also keep an IP log for each account, and in case you find that one IP address is used for several accounts, you can always check up on that IP to determine wether it's likely to be used by multiple people or not, and optionally put it in a separate banned_ip table. But care has to be taken under such circumstances. I once blocked the ip-address used by everyone connection via WAP for a certain cellphone operator...

        fr600;10989928 wrote:

        Btw, your code does not consider the hourly time limit that I'm trying to impose.

        Just add that part to the where clause

        AND t + INTERVAL 1 HOUR > NOW()

          Thanks. I'll adjust accordingly for registered and unregistered users.

          The interval code...

          When I don't use it, I get a count of all records under an IP address, but when I use it, I get 0 even though there's one record within the last hour.

          :S

            Where 't' is the identifier for the appropriate field and that field is of type DATETIME?

              johanafm;10989943 wrote:

              Where 't' is the identifier for the appropriate field and that field is of type DATETIME?

              I'm using ldatetime instead of t and yes, it is DATETIME type.

              Here's my code:

              SELECT COUNT(*) FROM logs WHERE lipaddress = '$lipaddress' AND ldatetime + INTERVAL 1 HOUR > NOW()

              I have tried several variations but it's not working.

                Are you certain the server is using the same time as you are? What happens if you

                SELECT NOW() AS now, ldatetime + INTERVAL 1 HOUR plus_one_hour, ldatetime + INTERVAL 1 HOUR > NOW() as comparison

                Also, you should include the field inidcating success/failure and group by this field. Otherwise, if you get a count of 1 or 2, you do not know if one of those is a success.

                SELECT ..., success
FROM ...
WHERE ...
GROUP BY success
ORDER BY success DESC

                Assuming success = 1 means success and 0 means failure. If you inspect the first row and it contains success = 1, there is at least 1 successful attempt within the last hour, and you should reject the request. If it's not, you are looking at failures, which means checking the counted records. If it's at your threshold of 3, you rejeect the request.

                  You got it! Time is the issue.

                  I'm using:

                  date_default_timezone_set('UTC');

                  to override the timezone.

                  I believe mysql is taking current server time whereas php is converting it to UTC.

                  Without disabling UTC or manually calculating the difference and adding it to mysql_query, is there a way to fix it?

                  Thank you so much.

                    You can choose any timezone you want to not matter where your machine(s) is(are). I would however recommend that you use UTC, since then you know what timezone to set for the next server you move to, no matter where it is. The other way to go is to use whatever tz your server is in physically.

                    But PHP and MySQL should most definitely be using the same timezone or you will either run into problems or you will have to keep converting back and fourth.

                      After going through what you posted again, I found MySQL stores data in UTC and PHP uses it too, but NOW() in mysql_query considers local time which is not UTC.

                      Is there any way to fix that without editing the config file?

                      Thanks

                      EDIT:

                      I'm using

                      mysql_query("SET SESSION TIME_ZONE='UTC'");

                      but I wish I knew a better way of doing this.

                        $timestamp = gmdate("Y-m-d H:i:s", time());

                        Instead of using NOW(), $timezone did the job and there's no need to set session timezone.

                        Sometimes we (I) make things so complicated that we (I) don't see an easy solution.

                          Write a Reply...