simple PDO insert sql insert form

webdeveloper123 · July 20, 2022

Hi Guys,

I've decided to try my hand at PDO moving on from mysqli as it's considered much better and more secure. I have the script below and If I don't comment out the include functions.php line I get a friendly error message. But when I do comment it out I get this:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: 
Invalid parameter number: parameter was not defined in /var/www/vhosts/xxx.xxx/xxx.xxx.xx/customer/insert.php:36 
Stack trace: #0 /var/www/vhosts/xxx.xx/xx.xx.xxx/customer/insert.php(36):
PDOStatement->execute(Array) #1 {main} thrown in 
/var/www/vhosts/xxx.xx/xxxx.xx.xx/customer/insert.php on line 36

Here is the code:

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> Document </title>
</head>
<body>



<?php

include 'includes/db.php';
//include 'includes/functions.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

  $sql = "INSERT INTO customer_details (first_name, last_name, address, town, county, post_code, fav_food, birthdate, email, terms) 
          VALUES (:first_name, :last_name, :address, :town, :county, :post_code, :fav_food, :birthdate, :email, :terms);";


$customers['fname'] = $firstName = $_POST['fname'];
$customers['lname'] = $lastName = $_POST['lname'];
$customers['address'] = $address = $_POST['address'];
$customers['town'] = $town = $_POST['town'];
$customers['county'] = $county = $_POST['county'];
$customers['postcode'] = $postCode = $_POST['postcode'];
$customers['fav_food'] = $favouriteFood = $_POST['fav_food'];
$customers['birthday'] = $birthdate = $_POST['birthday'];
$customers['email'] = $email = $_POST['email'];
$customers['terms'] = $terms   = (isset($_POST['terms']) and $_POST['terms'] == true) ? true : false;



$statement = $pdo->prepare($sql);
$statement->execute($customers);


}

?>
 
<form action="insert.php" method="post">
  <label for="fname">First name:</label><br>
  <input type="text" id="fname" name="fname"><br>
  
  
  <label for="lname">Last name:</label><br>
  <input type="text" id="lname" name="lname"><br>
  
  <label for="address">Address</label><br>
  <input type="text" id="address" name="address"><br>

  
  <label for="town">Town</label><br>
  <input type="text" id="town" name="town"><br>
  
  <label for="county">County</label><br>
  <input type="text" id="county" name="county"><br>
  
  <label for="postcode">Post Code</label><br>
  <input type="text" id="postcode" name="postcode"><br><br>
  

  <label for="food">What is your favourite food?</label><br>
  <input type="radio" id="burger" name="fav_food" value="Burgers">
  <label for="burger">Burgers</label><br>
  <input type="radio" id="pizza" name="fav_food" value="Pizza">
  <label for="pizza">Pizza</label><br>
  <input type="radio" id="kebab" name="fav_food" value="Kebabs">
  <label for="kebab">Kebabs</label><br><br>
  
  
  <label for="birthday">Birthday:</label>
  <input type="date" id="birthday" name="birthday"><br><br>
  
  <label for="email">Email</label><br>
  <input type="text" id="email" name="email"><br><br>
  
  <input type="checkbox" id="terms" name="terms" value="true">
  <label for="terms">I agree to the terms.</label><br><br>

    <input type="submit" value="Submit">  
</form>

</body>
</html>

I'm pretty sure it's to do with the $customers array becuase most of the code is from the book

Many thanks

Edited July 20, 2022 by webdeveloper123

kicken · July 20, 2022

The parameter names in the query and the keys in your array need to match. You've defined your parameters for the name as first_name and last_name, but in your array you used fname and lname. Also post_code vs postcode, birthdate vs birthday.

webdeveloper123 · July 20, 2022

haha rookie error! Thanks kicken!

benanamen · July 20, 2022

Your double variable assignment is pointless and just litters the codebase. You already have the POST array, just use it. Also, the code could use some validation.

webdeveloper123 · July 21, 2022

I'm doing the validation today & I have already started to sanitize (on a diff page)

16 hours ago, benanamen said:

Your double variable assignment is pointless and just litters the codebase.

You mean most of this is not necessary?

$customers['first_name'] = $firstName = $_POST['fname'];

16 hours ago, benanamen said:

You already have the POST array, just use it.

Could you give me an example please?

mac_gyver · July 21, 2022

1 hour ago, webdeveloper123 said:

You mean most of this is not necessary?

correct. this is a waste of your time typing. you wrote out a line of code (10) for each input. if you had 30 or 100 inputs would writing out a line of code for each one make sense? all this is doing, except for the very last one, which will either be set or it won't, is copying variables to other variables for nothing. you should actually keep the form data as a set, in an array variable, then operate on elements in this array variable throughout the rest of the code. this will allow you to then dynamically process the data, rather than writing out repeated blocks of code that only differ in the input they operate on.

the only time you should create a new variable for a piece of data is if you modify the value in some way, so that it has a different meaning from the original. one such case is trimming the data. see the next point.

1 hour ago, webdeveloper123 said:

I have already started to sanitize

forget you ever saw the word sanitize related to data. except for trimming data, so that you can detect if it was all white-space characters, you should NOT modify data. you should only validate data. if the data is valid, use it. if it is not valid, setup a message for the visitor telling them what was wrong with the data so that they CAN correct the value and resubmit it if appropriate.

if you keep the data as a set, in an array variable, as suggested above, you can trim all the data at once using ONE single line of code. in this case, you would put the trimmed working copy of the form data into a different array variable name, such as $post, since it no longer has the same meaning as the original data.

additionally, you have a bunch more typing and keeping track of things than what is necessary, and some other points -

for the initial problem, using simple ? positional place-holders requires much less typing then using named place-holders and would have prevented the error entirely.
don't use multiple names for the same piece of data. this just creates more work for you in keeping track of what any particular piece of data is called. if first_name, last_name, post_code, ... are what the database column names are, just use those same names throughout the code and html markup.
since the form and form processing code are on the same page, simply leave out the entire action='...' attribute to cause the form to submit to the same page it is on.
you can include the form field between the <label></label> tags, letting you eliminate the for='...' and id='...' attributes.
use 'require' for things that your code must have for it to work.
the post method form processing code should be above the start of the html document.
when you validate the data, store the user/validation errors in an array using the field name as the array index.
after the end of all the validation logic, if the array holding the errors is empty, use the submitted form data.
the ; on the end of the sql query statement is no needed.
this query can/should produce a duplicate error for the field(s) that must be unique, such as the email. any field that must be unique should be defined in the database table as a unique index. any insert/update query that can result in a duplicate error should have an exception (since you are using exceptions for database errors) try/catch block. the catch code should test if the error number is for a duplicate index error, then setup a message for the visitor telling them exactly what was wrong with the data that they submitted. for all other error numbers, just rethrow the exception and let php handle it.
after the end of all the form processing logic, if there are no errors, redirect to the exact same url of the current page to cause a get request for the page. this will prevent the browser from trying to resubmit the form data if the visitor reloads the page or navigates away from and back to that url. this is the reason for putting the form processing code above the start of the html document.
if you want to display a one-time success message, store it in a session variable, then test, display, and clear that session variable at the appropriate location in the html document.
if there are errors at item #8, the code would continue on to display the html document, display any errors, redisplay the form, populating the form field values with any existing data so that the visitor doesn't need to keep reentering data over and over.
any value you output on the web page needs to have htmlentities applied to it to help prevent cross site scripting.

Edited July 21, 2022 by mac_gyver

webdeveloper123 · July 21, 2022

10 minutes ago, mac_gyver said:

any value you output on the web page needs to have htmlentities applied to it to help prevent cross site scripting.

Yes I used htmlspecialchars on my "display" page. Basically a page which shows all records from the table. This is what I meant for sanitizing.

Thank you that is a long list of things to think about.

Relating to my post before what should I be using instead of:

$customers['first_name'] = $firstName = $_POST['fname'];

$statement = $pdo->prepare($sql);
$statement->execute($customers);

Like what benanamen said, "just use the post array"

mac_gyver · July 21, 2022

$_POST['fname'] IS a perfectly fine php variable and already contains the value from that field. assuming you didn't need to do anything to that value that would change it before using it, just use that variable when needed. don't waste time making up other variable names and typing out code making two copies of that original variable.

programming IS a tedious typing activity. don't make it harder than it needs to be. his comment and most of what i wrote are about only doing necessary work/typing that contributes to the overall goal.

Barand · July 21, 2022

If, in your form html, you name your input fields customer[fname], customer[lname] etc.

when inserting you can then

$stmt = $pdo->prepare("INSERT INTO customer (first_name, last_name) VALUES (:fname, :lname)";
$stmt->execute($_POST['customer']);

webdeveloper123 · July 21, 2022

ok thanks guys

webdeveloper123 · July 27, 2022

On 7/21/2022 at 1:22 PM, mac_gyver said:

forget you ever saw the word sanitize related to data.

So your saying apart from htmlspecialchars, don't sanitize data, keep sending it back until it passes validation. And also, your saying don't use the built in sanitization filters?

webdeveloper123 · July 27, 2022

And what about when I create my Update sql form. Do I have to re-validate everything again? Or put it in an includes statement or something?

mac_gyver · July 27, 2022

20 minutes ago, webdeveloper123 said:

So your saying apart from htmlspecialchars, don't sanitize data

part of this issue is what your definition is of sanitize. i/we think of it as removing or eliminating the bad parts, e.g. to sanitize a surface to kill a virus on it. reread the rest of that paragraph. don't modify the actual piece of data, which changes the meaning of the data, then attempt to use it in your application.

let me tell you a story about a prior version of this forum software. the authors of this code rolled their own email address sanitize function that removed characters that THEY thought should be removed, but which were actually valid in email addresses. this resulted in users being able to create a valid email address at the same domain as an existing email address, such as gmail, that after sanitization, matched the email address of an administrator. they were then able to do a password recovery for that administrator account but which used their own email address. a copy of the user data was stolen.

applying htmlspecialchars/htmlentities to a piece of data when you output the value it in a html context, if only done at that point, is not modifying the actual data. it is converting any html special character to their html entities in the output, so that those html special characters cannot break the html syntax.

50 minutes ago, webdeveloper123 said:

keep sending it back until it passes validation

yes.

50 minutes ago, webdeveloper123 said:

don't use the built in sanitization filters?

yes. only use the validate filters, not the sanitize filters. if data is valid, you can use it for its intended purpose in the application. if it is not valid, let the user correct what is wrong with it and resubmit it. if the user is a bot/hacker, the extra junk they include in a value won't pass validation, but after being removed by sanitization, would allow the application to proceed to use the value.

mac_gyver · July 27, 2022

38 minutes ago, webdeveloper123 said:

And what about when I create my Update sql form. Do I have to re-validate everything again? Or put it in an includes statement or something?

i'm not sure what that means.

external data submitted to your site can come from anywhere, not just your form, can be anything, and cannot be trusted. you must validate all input data before using it. it doesn't matter what the actual operation being performed on that data is.

webdeveloper123 · July 27, 2022

I get what your saying.

5 minutes ago, mac_gyver said:

applying htmlspecialchars/htmlentities to a piece of data when you output the value it in a html context, if only done at that point, is not modifying the actual data.

yes that's all i've done with it

webdeveloper123 · July 27, 2022

1 minute ago, mac_gyver said:

you must validate all input data before using it.

so for example I've finished my insert sql form, all validated, everything working, really happy with it

But now I am going to create an update sql form. So do I have to revalidate the data? I already validated it in the insert sql, so now I have to do it again for the update form? Wouldn't that be repeating code again and again?

mac_gyver · July 27, 2022

39 minutes ago, webdeveloper123 said:

So do I have to revalidate the data?

of course. it's newly submitted data. it came from a source that's outside of your control.

39 minutes ago, webdeveloper123 said:

I already validated it in the insert sql,

you validated the specific data that was submitted and used for the insert query. this has nothing to do with the data that was submitted at a different time for an update query.

39 minutes ago, webdeveloper123 said:

Wouldn't that be repeating code again and again?

yes. programming IS a tedious, repetitious activity. this is why you need to always be on the lookout for ways of simplifying and reusing code, so that you end up being able to concentrate on the goal you are trying to achieve, rather then on the implementation details.

the only real difference in the data between the insert and the update code is the update code will have an id value, that the insert code didn't have.

Edited July 27, 2022 by mac_gyver

webdeveloper123 · July 27, 2022

ok thanks for your help

Sign In

simple PDO insert sql insert form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information