Add form submission to database and send an email too

[VipulK]

I have a client whose website sent the contents of a contact form to their email but for some reason the mail sending took 20-25 seconds to process which was bad for user experience. I did very thorough investigation and the hosting provider also looked into it but couldn't find anything out of the blue. It seems the only solution would be to change the servers completely which is not possible at the moment. Anyways, to reduce this time, I instead routed the contact form data to a database (MySQL) instead and created a CRM sort of panel for him to view all the submissions he has received from the contact form.

 

Now, that's all fine but the client the other day told me if he could get a mail as well, so he gets notified when a new query has come from the contact page. If I add the mail() function back in there, the whole point of database will be moot since it'll take even longer this time because now I'll have to add to database and send email too. But it seems having a notification is important to the client.

 

So I was wondering If it would be possible to add data to the database and show thank you page to the website user instantly, but send the email in the background? Or maybe schedule the mail sending at a later time which doesn't require any input from the user? The traffic on the site isn't much so performance is not that high on the priority list.

 

Any ideas would be greatly helpful.

[requinix]

How much access do you have on the machine? Is it dedicated? Shared? You have access to cron?

[benanamen]

To answer your question yes. You shouldn't need to schedule emails being sent from a form submission. Post the code you have so we can review it. It is simple to do what you have asked and is very basic.

[VipulK]

@requinix The server is a VPS and I have full access. I suppose I have access to cron as well but I haven't used it before.

 

@benanamen here's what I have so far

 

$name = mysqli_real_escape_string($connect, $_POST['name']);
$phone = mysqli_real_escape_string($connect, $_POST['phone']);
$email = mysqli_real_escape_string($connect, $_POST['email']);
$message = mysqli_real_escape_string($connect, $_POST['message']);
$today = date('Y-m-d');
$time = date("g:i:s a");
$ip = $_SERVER['REMOTE_ADDR'];
$source = $_SERVER['HTTP_REFERER'];


if(isset($_POST['g-recaptcha-response'])){
  //if form has captcha process it
  $url='https://www.google.com/recaptcha/api/siteverify';
  $secret = 'RECAPTCHA_KEY';


  $response = $_POST['g-recaptcha-response'];


  $verifyCaptcha = file_get_contents($url."?secret=".$secret."&response=".$response."&remoteip=".$ip);
  $captchaReply = json_decode($verifyCaptcha);


  if(isset($captchaReply->success) AND $captchaReply->success == true){
    $validQuery = true;
  } else {
    $validQuery = false;
  }
} else {
  $validQuery = true;
}


if($validQuery){

  //ready to insert
  $lastId = mysqli_query($connect, "SELECT id, query_id FROM `queries` ORDER BY `id` DESC LIMIT 1");
  $lastId = mysqli_fetch_assoc($lastId);


  //generate a unique id for this query
  $explode = explode('-', $lastId['query_id']);
  $splitNumber = str_split($explode[0], 2);
  $yearNumber = $splitNumber[0];
  $monthNumber = $splitNumber[1];
  if($yearNumber == date('y')){
    $queryID = $explode[1] + 1;
  }else{
    $queryID = '0000';
  }


  $queryID = str_pad($queryID, 4, "0", STR_PAD_LEFT);
  $queryID = date('ym').'-'.$queryID;


  //insert query
  $insertQuery = "INSERT INTO queries (query_id, `name`, `phone`, `email`, `project_details`, `date_of_query`, `ip`, `source`, location) VALUES ('".$queryID."', '".$name."', '".$phone."', '".$email."', '".$message."', '".$today."', '".$ip."', '1', '".$source."')";
  $insert = mysqli_query($connect, $insertQuery);


  if($insert){
    header('Location: thanks.php');
    exit();
  } else {
    echo "Query failed. ".mysqli_error($connect);
  }
} else {
  echo 'Captcha verification Failed. Please try again.';
}

I'm not asking for someone to code it for me or a ready-made solution. I'm asking for concept ideas to go ahead for it.

[requinix]

Actually we were thinking more about the code that sends the email. The older code, I guess, that was running slowly.

 

If you have cron and we can't figure out the email problem then it's not too hard to make a periodic emailing thing. But as benanamen said, you shouldn't really have to resort to that...

[benanamen]

Why are you generating your own query id? You should be using prepared queries as well.

 

Where is the code that sends the email? Is this even the same code you mention in your first post?

Edited November 20, 2016 by benanamen

[requinix]

You should be using prepared queries as well.

Prepared statements mean the developer does not have to escape the data manually. The developer is already escaping the data manually. Therefore prepared statements are not required.

[Jacques1]

The above code is actually a perfect example for why manual escaping just doesn't work.

 

He escapes the input somewhere on top of the script (essentially mimicking Magic Quotes), then runs the query somewhere below. Oops, he missed the referrer, so all those mysqli_really_really_escape_this_please() calls are useless. And if he happens to need the data for something other than a query, the script silently fails. Or there are “strange backslashes” on the site. Or we're back to the days of stripslashes().

 

Manual escaping is like those abstinence-only programs for horny teenaders: In theory, it works great. In practice, not so much – or so I've heard.

[mac_gyver]

here's what I have so far

 

Nooooooooo...... this code is logically incorrect and if you are using this same basic code for the email version, it is not secure.

 

the point of a captcha is to prevent non-human submissions from working or unnecessarily using server resources. the current code, if there is no captcha field in the submitted data sets $validQuery = true; and merrily runs the rest of the code. only if there is a captcha field in the submitted data and its value matches the expected value should the rest of the the form processing code run. you should not have any other statements before you have verified the captcha.

 

your form processing code, regardless of what it finally does with the submitted form data, must first test if a post method form was submitted. next, only checkbox and radiobuttons in a form are 'optional' and may not exist in the submitted form data. by using an isset() test for a field that is 'required' makes that field 'optional'.

 

if you need (that still hasn't been determined, since you should find and fix what's causing the slow email operation) to convert from sending an email to recording the submitted data, all you should do is take the same information you have now that's going into the email and (securely) insert that into the database table, along with recording the date/time of the submission, perhaps other things like the visitor's ip address..., and a status field, that would be used to control if the record has been send in the summery email. there's no need for all the rest of logic you have shown. all you are doing is changing what happens with the submitted form data.

 

next, to implement the cron based sending of a summery email, you would just find the records in the table that have a status that says they have not been sent, retrieve the data, produce the summery email, and if sending is successful, change the status to indicate they have been sent. you could also have a field in the table that you update with the send date/time.