CODEWITHSUNDEEP
phpmysql
homerun
homerun

Reputation: 20765

PHP: prevent from overflow the database

I have tiny url service , the sending data to the server is working simply with ajax function which send data to certain page and insert the data to the database.

I just have played a little bit with firebug and i found out that i can loop the ajax function thousands times in a second and it's floating my database.. which i just did..

session and cookies could not work here from obvious reason..

how can i prevent this?

Upvotes: 1

Views: 287

Answers (2)

Will H
Will H

Reputation: 1448

A very easy way to fix this issue that works really well and prevents DDOS attacks is to use bulk inserts on a post processing function. For your tinyurl have the call save all the data that is being entered as a concatenated file on the filesystem in a flat text file for instance (CSV) works well for this.

Then run a cron job every 1 ~ 5 minutes that reads the text file and does a bulk insert to mysql. The key here is doing bulk inserts. It's much more efficient to do 1 bulk insert than 100,000,000 single queries.

To give you an idea I deal with massive data inserts on a daily basis, where we get roughly 1 million insert requests per minute. Doing 1 mil inserts as single queries will take on our huge db server about 15 minutes. Doing them as a bulk insert takes about 18 seconds. It's staggering how much faster bulk inserts are and you also only consume 1 connection on your mysql box.

Bulk inserts are very similar to a regular insert the only difference in the query is in the VALUES part. Where you would normally have VALUES=('abc','123','abc') for instance you would now have... VALUES=(('abc','123','abcd'),('cde','456','dsw');

Hope this helps :)

Upvotes: 1

evan
evan

Reputation: 12543

What I think you want to prevent is a DOS (Denial of Service) attack. There is a lot of information out on the web on how to prevent this. For a very simple service solution, you can limit the interaction with your database to require a pause between requests.

You can use sessions to help prevent this.

Flow:

  1. User views page - create session
  2. User uses service based via ajax
  3. On ajax request - check if session contains "last_run" timestamp.
  4. If it does not exist, allow the query, add "last_run"
  5. If it does exist and the timestamp is older than your timeout (say, 1 second), allow the query, update "last_run".
  6. If it does exist and the timestamp is not older than your timeout, don't allow the query.

Upvotes: 0

Related Questions