security – PHP Email Verification, Sanitizing Email Input for Database Table


Intro

The purpose of this post is review and feedback. I’ve been working with PHP for three weeks and have reached a point where I require the advice from those with much more experience. The PHP scripts I’ve written are fully functional and have already been perfected to the best of my abilities. There is a lot to go over, so I will be as detailed as possible…

I have an HTML form on my website where users can input their email address. Using php, the input is sanitized and stored in a database table for the purpose of creating an email list. The information that I insert into the table includes:

  • id: int(11) AUTO_INCREMENT,
  • datetime: VARCHAR(18),
  • email: VARCHAR(255),
  • acode: VARCHAR(45) *this is a verification code sent to the user’s email (more on this later),
  • verified: tinyint(1) *this is the verification status (either a 0 or 1),
  • IP4: VARBINARY(39) *used to store the user’s IPv4 Address

Security threats in mind:

1. SQL Injections!!! — Solutions: Prepared Statements (PDO), using only UTF-8, and including “$bpdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);” in the database connection

2. XSS Attacks!!! — Solutions: htmlspecialchars(), Content-Security Policy (placed in htaccess):

<FilesMatch ".(html|php)$">
    Header set Content-Security-Policy "default-src 'self'; style-src 'self' 'unsafe-inline'; script-src 'self' 'unsafe-inline' 'unsafe-eval'; img-src 'self' data: 'unsafe-inline'; media-src 'self' data: 'unsafe-inline'; connect-src 'self';"
</FilesMatch>

3. OS Command Attacks!!! — Solutions: Striping whitespace (not necessary with emails), validating against a whitelist of permitted values.

4. DOS Attacks!!! — Solution: None implemented. I’m unsure if any additional precaution is necessary, since there are no login possibilities on my website.

5. PHP Email Injection!!! — Solution: A Regular Expression (the one I have is mostly designed to allow for international characters).

Additionally, I use an SSL Certificate, SiteLock Security- Essential, CloudFlare CDN, and have implemented a DMARC Policy in my DNS (something I’ll be fine tuning for the foreseeable future).

A detailed look at emailconfig.php:

This is the full script for reference. I’ll discuss each piece below it.

<?php 
    //1 DATABASE CONNECTION
    $dbHost = "HOST";
    $dbUser = "USER";
    $dbPassword = "PASSWORD";
    $dbName = "DATABASE";
    
    try {
      $dsn = "mysql:host=" . $dbHost . ";dbname=" . $dbName;
      $pdo = new PDO($dsn, $dbUser, $dbPassword);
      $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
      $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
      echo "DB Connection Failed: " . $e->getMessage();
      exit(0);
    }
    //1 END

    //2 ADD EMAIL TO DATABASE
    
    //set date and time
    date_default_timezone_set('America/Los_Angeles');
    $timestamp = strtotime('NOW');
    $dateTime = date('Ymd-His', $timestamp);
    
    //variable to store ipv4 address
    $userIP4 = gethostbyname($_SERVER('REMOTE_ADDR'));
    //storing ip6 could be something like: "bin2hex(inet_pton($_SERVER('REMOTE_ADDR')));" but I couldn't figure out if the output was correct, because it looked nothing like an ipv6 address.....
    
    if(filter_var($userIP4, FILTER_VALIDATE_IP)) {
        //yes it's valid IPv4
        if($_SERVER('REQUEST_METHOD') == 'POST') {
            $email = htmlspecialchars($_POST('email')); //convert special characters to HTML entities (&,",<,>)
            $Temail = trim($email); //trim spaces on ends
            
            //allow international characters
            if(preg_match("/^(_a-z0-9-)+(.(_a-z0-9-)+)*@(a-z0-9-)+(.(a-z0-9-)+)*(.(a-z){2,3})$^/", $Temail)) {
                //prevents invalid email addresses
                header("Location: invalid.html");
                exit (0);
            } else {
                //Check Email Domain MX Record
                $email_host = strtolower(substr(strrchr($Temail, "@"), 1));
                if (!checkdnsrr($email_host, "MX")) {
                    header("Location: invalid.html");
                    exit (0);
                } else {
                    //Prevent users from inputting a specific domain...
                    $notallowed = (
                        'mydomain.com',
                    );
                    $parts = explode('@', $Temail); //Separate string by @ characters (there should be only one)
                    $domain = array_pop($parts); //Remove and return the last part, which should be the domain
                    if ( ! in_array($domain, $notallowed)) {

                        //checks database to make sure the email is not a duplicate
                        $stmt1 = $pdo->prepare("SELECT * FROM emailTable WHERE email=?");
                        $stmt1->execute(($Temail));
                        $user = $stmt1->fetch();
                        if($user) {
                            //prevents adding a duplicate email
                            header("Location: duplicate.html");
                            exit (0);
                        } else {
                            //generate Activation code
                            $Acode = md5(time().$Temail);
                            
                            //send verification email
                            $emailfrom = 'no-reply@mydomain.com';
                            $fromname = 'MY NAME';
                            $subject = 'Confirm Your Email Subscription';
                            $emailbody = "
                                <html>
                                <body style='background-color: #000; padding: 15px;'>
                                    <table style='background-color: #222;'>
                                        <tr style='background-color: #333; padding: 15px; font-size: 1.3rem;'>
                                            <td><h2 style='color: #FFF;' align='center'>Please Verify Subscription</h2></td>
                                        </tr>
                                        <tr>
                                            <td style='color: #FFF; font-size: 1.1rem;' align='center'>
                                                <br/>
                                                <br/>
                                                If you didn't sign up for my email list, simply delete this message. You will not be added unless you push the button below.
                                                <br/>
                                                <br/>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td style='color: #FFF; font-size: 1.3rem;' align='center'>
                                                <button style='background-color: #000; width: 6rem; height: 2rem;'><a href='https://www.MYDOMAIN.com/verify.php?acode=$Acode' style='color: #F00; text-decoration: none; font-size:1rem;'>VERIFY</a></button>
                                                <br/>
                                                <br/>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td style='color: #FFF; font-size: 1.1rem;' align='center'>
                                                <font style='font-size:0.8rem;'>This email was automatically generated from a mailbox that is not monitored.</font>
                                            </td>
                                        </tr>
                                    </table>
                                </body>
                                </html>";
                                
                            $headers = "Reply-To: MY NAME <no-reply@MYDOMAIN.com>rn"; 
                            $headers .= "Return-Path: MY NAME <no-reply@MYDOMAIN.com>rn"; 
                            $headers .= "From: MY NAME <no-reply@MYDOMAIN.com>rn";  
                            $headers .= "MIME-Version: 1.0rn";
                            $headers .= "Content-type: text/html; charset=UTF-8rn";
                            $headers .= "X-Priority: 3rn";
                            $headers .= "X-Mailer: PHP". phpversion() ."rn" ;
        
                            $params = '-f ' . $emailfrom;
                            $send = mail($Temail, $subject, $emailbody, $headers, $params); // $send should be TRUE if the mail function is called correctly
                            if($send) {
                                //add the new email and other data to the database
                                $sql = "INSERT INTO emailTable (IP4, datetime, email, acode) VALUES (:IP4, :datetime, :email, :acode)";
                                $stmt2 = $pdo->prepare($sql);
                                $stmt2->execute(('IP4' => $userIP4, 'datetime' => $dateTime, 'email' => $Temail, 'acode' => $Acode));
                                $userIP4 = "";
                                $dateTime = "";
                                $Temail = "";
                                $Acode = "";
                                header("Location: success.html");
                                exit (0);
                            } else {
                                header("Location: invalid.html");
                                exit (0);
                            }
                        }
                    } else {
                        header("Location: notallowed.html");
                        exit (0);
                    }
                }
            }
        } else {
            header("Location: invalid.html");
            exit (0);
        }
    } else {
        header("Location: invalid.html");
        exit (0);
    }
    //2 END
    ?>

Let’s discuss in pieces:

1. Database Connection —

As far as I know, this is the correct way to set up the connection. I’ve found that about half of people recommend including the DB connection in each php file and the other half of people recommend having the connection in a separate file of its’ own and then linking it with require_once(). I’ve decided to include a database connection in each file.

2. Setting the Date and Time —

3. Setting and validating IPv4 variable —

I’m most unsure of this section. Through testing, I’ve found that this accurately stores the user’s ipv4 address into my database, but I feel that it may be insufficent from a security standpoint. I’ve attempted to store ipv6 addresses, but the code that I tried (found in the note) gave me an output that looked nothing like an ipv6 address, but perhaps it was correct. More info on this would be much appreciated. For the working ipv4 code, I’ve found that removing the gethostbyname() makes no difference to the output, so would removing this be acceptable, or should it be replaced with something else? Again, it works.

4. Validating the User’s Email Input —

Here’s a long one, but let’s go through it step by step. I used to use filter_var($email, FILTER_VALIDATE_EMAIL), but found that there was no way to make it so this allowed for international characters, so I got rid of it and replaced it with what you see. First we run the $email string through htmlspecialchars($_POST('email')); and a trim to replace certain characters and get rid of whitespace on either end. Next we put the $Temail string through the preg_match() regex in order to allow international characters. Does this also protect against OS Command Attacks?? Next we use the $email_host variable to check to see if the domain is real or not. I’ve tested it against gmail.com, yahoo.com, protonmail.com, my own domain and many others, and it seems to work well. Next I use an array ($notallowed), two variables and an if statement to prevent users from inputting my own domain, or any other domain that I include in the array. If the input matches any of these domains, then they are sent to notallowed.html. Next we check the database to see if the email is already in the database. If it is, then the user is directed to duplicate.html. Phew.

5. Generate verification code and send it in an email —

I decided to combine md5 with the time and the user’s trimmed email in order to create the verification code. The html email is pretty straight forward. I read a bit about how you basically need to pretend it’s 1999 when using html for emails. This led me to format it with tables and inline css. The verification code that is generated is placed in a button that reads <a href='https://www.MYDOMAIN.com/verify.php?acode=$Acode'>. The link uses a second php script (verify.php) that I’ll discuss later on. One of my major goals for this section was preventing the verification email from going to spam! I include a number of headers in the email, which, to the best of my knowledge, are enough to prevent this. Testing on gmail, yahoo, protonmail and my own domain have proven their effectiveness.

6. Adding everything to the database —

Additional Goals:

1. Storing IPv6 Addresses — As it stands, the code that I have accurately stores the user’s IPv4 address. What I haven’t been able to figure out is how to store the Ipv6 address. I tried the following: $userIP6 = bin2hex(inet_pton($_SERVER('REMOTE_ADDR')));
this inserted a string in the table (a VARBINARY column) that looked nothing like an IPv6 address, but remained consistent after each test…

Questions:

  • What changes/additions should be made to strengthen security?
  • I use filter_var($userIP4, FILTER_VALIDATE_IP) to validate the IP.
    Is this sufficient? Would the user having a VPN affect the
    functionality of this??
  • Is the regex that I used
    (preg_match("/^(_a-z0-9-)+(.(_a-z0-9-)+)*@(a-z0-9-)+(.(a-z0-9-)+)*(.(a-z){2,3})$^/", $Temail)) helpful in preventing OS Command Attacks? If not, what
    could be added to do so?