html – Can't get PHP to process form data into MySQL database-ThrowExceptions

Exception or error:

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);
    ?>
How to solve:

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();
?>

###

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.

Leave a Reply

Your email address will not be published. Required fields are marked *