I want to ask you for clarification and advice regarding the following situation that emerged during a penetration test of my client's eCommerce shop.
My client is using an xt:commerce shop software in combination with a MariaDB. During an initial automated discovery of potential SQLi and XSS vulnerabilities, the middleware that reports about various errors (web server, PHP and database errors) suddenly threw multiple errors that the maximum number of allowed user connections were exceeded:
(HY000/1203): User xyz already has more than 'max_user_connections' active connections.
Subsequently, similar error messages were spawned:
exception 'Exception' with message 'Unable to connect to database server!' Error-Nr.: 1045 Access denied for user 'abc'@'localhost' (using password: NO)
Effectively, for the page visitors it was not possible to browse the articles or to purchase them as no one was able to connect to the database using the standard user anymore.
The question was, why internally the database kept some connections open. It seemed like some jobs failed to finish. After some more investigations, we found that 99% of the open connections related to this particular user were in a zombie-like state with status
Waiting for table flush. Immediately, I thought that the client was using an unreported backup script that would execute some sort of
ANALYZE TABLE as reported here, which unfortunately colluded with the penetration test. And indeed there was an unreported CRON job doing just this. However, we found that this job didn't run at this time and hence could impossibly be the reason for the behavior.
So my first question is: What could be the reason that led to this deadlock situation, which could only be resolved by a restart of the database service?
Secondly, during a second phase, which did not involve SQLi tests at all but during which different XSS vectors were tested against a potentially vulnerable script in an automatic fashion, we again saw that the maximum number of user connections were depleted. This effectively is a DoS against the database making it unavailable to legit users of the website. We checked the related database configuration entries and they were adjusted as follows:
As the legit users connect via PHP using the same DB user, there are effectively 30 connection available.
So my second question is: How can you mitigate such easy DoS attacks against the DB, where you simply exhaust the available connection by executing numerous SQL queries (i.e., be repeatedly querying a search form)? A naive approach would be setting
max_user_connections=0. However, there will be a trade-off and I have a bad feeling about it. One the one hand you get rid of the bottle neck of limited connections. However, the DB resources will be stressed much more. Is there a more clever way to avoid such DoS-like situation while also maintaining a reasonable usage of the DB resources?
P.S.: Yes, never do an active / aggressive pen-test on a live system. The client explicitly asked (begged) to do it anyway, no matter how much I told him that this is a very bad idea.