Flooding user causes lost connection to Mysql server during query

[Snoader]

YaBB SE Version: 1.5.4
PHP Version:
MySQL Version:
Server Platform: Unix, Linux, or BSD
Link to Forum:

Problem Description:
Hi,

Suppose, a (banned) user is flooding a forum. He manages to send 6 or 7 calls to the forum within one second.

In the errorlog, we see this (multiple times):

Guest : x.x.x.x : Today at x:x:x
/forum/index.php?board=30;action=post;threadid=[...]
Database error: Lost connection to MySQL server during query
File: /opt/guide/www.mysite.com/HTML/forum/Sources/Security.php
Line: 50

This means, that the following query doesn't work well when a user is flooding the forum:

Code Select Expand

$request = mysql_query("SELECT value FROM {$db_prefix}banned WHERE (type='ip' AND (value='$remote_ip' OR value='$ipparts[0].$ipparts[1].$ipparts[2].*' OR value='$ipparts[0].$ipparts[1].*.*')) $registeredUserString;") or database_error(__FILE__, __LINE__);

As a result of this error, it won't be determined that the user causing this error is a banned user!! Not that he can do anything on the forum, but I don't like it that he can cause database_errors.

Is is possible to put an index on the banned-table?

Or is there any other way to make sure this query will perform well and doesn't lead to an error when stressed?

Thanks in advance!

[[Unknown]]

Usually this is because of an unstable link to the MySQL server.  For best performance, I'd recommend having them - funny enough - on the same server. (web and database.)

-[Unknown]

[Snoader]

Usually this is because of an unstable link to the MySQL server.  For best performance, I'd recommend having them - funny enough - on the same server. (web and database.)

Unfortunately, that's no option since the site is hosted somewhere else.

Suppose this is not because of an unstable link, are there any things I can do to prevent errors during the query?

[[Unknown]]

If you have no banned users at all, I can tell you how to remove it .

Do you ban by IP, usually?  If you do, do you use wildcards (127.0.*.*) or the exact IP?

-[Unknown]

[Snoader]

Because I had the idea that the query had performance problems, I made some changes to the code.

I took the Security.php from version 1.4.1 (with a triple ban-check, instead of the current check which contains 3 checks (IP, email and username) at once), and copied the code of the query to check for a banned IP.

I added the following to the current function banning():

Pseudo-code:

User = Guest?
then
  wait a small random amount of time
  check only for banned IP
else
 do normal check
end

Checking for banned usernames and email-adresses when the current user is a Guest is not necessary at all, and now our errorlog stays clean when the flooder is flooding the forum because the check-IP query works much better under high loads.

When the user stops his flooding attacks somewhere in the future, I'll remove the random amount of time-wait, because it (only) effects other guest-users (slower loading of pages).

[Snoader]

By the way [Unknown], thanks for responding!