3

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:

max_connections=250
max_user_connections=30

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.

  • Just to be sure: you're giving the user direct access to the database? – Marcus Müller Apr 6 '18 at 9:59
  • No, the user gets a standard mysql interface via PHP using mysql_connect(), mysql_query(), etc. – user1192748 Apr 6 '18 at 13:58
0

I'd expect that you dimension your database system so that you can serve as many parallel requests as your overall system should allow. If each request spawns a new database connection (which in itself might be seen as design shortcoming), 30 does seem very low. Have you actually tested how much overhead a zombie connection is, and how long it takes for the database server to close it? My suspicion is that you're fighting ghosts here, and can easily use an order of magnitude more connections.

I don't know your web application, but often you'll find something like N service workers which each hold one persistent connection to the database (which hence only needs to accept N connections), and these workers split the user sessions "fairly" among them. I don't think PHP lends itself to such "sane" network service designs, but then again, I've always felt biased against PHP.

0

The best thing i could think about is to fix the application and the Environment that it will work correctly and closes all DB Connections after use. And if the CronJob runs some time and don't close it's Connections this could also be a problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.