Rapid Ajax calls causing some MySQL queries to be ignored

Question

I am currently working on a Joomla website on a Unix server. There are no users connected at the moment because I'm the only one who can access it (so no heavy traffic).

During testing, I proceed to make several MySQL INSERTs or UPDATEs on a table in my DB. When the Ajax is triggered, data is sent to a php file which connects using jFactory::getDBO(), executes a query, and returns a success response.

Everything works perfectly, until I run several tests in rapid succession. After a while, even though I still get a success response in Ajax, there are no affected rows (not on this table, not on any other table).

It's like MySQL blocks all the connections (and yet I don't get any error message). I tried opening a different browser and magically the queries execute perfectly/successfully from there. I think I also noticed that it starts working again when I close the original browser and open it again.

What could the issue be? And how can I solve it? I was looking for a way to close the getDBO() connections after each query, but all I found online is that on a Unix server, this type of connection is automatically closed after each query.

This is the Ajax Call:

jQuery.ajax({
          type:"POST",
          url:"/models/ajax.php",
          data:{"sid":sid, "steid":steid, "answer":answer,   "function_to_call":21},
          dataType: "json",
          cache: false,
          success: function(data){
              console.log(JSON.stringify(data));
          },
          error: function(msg){
            console.log(JSON.stringify(msg));   
          }     
});

This is how the insert/updates are performed:

if(isset($step) && !empty($step) && trim($step) != ""){

  $sql = "UPDATE #__thamatho_student_mistakes SET   
  date_mistake='".$date."', answer='".addslashes($answer)."' WHERE 
  student=".$userid." and step=".$steid;
  $db->setQuery($sql);
  $db->query();

}else{  

  $sql = "Insert into #__thamatho_student_mistakes (`student`, 
  `step`, `answer`, `date_mistake`) values (".$userid.", ".$steid.", 
  '".addslashes($answer)."', '".$date."')";
  $db->setQuery($sql);
  $db->query();

}

Answer 1

Probably the browser gets a response from the browser cache, try to add a time variable to the link or otherwise disable the cache

Answer 2

My first concern is always security, add_slashes() is an inadequate function to call to protect your query. I would urge you to implement the Joomla query methods to build your queries and read about Joomla's Security Recommendations.

I agree with the other volunteers that this is likely to be a matter of caching and that MySQL is not likely the culprit in your issue. If MySQL was having trouble you, could find some evidence in the error logs or write your php file to return some informative feedback.

To offer greater specificity on the commented advice.

You can add a random or time-based querystring to the url/page that is receiving your submitted data so that never will you have two duplicate urls:

url:"/models/ajax.php?unique=" + Math.random(),

or

url:"/models/ajax.php?unique=" + +new Date();

Note, you don't actually need to use $_GET['unique'] on the receiving page, this is merely being used as a unique identifier to prevent caching.

If that doesn't work or you wish to "double-down" on cache-prevention, then you can also write header("Cache-Control: no-cache, must-revalidate") at the very top of your receiving php file.

Beyond all of that, Joomla has some guides on developing an ajax interface which are intended to improve security and make coding simpler.

And as far as your raw php validation condition:

if(isset($step) && !empty($step) && trim($step) != ""){

That can be rewritten as if (!empty($step) && trim($step) != "") { because !empty() checks for existence (making isset() redundant) AND checks for a non-false-y value. If you have a more narrow expectation of the $step value, then I would advise that you perform stronger validation at this location in your code.

I don't know the capabilities of your project interface, but if several subsequent submissions (ajax calls) are likely, perhaps you could re-design the form to allow multiple sets of data to be delivered in a single batch. By minimizing the total ajax calls, you minimize the number of times that a db connection will have to be opened and closed -- this is best practice.

For greater peace-of-mind, you could return a more detailed explanation of the values sent and how many rows were affected.