Can't get PHP to process form data into MySQL database

Question

I'm trying to process some HTML form data into a MySQL database using some PHP, but this is my first foray into webdev and I think I'm in over my head. The form is POSTed to the formSubmit.php file, which turns them into the variables that the sql command then queries. I've tried changing round the variable layout, but it still won't send for some reason.

The HTML form:

<form class="middleForm" name="pizzaGuest" action="formSubmit.php" method="POST">
<fieldset>
    <legend>Guest details</legend>
    First name:<br>
        <input type="text" name="firstName" required><br>
    Last name:<br>
        <input type="text" name="lastName" required><br>
    Email address:<br>
        <input type="email" name="email" required><br>
    Party date:<br>
        <input type="date" name="date" required><br>
    Diet:<br>
        <select name="diet">
            <option value="omnivore" selected>Omnivore</option>
            <option value="pescatarian">Pescatarian</option>
            <option value="vegetarian">Vegetarian</option>
            <option value="vegan">Vegan</option>
        </select><br>
    Dairy free?<br>
        <input type="checkbox" name="dairyFree"><br>
    Toppings:<br>
        <input type="text" name="toppings"><br>
    Allergies:<br>
        <input type="text" name="allergies"><br>
    <input type="submit" value="Submit">
</fieldset>
</form>

formSubmit.php:

<?php
        $servername = "localhost";
        $username = "partyForm";
        $password = "████████████";
        $dbname = "pizza";
        $conn = mysqli_connect($servername, $username, $password, $dbname);
        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        }

        $FirstName = $_POST["firstName"];
        $LastName = $_POST["lastName"];
        $Diet = $_POST["diet"];
        $Allergies = $_POST["allergies"];
        $Email = $_POST["email"];
        $DairyFree = $_POST["dairyFree"];

        $sql = "REPLACE INTO guests (FirstName, LastName, Diet, Allergies, Email, DairyFree) VALUES ($FirstName, $LastName, $Diet, $Allergies, $Email, $DairyFree);";

        mysql_query($sql)
        mysqli_close($conn);
    ?>

The sql is vulnerable to SQL injection and unless the variables given in the sql are all integers then they would, at the very least, need to be quoted. Use `prepared statements` to mitigate such situations — Professor Abronsius, Jan 11 '20 at 13:36
Turn on all error reporting so you will see any errors that might be happening. `ini_set('display_errors','1');ini_set('display_startup_errors','1');error_reporting(E_ALL);mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` — Dave, Jan 11 '20 at 13:41

score 1 · Accepted Answer · answered Jan 11 '20 at 13:42

You might try using prepared statements instead as they proect against sql injection and avoid the need to add quotes as your sql omits.

<?php

    $servername = "localhost";
    $username = "partyForm";
    $password = "xxx";
    $dbname = "pizza";

    $conn = new mysqli( $servername, $username, $password, $dbname );
    if( !$conn ) die("Connection failed");

    $sql = "replace into guests ( `firstname`, `lastname`, `diet`, `allergies`, `email`, `dairyfree` ) values (?,?,?,?,?,?);";
    $stmt=$conn->prepare($sql);
    $stmt->bind_param('ssssss',$_POST["firstName"], $_POST["lastName"], $_POST["diet"], $_POST["allergies"], $_POST["email"], $_POST["dairyFree"] );
    $stmt->execute();
    $stmt->close();
    $conn->close();
?>

score -1 · Answer 2 · answered Jan 11 '20 at 13:45

For a best usage and confort, check the PDO driver for MySQL instead of mysql. With this method, you can perform prepared statements easily.

The connection with this driver will be:

$dbh = null;

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
} catch (PDOException $e) {
    print "Erreur !: " . $e->getMessage() . "<br/>";
    die();
}

$stmt = $dbh->prepare("REPLACE INTO guests (FirstName, LastName, Diet, Allergies, Email, DairyFree) VALUES (:FirstName, :LastName, :Diet, :Allergies, :Email, :DairyFree);");
$stmt->bindParam(':FirstName', $FirstName);
$stmt->bindParam(':LastName', $LastName);
$stmt->bindParam(':Diet', $Diet);
$stmt->bindParam(':Allergies', $Allergies);
$stmt->bindParam(':Email', $Email);
$stmt->bindParam(':DairyFree', $DairyFree);
$stmt->execute();

// Close the connection at the end of your queries
$dbh->close();
$dbh = null;

This the best approach to secure your code and minimize the risk go SQL injections.

OP uses MySQLi, not PDO. Do not catch exceptions. There's no close method in PDO. You should enable PDO exception error reporting. — Dharman, Feb 25 '20 at 08:38

Can't get PHP to process form data into MySQL database

2 Answers2