SQLite insert upon page refresh-CodePudding

I have few input types on a form and a submit button as following:

<form id="register_form" method="post" role="form" action="">
    <input autofocus="" id="firstname" name"firstname" placeholder="First Name" type="text" required />
    <textarea id="Address" name="Adress" placeholder="No Address" type="text" rows="3" cols="30"></textarea>
    <select id="country" name="country" required>
            <option selected>Choose</option>
            <option value="10">Germany</option>
            <option value="11">Poland</option>
            <option value="12">United Kingdom</option>
    </select>
    <button type="submit">Submit My New Entry</button>
</form>

Also i have a PHP code for submitting my data to a SQLite database:

<?php
try {
    //open the database
    $db = new PDO('sqlite:db/users.db');

    $firstname = $_POST["firstname"]; 
    $Address = $_POST["Address"];
    $country = $_POST["country"];
    
    //Insert record  
    $db->exec("INSERT INTO details 
                        (firstname, Address, country) 
                VALUES ('$firstname', '$Address', '$country')");

    $db = NULL;
} catch(PDOException $e) {
    print 'Exception : ' .$e->getMessage();
}
?>

My problem is that every time i refresh the page the script cause to register a new entry into my databse with empty fields, even if some input fields are marked as required or the databse table is set to rollback on NULL value (both are ignored). How can i avoid this?

PS. required works only if i press the submit button.

CodePudding user response：

Your PHP script contains both the PHP and HTML (the form), so if you load this script, the PHP script will be triggered immediately, which will insert a blank record

(1) So, you should slightly amend the page so that only if there is form submission by POST (when someone submits the form), then to do the PHP part, by adding say :

if (isset($_POST["firstname"])){
// the PHP codes
}

(2) There is a typo in your HTML form, name="Adress" should be name="Address"

(3) Please use parameterized prepared statement in your PHP insert query to avoid SQL injection attacks

So the code is:

<?php

if (isset($_POST["firstname"])){

try {
    //open the database
    $pdo = new PDO('sqlite:db/users.db');

    $firstname = $_POST["firstname"]; 
    $Address = $_POST["Address"];
    $country = $_POST["country"];
    
$data = [
    'firstname' => $firstname,
    'Address' => $Adddress,
    'country' => $country,
];

$sql="INSERT INTO details (firstname, Address, country) VALUES (:firstname, :Address, :country)";

$stmt= $pdo->prepare($sql);
$stmt->execute($data);

$pdo = NULL;
} catch(PDOException $e) {
    print 'Exception : ' .$e->getMessage();
}}
?>

<form id="register_form" method="post" role="form" action="#">
<table border=0><tr><td>
First Name: 
<td>
<input autofocus="" id="firstname" name="firstname" placeholder="First Name" type="text" required />
<tr><td>
Address: 
<td><textarea id="Address" name="Address" placeholder="No Address" type="text" rows="3" cols="30"></textarea>
<tr><td>
Country: <td>
<select id="country" name="country" required>
            <option selected>Choose</option>
            <option value="10">Germany</option>
            <option value="11">Poland</option>
            <option value="12">United Kingdom</option>
</select>
<tr><td><button type="submit">Submit My New Entry</button>
</form>

CodePudding user response：

You just need to block the code from running.

This code, checks for the POST data and checks so there is content. Then runs the database processing.

PHP

<?php

function processData()
{
  try
  {
    list($firstname, $address, $country) = $_POST;
  
    $db = new PDO('sqlite:db/users.db');
    $db->exec("INSERT INTO details (firstname, address, country) VALUES ('$firstname', '$address', '$country')");
  }
  catch (PDOException $e)
  {
    printf("Exception : %s", $e->getMessage());
  }
}

if (
  (isset($_POST["firstname"]) && !empty($_POST["firstname"]))
  && (isset($_POST["address"]) && !empty($_POST["address"]))
  && (isset($_POST["country"]) && !empty($_POST["country"]))
)
{
  processData();
}

?>

HTML:

<form
  action=""
  id="register_form"
  method="post"
  role="form"
>
  <input
    autofocus
    id="firstname"
    name"firstname"
    placeholder="First Name"
    type="text"
    required
  />
  <textarea
    id="address"
    name="address"
    placeholder="No address"
    type="text"
    rows="3"
    cols="30"
  ></textarea>
  <select
    id="country"
    name="country"
    required
  >
    <option disabled selected>Choose</option>
    <option value="10">Germany</option>
    <option value="11">Poland</option>
    <option value="12">United Kingdom</option>
  </select>
  <button type="submit">Submit My New Entry</button>
</form>