whois online -> server crash (too many connections mysql)

[loop]

Hi All
We used phoenixcart since 3 years now and in the last 2 weeks (probably i got a lot of traffic, bots / spammer) i have a couple of time that i receive a error 500 too many connections,

i found out, whenn this happends i see like 120 process in the sql process which tries to do this:
DELETE FROM whos_online WHERE time_last_click < 1740932324 (with different timestamps)

i'm not sure if my problem is because of this, that i have a "bot" attack or something and the whois table want to delete on same time 120 times and this makes problem, but for testing purposes, can somebody tell me if i can disable the whois modul, or do i need this urgently? other idea would be that i change the delete query to a cron job instead on every page load...

in the whois.php i can / could comment the delete, but maybe there is a better way or maybe somebody know the real reason for my too many connections (whois table was / is 14'000k entries at the moment, so i'm guessing there is a lot on our server bots / spam / indexing)

Code: Select all

 protected static function expire($current_time) {
      //$GLOBALS['db']->query("DELETE FROM whos_online WHERE time_last_click < " . (int)($current_time - 900));
    }

thanks for letting me know asap, i'm a little bit worried

[ecartz]

You could try adding LIMIT 150 to the end of the query. It's possible that each query is locking on deleting all 14,000 entries. Limiting to 150 might cause them to return more quickly.

You also might consider turning off the insert, possibly only for not-logged in users.

And of course, if you fix whatever's causing so many simultaneous connections, that might solve the problem directly.

[heatherbell]

thanks for letting me know asap, i'm a little bit worried

Just to share our experience, we had similar issue earlier last year with mysqli::__construct(): (hy000/1040): too many connections happening.
Looking at the time of the error we could see a direct correlation with a spike in bots at those times creating 1000's of page requests in access logs where we could identify IP and User Agent.
We blocked them in htaccess and the issue went away for a time.
We have since discovered that these bots are likely to be AI data scrapers.
The issue did eventually recur and realised we could be chasing our tail to keep up with them.
We then followed advice to change to a host that provided bot protection and we have not had an issue since.

[loop]

hi, i'm pretty sure it's about bot / ai scrapper / scammer ...
i have a own server so i can't / will not change to a cdn hosting provider, but maybe i shoud / can book additional security layer but i have no knowledge which one is good / and the price worth.

does anybody has experience which provider i should have a look for cdn and if it's difficult to implement this security layer if owning own server?

[ecartz]

CloudFlare is the big one. I don't know that anyone else comes close to matching their price/feature set.

It's a proxy. You move your domain to their DNS. They proxy to your server. They can also handle your static files (so that those requests never even hit your server once cached).

A self-hosted alternative would be something like Fail2Ban, which automatically bans IPs that attempt authentication but fail, or modEvasive. https://security.stackexchange.com/a/35778/317099

[loop]

hi ecartz
do you have any experience with cloudflare / phoenixcart? if i book cloudflare (PRO or Business, don't quite understand the difference yet) then i have less problems with bots / attacks as i see it. I don't want to outsource static files (images), because I resize them dynamically with thumbor and that is probably very complex or would also be huge amounts of data all images in all versions / file formats

do you have PRO or Business subscription experience? do you recommend the Business solution?

greetings and thanks
Philipp

[ecartz]

do you have PRO or Business subscription experience?

I do not have personal experience with using CloudFlare. Every site on which I worked that did had someone else managing it.

I would expect even the free tier to handle a basic DDOS. If you are taking credit cards on your site (e.g. with Stripe rather than a hosted solution like PayPal IPN), then you might be required to use Business for PCI. If you need Business, it might be cheaper to stop self-hosting. You could likely get a VPS with DDOS protection included for less if you shop around.

[heatherbell]

These hosts have Cloud Server with CDN, WAF with Bot & DDoS Protection built in:
https://www.20i.com/phoenix-cart-hostin ... rt+hosting

[MyGamesShop]

You should not have to be forced to any hosting company for protection from the very people who sell services to people who launch DOS attacks.

app.php/addons/paid_addon/phoenix_firewall

Phoenix has a paid firewall I am using its done a good job so far auto-banning. However I promote the resistant pests to a real firewall.

This is from Ampache Media Server .htaccess who also clearly have the same problem, this might need adjusting.

# BOTS
#RewriteCond %{HTTP_USER_AGENT} (Agent\ GPT|AgentGPT|AIBot|AI2Bot|AISearchBot|AlexaTM|Alpha\ AI|AlphaAI|Amazon\ Bedrock|Amazon\ Lex|Amazonbot) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (Amelia|anthropic-ai|AnyPicker|Applebot|AutoGPT|AwarioRssBot|AwarioSmartBot|Brave\ Leo\ AI|Bytespider|CatBoost) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (CC-Crawler|CCBot|ChatGPT|Chinchilla|Claude-Web|ClaudeBot|cohere-ai|cohere-training-data-crawler|Common\ Crawl) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (commoncrawl|Crawlspace|crew\ AI|crewAI|DALL-E|DataForSeoBot|DeepMind|DeepSeek|DepolarizingGPT|DialoGPT|Diffbot|Googlebot) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (DuckAssistBot|FacebookBot|Firecrawl|Flyriver|FriendlyCrawler|Gemini|Gemma|GenAI|Google\ Bard\ AI|Google-CloudVertexBot) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (Google-Extended|GoogleOther|GPT-2|GPT-3|GPT-4|GPTBot|GPTZero|Grok|Hugging\ Face|iaskspider|ICC-Crawler|ImagesiftBot) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (img2dataset|IntelliSeek\.ai|ISSCyberRiskCrawler|Kangaroo|LeftWingGPT|LLaMA|magpie-crawler|Meltwater|Meta\ AI|Meta\ Llama) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (Meta\.AI|Meta-AI|Meta-ExternalAgent|Meta-ExternalFetcher|MetaAI|Mistral|OAI-SearchBot|OAI\ SearchBot|omgili|Open\ AI) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (OpenAI|PanguBot|peer39_crawler|PerplexityBot|PetalBot|RightWingGPT|Scrapy|SearchGPT|SemrushBot|Sidetrade|Stability) [NC,OR]
#RewriteCond %{HTTP_USER_AGENT} (The\ Knowledge\ AI|Timpibot|VelenPublicWebCrawler|WebChatGPT|Webzio|Whisper|x\.AI|xAI|YouBot|Zero\ GTP) [NC]
#RewriteRule (.*) - [F,L]

# SQL INJECTION
#RewriteCond %{QUERY_STRING} "%3Cscript%3Ealert%28" [NC,OR]
#RewriteCond %{QUERY_STRING} "%20UNION%20ALL%20SELECT%20" [NC,OR]
#RewriteCond %{QUERY_STRING} "%20FROM%20information_schema.tables%20" [NC]
#RewriteRule (.*) - [F,L]

But to the DEV's, Phoenix is very vulnerable to Bot attacks if you haven't read elsewhere (I don't mean vulnerable as in In-secure).
I think we need a simple, friendly and accessible "Are You Human" checkbox before the IP can access any products away from the front page of the site with allowances for some headless 'Good Bots'. Or at least limit the page visits before the checkbox is...checked?


Don't do the above htacces changes... Maybe someone could confirm if they work or need amending for Phoenix.
(remember I don't know what I'm doing.)