Maintaining state between browser tabs in PHP

Question

I'm building a web application that allows users to run a query against one of two databases. They are only allowed to submit 1 query to each database at a time. Currently, I'm setting $_SESSION['runningAdvancedQuery'] (for instance) to let me know if they're running a query. This is to prevent someone from opening up a 2nd browser tab and hitting the resource again. It works great except...

If I start a query in a tab, then close that tab before it finishes, the flag does not get unset. Therefore, if I reopen the page, I cannot run any queries because it thinks I have one still running. Anyone have advice for getting around this?

Answer 1

Set this value not to for example 1, but to unix timestamp, and do checking by comprasion last-query-timestamp to now, setting up some time difference that must go by to execute next query. Remeber to set a block-time to safe value - longest time which query can be executed. If user closes his tab, after a short time he will be "unlocked".

Answer 2

<?php
ignore_user_abort(true);

//if session variable is not set
    //set session variable
    //run query
    //unset session variable
//else
    //show error: "your other query isn't finished yet"

?> 

Answer 3

Instead of setting $_SESSION['runningAdvancedQuery']; to true, you could set it to the output of SELECT CONNECTION_ID();, and check show processlist; whether it's still running. This would be an addon to the other answers though: especially when using persistent connections other processes could have the connection_id in use.

As the same user, you are always allowed to see your own processes. So, to combine the other checks:

1. Store timestamp, connection_id & actual sql-query (I'd remove all white-space & cast to either upper or lower string to avoid slight differences in presentation to matter).
2. Of course use the ignore_user_abort() functionality, do a session_write_close() before the query, restart the session & set it to finished after the query.
3. On a check for the running query, check whether
   • The connection-id is still present.
   • The timestamp + seconds the query is running are reasonably close to the current time.
   • The query (lowercase, stripped whitespace) is about the same query as requested (use SHOW FULL PROCESSLIST; to get the whole query).

Optionally, take it a step further, and give people the possibility to KILL QUERY <connection_id>; (only if the previous 3 checks all resulted in a positive).