Restrict MySQL (MariaDB 10.1) for vulerable loop scripts

mehargags

Restrict MySQL (MariaDB 10.1) for vulerable loop scripts

Hi all,

Last night I identified one of my server's MySQL load had all 4 CPU cores over 95% of use, while the RAM usage was normal. Identified an old (but busy) site had some old redundant script lying which a Russian IP 188.162.43.151 was trying to exploit. It was trying to pull out email ID's from a newsletter table with around 700,000 records.
Blocking this IP for all access resolved the issue for me.

I've informed the client's Dev team and they are actually re-developing the whole site... so will take care of this.

My question here is, can we as server admins restrict MySQL to kill queries that put the load too much for a certain period of time? how can I configure and mitigate such deadly loops on the server side configuration.

Thanks alot

gbshouse

That's a problem with application and not database. You need to validate parameter types, ranges (if necessary). There are few ways to prevent SQL injection

mehargags

@gbshouse said:
That's a problem with application and not database. You need to validate parameter types, ranges (if necessary). There are few ways to prevent SQL injection

Yes I do understand... the same has been fwd to the client's Dev team. I was just wondering if there is something at the server config level we can do to counter such situations.

sanmax88

Try blocking it with a modsec rule, you have a sample input afterall

Had this problem before, it can also block mysql connections per user when multiple bots found this vulnerability

mustafaramadhan

In nginx or hiawatha, possible limiting access from certain IP.

Jari

Perhaps https://mariadb.com/kb/en/mariadb/aborting-statements/ or https://mariadb.com/kb/en/mariadb/grant/#setting-per-account-resources-limits will point you in the right direction.

Francisco

php3

theroyalstudent

@Francisco said:
php3

You'll never know, maybe it's for the sake of confusing users

Mark_R

services like https://www.incapsula.com exist for people who cannot secure their own setups.

mehargags

@Mark_R said:
services like https://www.incapsula.com exist for people who cannot secure their own setups.

I'm 200% sure you didn't even read my post completely. Asking question here and consulting fellows doesn't make someone incompetent.

@Jari said:
Perhaps https://mariadb.com/kb/en/mariadb/aborting-statements/ or https://mariadb.com/kb/en/mariadb/grant/#setting-per-account-resources-limits will point you in the right direction.

Thanks... something close to what I was thinking. However the "account" here to block/timeout is what?? localhost ? MySQL is only listening to unix socket, not on TCPIP remote hosts. So technically it is single Apache "account" only... right ?

@sanmax88 said:
Try blocking it with a modsec rule, you have a sample input afterall
Had this problem before, it can also block mysql connections per user when multiple bots found this vulnerability

Yup I thought of it, but it is again some Dev to decide what pattern to block otherwise I may block working scripts of the App. But will take a look. Can you suggest a rule seeing the args in the offending script?

@mustafaramadhan said:
In nginx or hiawatha, possible limiting access from certain IP.

Thats how it was mitigated... I blocked that IP in my firewall!

GM2015

How many times has this request been occurred? Are they always asking the same url?

mehargags said: Thats how it was mitigated... I blocked that IP in my firewall!

Jari

The account is not related to any particular software (e.g. Apache). It is the MySQL account configured in your application.
Your application's config most likely contains database host, database name and credentials. These credentials determine the proper MySQL account.
Since you are using a Unix socket, the MySQL account should have localhost as its host. Keep in mind that 127.0.0.1 is not the same as localhost, because the former implies a TCP connection while the latter applies to Unix sockets.
Maybe I can provide you with an example once I get home, typing commands on my phone sucks.

mehargags

@GM2015 said:
How many times has this request been occurred? Are they always asking the same url?

@GM2015Can't give you a count... but there were continuous attacks with the same request. While Identifying the prob, I suspended DB... even full Mysql Services to drop any ongoing threads. It kept fine for a brief moment then again the 4xCPU shot up to >95%. The MySQL process list showed the same threads, So I knew this is some kind of request coming in and Apache log lead me to find this IP. Blocking this IP in the firewall mitigated the request.

So what I conclude is this script have been under some notorious researcher's radar. Any more views you have on this ?

@Jari said:
It is the MySQL account configured in your application.These credentials determine the proper MySQL account.

@Jari Ah yes...Thanks for the clarity... I understand now.

And Yes I know abt Unix Socket/localhost and 127.0.0.1 over TCP. MySQL is disabled for TCP, even the ports are blocked.

@Jari said:
Maybe I can provide you with an example once I get home

Would love that... Thanks for all the pointers!

Jari

The following worked for me:

GRANT USAGE ON abused_db.* TO 'malicious_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 36000; GRANT USAGE ON abused_db.* TO 'malicious_user'@'localhost' WITH MAX_STATEMENT_TIME 10;

The first statement limits the number of queries per hour (10 per second on average). As far as I know, this would still allow for 10k queries to be executed within the first minute and 26k for the remaining 59 minutes.
The second statement protects you from long running queries by limiting the execution time to 10 seconds.

Of course, your mileage may vary and these limits' usefulness is somewhat questionable, but it might be worth a shot.

tommy

Change your script to denied direct access to php file(s).

Filter filter and filter