0

I have a website where I need to fetch data from mysql and show them to user.

Every time user refreshes the page the mysql is going to connect, fetch som data, return it and the connection is closed (so user can be up-to-date). There is a problem with it, because I tired to spam refreshes on my site and after 25.000 mysql connections in hour, my page is going to be down for an hour, because I have mysql_max_connection equal to 25.000 by my hosting provider. I have only one account for MySQL there so I can't switch to any other account.

I was thinking about using the PDO::ATTR_PERSISTENT to make persistent connections, but there is no mysql_reset when the user comes back, so I think it's not a good idea to use it.

I wanted to ask if there is any way to prevent it within PDO or any other PHP/MySQL functions or how it's done by some commercial sites like facebook or so. Thank you for your advise.

Dawid Zbiński
  • 5,521
  • 8
  • 43
  • 70
  • 1
    If you are worried about DOS attacks and not normal usage fail2ban or something similar might be a better choice. – Gerald Schneider Feb 11 '16 at 14:21
  • How it's done by commercial sites like Facebook? Well, according to [this article](http://www.datacenterknowledge.com/the-facebook-data-center-faq-page-2/), as far back as 2010 they already had one of the largest MySQL clusters on the planet, and a total of around 60,000 servers (though the majority of those would be web servers; there's no split of database servers in the article.) – Matt Gibson Feb 11 '16 at 14:24
  • I think this is more of about general DDoS prevention, hence too broad (there is zillion ways to do this), and even if it was not, I’d be inclined to say it’s not really programming, but rather server related question, as there must be a mechanism outside of the code that will cut the unwanted connections. – Smar Feb 11 '16 at 14:33
  • If you have thousands of connections, you would be distributing them among a number of different DB servers, not just pointing them to a single machine. See MySQL [clustering](https://dev.mysql.com/doc/refman/5.0/en/faqs-mysql-cluster.html) and [replication](https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-replication.html), for example. – kalatabe Feb 11 '16 at 15:07

1 Answers1

1

You can limit the usage per user per hour in MySQL: LINK SECURITY MYSQL

However, most web apps use a single MySQL username for all application users. So this might just serve to throttle your whole website.

The addslashes function is not the right solution for preventing SQL injection. Every MySQL API includes a more appropriate escaping function, for example mysqli_real_escape_string() or PDO::quote().

But even better is to use prepared statements with query parameters instead of escaping and concatenating variables into SQL query strings.

Examples are easy to find if you examine other questions with the sql-injection tag. One of the best answers is in How can I prevent SQL-injection in PHP?

Check and try these information. Good luck.

Community
  • 1
  • 1
BlackHack123
  • 349
  • 1
  • 10