PHP Form doesn't insert into MySQL

Question

I'm trying to make a form and upload that into my database, but it doesn't work.
This is my HTML Code:

<form name="Form-Request" method="post" action ="icn/form.php">
    <div>
        <p><input type = "text" placeholder="Name" name = "name"></p>
        <p><input type = "email" placeholder="Email-address" name = "email"></p>
        <p><input type = "text" placeholder="Virtual Airline" name = "va"></p>
        <p><input type = "text" placeholder="Virtual Airline (IATA Code)" name = "va-iata"></p>
        <p><select name="pricing">
            <option>Free</option>
            <option>Business</option>
            <option>Pro</option>
        </select></p>
        <p><select name="vam-vms">
            <option>PHPVMS</option>
            <option>VAM</option>
        </select></p>
        <p><input type = "text" placeholder="Additional Info" name = "add-info"></p>

        <input class="button-primary" type="submit" value="Submit">

and this is my PHP post file, I have entered the database details but I don't want to share it :):

<?php
$name = $_POST['name'];
$email = $_POST['email'];
$va = $_POST['va'];
$vaiata = $_POST['va-iata'];
$pricing = $_POST['pricing'];
$vamvms = $_POST['vam-vms'];
$additionalinfo = $_POST['add-info'];

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO request_site (Name, Email Address, Virtual Airline, Virtual Airline IATA, Pricing, VAM/PHPVMS, Additional info)
VALUES ('$name', '$email', '$va', '$vaiata', '$pricing', '$vamvms', '$additionalinfo' )";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Thanks.

What do you mean with _it doesn't work_ ? You're open to SQL Injection — Sfili_81, Jan 24 '19 at 14:35
Columns usually do not have spaces. If the column names in your table have spaces, you'll need to surround the column names with backticks. Checking explicitly for [mysqli errors](http://php.net/manual/en/mysqli.error.php) will point out what is wrong with your query. — aynber, Jan 24 '19 at 14:35
Surround all of your table columns with backticks. `INSERT INTO request_site (\`Name\`, \`Email Address\`, \`Virtual Airline\`, \`Virtual Airline IATA\`, \`Pricing\`, \`VAM/PHPVMS\`, \`Additional info\`)` if those are your actual table columns. — GrumpyCrouton, Jan 24 '19 at 14:36
Also, you are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur in your values,** but will not help with column names. — aynber, Jan 24 '19 at 14:36
I've never had to use backticks in queries which is why I get instant headache when I see them in code :o — user3647971, Jan 24 '19 at 14:37

score 0 · Answer 1 · answered Jan 24 '19 at 14:54

There are a few things that you should do with this code.

First, since you're trying to use every field in your form, you should add the required attribute to each input field.

Second, since you're submitting a post request, I would suggest that you check in your PHP script if the request is a POST and check if the values you're attempting to get from the POST exist. This will prevent someone from simply navigating to your script and trying to execute it improperly:

<?php
if (
    $_SERVER['REQUEST_METHOD'] === 'POST' && 
    array_key_exists('name', $_POST) &&
    array_key_exists('email', $_POST) &&
    array_key_exists('va', $_POST) &&
    array_key_exists('va-iata', $_POST) &&
    array_key_exists('pricing', $_POST) &&
    array_key_exists('vam-vms', $_POST) &&
    array_key_exists('add-info', $_POST)
  ) {
  /* ... */  
} else {
  http_response_code(401);
}

?>

Third, don't use mysqli. The PDO class has been out since PHP 5.1.0 and is the preferred method of connecting to databases. In this case, use a Try/Case statement to attempt to establish a PDO connection:

try {
  /* database configuration */
  $servername = "host";
  $username = "username";
  $password = "password";
  $dbname = "dbname";

  /* establish a PDO connection */
  $dsn =  "mysql:dbname=$dbname;host=$servername;charset=utf8mb4";
  $db  =  new PDO($dsn, $username, $password);
  $db  -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db  -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  /* run SQL query */
} catch(PDOException $ex) {
  /* handle database error */
}

Fourth, you'll need to tweak your SQL statement a bit, I think that this is where it is causing some errors. It is generally best practice to escape the column name using back-ticks, but in this case since it looks like your column names have spaces in them, it is actually necessary (plus name might be a keyword too). It is also best practice to use parameters as to prevent for SQL injection. The idea is that you pass a user-defined name in the SQL query with a : prefix and that name corresponds to a value defined in an array that gets passed:

/* Insert all the values in the request_site */
$stmt = $db->prepare('
INSERT INTO `request_site` (
  `Name`,
  `Email Address`,
  `Virtual Airline`,
  `Virtual Airline IATA`,
  `Pricing`,
  `VAM/PHPVMS`,
  `Additional info`
) VALUES (
  :name,
  :email,
  :va,
  :vaiata,
  :pricing,
  :vamvms,
  :additionalinfo
);
');

$stmt->execute(array(
  ':name'           => $name,
  ':email'          => $email,
  ':va'             => $va,
  ':vaiata'         => $vaiata,
  ':pricing'        => $pricing,
  ':vamvms'         => $vamvms,
  ':additionalinfo' => $additionalinfo
));

Put it all together and you get:

<?php
if (
    $_SERVER['REQUEST_METHOD'] === 'POST' && 
    array_key_exists('name', $_POST) &&
    array_key_exists('email', $_POST) &&
    array_key_exists('va', $_POST) &&
    array_key_exists('va-iata', $_POST) &&
    array_key_exists('pricing', $_POST) &&
    array_key_exists('vam-vms', $_POST) &&
    array_key_exists('add-info', $_POST)
  ) {
    /* put the $_POST values in variables */
    $name = $_POST['name'];
    $email = $_POST['email'];
    $va = $_POST['va'];
    $vaiata = $_POST['va-iata'];
    $pricing = $_POST['pricing'];
    $vamvms = $_POST['vam-vms'];
    $additionalinfo = $_POST['add-info'];

  try {
    /* database configuration */
    $servername = "host";
    $username = "username";
    $password = "password";
    $dbname = "dbname";

    /* establish a PDO connection */
    $dsn =  "mysql:dbname=$dbname;host=$servername;charset=utf8mb4";
    $db  =  new PDO($dsn, $username, $password);
    $db  -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db  -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    /* Insert all the values in the request_site */
    $stmt = $db->prepare('
    INSERT INTO `request_site` (
      `Name`,
      `Email Address`,
      `Virtual Airline`,
      `Virtual Airline IATA`,
      `Pricing`,
      `VAM/PHPVMS`,
      `Additional info`
    ) VALUES (
      :name,
      :email,
      :va,
      :vaiata,
      :pricing,
      :vamvms,
      :additionalinfo
    );
    ');

    $stmt->execute(array(
      ':name'           => $name,
      ':email'          => $email,
      ':va'             => $va,
      ':vaiata'         => $vaiata,
      ':pricing'        => $pricing,
      ':vamvms'         => $vamvms,
      ':additionalinfo' => $additionalinfo
    ));

    echo 'New record created successfully';
  } catch(PDOException $ex) {
    /* handle database error */
    echo 'Connection failed: ', $ex->getMessage();
  }
} else {
  http_response_code(401);
}

?>

I don't see anything in my database... I don't get a message when I've submitted the form — Jan, Jan 24 '19 at 15:42
@Jan - add the following statement before the `http_response_code`: `echo 'The method is either not a POST or one of the POST values is missing'; exit();` — David, Jan 24 '19 at 16:11
It doesn't work.. I don't see anything in my database and it takes me to the next section of the page. — Jan, Jan 24 '19 at 19:41

PHP Form doesn't insert into MySQL

1 Answers1