Cant upload to database with a form

Question

I'm trying to upload data to a database with a form. It worked with my other form but when I try to use the same template I get the following errors;

Error: INSERT INTO formData (nameTeacher, nameChild, email, age, date, comment) VALUES (the values i filled in the form, they are correct)

Does anyone know what I am doing wrong and why I am getting this error?

<?php
//Check if Post isset, else do nothing
if (isset($_POST['submit'])) {
    //Require database in this file & image helpers
    require_once "database_connect.php";


    //Postback with the data showed to the user, first retrieve data from 'Super global'
    $nameTeacher   = mysqli_escape_string($db, $_POST['nameTeacher']);
    $nameChild = mysqli_escape_string($db, $_POST['nameChild']);
    $email  = mysqli_escape_string($db, $_POST['email']);
    $age   = mysqli_escape_string($db, $_POST['age']);
    $date = mysqli_escape_string($db, $_POST['date']);
    $comment = mysqli_escape_string($db, $_POST['comment']);


    //Require the form validation handling
    require_once "form_validation.php";



    if (empty($errors)) {

        //Save the record to the database
        $query = "INSERT INTO formData (nameTeacher, nameChild, email, age, date, comment)
                  VALUES ('$nameTeacher', '$nameChild', '$email', $age, $date, '$comment')";
        $result = mysqli_query($db, $query)
        or die('Error: '.$query);

        if ($result) {
            header('Location: index.php');
            exit;
        } else {
            $errors[] = 'Something went wrong in your database query: ' . mysqli_error($db);
        }

        //Close connection
        mysqli_close($db);
    }
}
?>
  <!doctype html>
  <html lang="en">

  <head>
    <title>Music Collection Create</title>
    <meta charset="utf-8" />
    <link rel="stylesheet" type="text/css" href="css/style.css" />
  </head>

  <body>
    <h1>Maak een reservering!</h1>

    <!-- enctype="multipart/form-data" no characters will be converted -->
    <form action="" method="post" enctype="multipart/form-data">
      <div class="data-field">
        <label for="nameTeacher">Naam docent:</label>
        <input id="nameTeacher" type="text" name="nameTeacher" value="<?= isset($nameTeacher) ? htmlentities($nameTeacher) : '' ?>" />
        <span class="errors"><?= isset($errors['nameTeacher']) ? $errors['nameTeacher'] : '' ?></span>
      </div>
      <div class="data-field">
        <label for="nameChild">Naam leerling</label>
        <input id="nameChild" type="text" name="nameChild" value="<?= isset($nameChild) ? htmlentities($nameChild) : '' ?>" />
        <span class="errors"><?= isset($errors['nameChild']) ? $errors['nameChild'] : '' ?></span>
      </div>
      <div class="data-field">
        <label for="email">Email:</label>
        <input id="email" type="text" name="email" value="<?= isset($email) ? htmlentities($email) : '' ?>" />
        <span class="errors"><?= isset($errors['email']) ? $errors['email'] : '' ?></span>
      </div>
      <div class="data-field">
        <label for="age">Leeftijd van de leerling:</label>
        <input id="age" type="number" name="age" value="<?= isset($age) ? htmlentities($age) : '' ?>" />
        <span class="errors"><?= isset($errors['age']) ? $errors['age'] : '' ?></span>
      </div>
      <div class="data-field">
        <label for="date">Datum:</label>
        <input id="date" type="date" name="date" value="<?= isset($date) ? htmlentities($date) : '' ?>" />
        <span class="errors"><?= isset($errors['date']) ? $errors['date'] : '' ?></span>
      </div>
      <div class="data-field">
        <label for="comment">Extra toevoeging (optioneel):</label>
        <input id="comment" type="text" name="comment" value="<?= isset($comment) ? htmlentities($comment) : '' ?>" />
      </div>
      <div class="data-submit">
        <input type="submit" name="submit" value="Reservering plaatsen" />
      </div>
    </form>
    <div>
      <a href="index.php">Go back to the list</a>
    </div>
  </body>

  </html>

You should look into using parameterized [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of manually escaping and injecting the user data into the queries like that. — M. Eriksson, Dec 14 '20 at 15:44
Put the error message in the `or die` line, not just the query. — Barmar, Dec 14 '20 at 15:45
Error: INSERT INTO formData (nameTeacher, nameChild, email, age, date, comment) VALUES ('Jan', 'Karen', 'example@gmail.com', 2020-12-12, 'hallo!' — Mich VDW, Dec 14 '20 at 15:46
That's the query, not the error message. Change it to `or die ("Error: " . mysqli_error($db));` — Barmar, Dec 14 '20 at 15:47
You're missing the quotes around the date. However, this would be a non-issue if you used parameterized prepared statements instead (which you should be.) You can also let [mysqli throw exceptions](https://stackoverflow.com/a/14578644/2453432) in case of errors. — M. Eriksson, Dec 14 '20 at 15:47

Ken Lee · Answer 1 · 2020-12-14T16:16:38.483

Please remove all calls to mysqli_escape_string.

and

Please change the line

$query = "INSERT INTO formData (nameTeacher, nameChild, email, age, date, comment)
                  VALUES ('$nameTeacher', '$nameChild', '$email', $age, $date, '$comment')";
        $result = mysqli_query($db, $query)
        or die('Error: '.$query);

to

$sql = "INSERT INTO formData (nameTeacher, nameChild, email, age, date, comment)
                  VALUES (?, ? , ? , ? , ?)";
$stmt= $db->prepare($sql);
$stmt->bind_param("ssssss", $nameTeacher, $nameChild, $email, $age, $date, $comment);
$stmt->execute();

The above change is to use parameterized prepared statements instead of direct SQL.

Compared to executing SQL statements directly, prepared statements have three main advantages:

Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times)

Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query

Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

For your case, actually you have also missed the quotation marks for the date field. In using the prepared statement this problem can be avoided.

The calls to `mysqli_escape_string` should also be removed if prepare statements are to be used. Also, adding an explanation why this approach is better and how it fixes the issue would add value to your answer. Provided that we ignore the fact this question is a duplicate, asked for about the millionth time. — El_Vanja, Dec 14 '20 at 16:03

Cant upload to database with a form

1 Answers1