Reset database field values when active page is unloaded

Question

In my app, I have a record locking mechanism that prevents two people from being in the Edit view of a record at the same time. Here is the function that locks the record when it is called up in the Edit view:

public function lockRecord($id = null) {
$this->loadModel('User');
$this->Model->id = $id;
$current = $this->Model->read(null, $id);

//Get the current logged-in user's ID
$userid = $current['Model']['requester_id'];

//Get the current lock expiry time
$lock_time = $this->Model->find('first', array(
  'fields'=>array('Model.lock_expiry_time'),
  'conditions'=>array('Model.id'=>$id)
  )
); 

//Get the ID of the user who has the record lock (if any)
$logged_user = $this->Model->find('first', array(
  'fields'=>array('Model.lock_key'),
  'conditions'=>array('Model.id'=>$id)
  )
); 

//Get that same user's full name
$full_name = $this->User->find('first', array(
  'joins' => array(
    array(
      'table' => 'recordtable',
      'alias' => 'Model',
      'type' => 'INNER',
      'conditions' => array('Model.lock_key = User.id')
      )
    )
  )
);
$this->set('lock_time', $lock_time);
$this->set(compact($current));
$this->set(compact($logged_user));
$this->set(compact($full_name)); 
if(AuthComponent::user('id') != $logged_user['Msr']['lock_key'] && date("Y-m-d H:i:s") < $lock_time['Msr']['lock_expiry_time']) {
  $this->Session->setFlash(__('This MSR is locked for editing by ' . $full_name['User']['full_name'] . '. Please try again in a few minutes or wait for this user to close the document.<br/>
    (Lock expires at '. $lock_time['Msr']['lock_expiry_time'] . ')'));
  $this->redirect(array('action' => 'view', $id));
} else {
  //Set a new lock key and expiry time if the record is free for editing
  $locksession = $this->Msr->query("UPDATE msrs SET lock_key = {$userid}, lock_expiry_time = ADDTIME(NOW(), '00:05:00') WHERE id = {$id}");
  $this->set('locksession', $locksession);
  }
}

When the user saves their changes, the unlockRecord function is called to release the key, reset the lock_expiry_time, and redirect the user to the "View" view:

public function unlockRecord($id = null) {
//Get a list of security groups
$groups = $this->Session->read('groups');
$this->Msr->id = $id;
//Reset the lock_key and the lock_expiry_time
$locksession = $this->Msr->query("UPDATE msrs SET lock_key = '', lock_expiry_time = '' WHERE id = {$id} ");
//If the module admin manually releases the lock, display a message
if(in_array('msr_module_admin', $groups)) {
  $this->Session->setFlash(__('The MSR has been unlocked and is available for editing.'));
}
$this->redirect(array('action'=>'view', $id));
}

There are three other conditions where the lock can be released:

--An administrator manually releases the lock by clicking a link.

--The user logs out of the system entirely.

--The lock expires five minutes after it is set.

I need the lock to be released any time the record in the Edit view is no longer active. For instance, if the user clicks off to another site or clicks a different link in my own site; anything that takes them away from the record they have open in the Edit view. lock_key should be set to '' and lock_expiry_time set to ''. How might I accomplish this?

`"... WHERE id = {$id}"` Aaaand that's an SQL injection vulnerability! Please make sure to use bindings when creating raw SQL statements, **_never ever_** insert possible user data into queries directly! — ndm, Mar 31 '17 at 20:09

score 2 · Answer 1 · edited May 23 '17 at 12:25

Recognizing that a page on your site has been abandoned by the user is not possible to do reliably in web programming.

What happens to your web site if she switches off her computer/device or disconnects her network? Nothing. What happens if she clicks a link in her bookmarks bar in the middle of editing your page? Nothing. You never hear from her again. That's why your lock has an expiry time.

So, to address your problem you need to think about ways to shorten the expiry time and/or reduce the chance of page abandonment.

One choice: put some Javascript in your edit page that uses an ajax-style request once every so often to hit your web site with a keepalive request. You can rig this up to hit your site every minute. Every time you get the keepalive request, you can extend the expiry time by 90 seconds.

Another choice: You may have noticed that StackOverflow pages throw a browser dialog box saying "Do you want to leave this site?" if you press the BACK button while editing a post. SO actually throws the dialog box whenever you abandon a page. They use the onbeforeunload event in the browser for this. You can do the same, enabling a dialog box when the edit page is activated, and disabling it when the user saves it.

Browsers don't allow web pages to force any sort of action other than a dialog box upon page unload. Such a feature would be very easy for cybercreeps to use to propagate malware.

That's certainly better than nothing, and pretty straightforward. I'll give it a shot. — Chris, Mar 31 '17 at 13:39

Reset database field values when active page is unloaded

1 Answers1