# Inserting users using PDO prepared statements

I made a small script to update fields in a database. I'm using PDO to connect to MySQL. All the business logic of PHP is in the top half of the file, and the form is at the bottom.

Here is the full code. If it's a POST request, it opens the PDO connection and inserts the values from the form.

<?php

/**
* Use an HTML form to update the users table
* with new entries.
*
*/

if ($_POST) { require_once "config.php"; require_once "common.php"; try {$connection = new PDO($dsn,$username, $password,$options);

$sql = "INSERT INTO users (firstname, lastname, email, age, location) VALUES (:firstname, :lastname, :email, :age, :location)";$statement = $connection->prepare($sql);

$statement->execute(array( "firstname" =>$_POST['firstname'],
"lastname"  => $_POST['lastname'], "email" =>$_POST['email'],
"age"       => $_POST['age'], "location" =>$_POST['location']
));

}

catch(PDOException $error) { echo$sql . "<br>" . $error->getMessage(); } } ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="x-ua-compatible" content="ie=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="css/primitive.css"> <title>Update Users</title> </head> <body> <div class="small-container"> <h1>Add a user</h1> <form method="post"> <label for="firstname">First Name</label> <input type="text" name="firstname" id="firstname"> <label for="lastname">Last Name</label> <input type="text" name="lastname" id="lastname"> <label for="email">Email Address</label> <input type="text" name="email" id="email"> <label for="age">Age</label> <input type="text" name="age" id="age"> <label for="location">Location</label> <input type="text" name="location" id="location"> <input type="submit" value="Submit"> </form> </div> </body> </html>  I know I can sanitize HTML input by doing something like: function escape($html)
{
return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8"); } // example use escape($_POST['firstname']);


I'm wondering how necessary it is when I'm using a prepared statement. I'm still a bit confused about sanitization and filtering, and when it's necessary. I know it's necessary when printing data out to HTML, but not sure how much when receiving input to PDO.

Please forgive any and all ignorance. I'm writing this from scratch and doing my best to teach myself PHP without frameworks.

Do not escape SQL query parameters when using PDO. PDO will do the right thing. If you call unnecessary escaping functions "just in case", then you end up mangling the data. That's how you get junk like "this &amp; that" appearing in some broken web pages.

When would you want to escape text using htmlspecialchars()? When outputting text to be embedded in HTML. For example,

 <input type="text" name="firstname" id="firstname" value="<?php echo escape($_POST['firstname']); ?>">  If you didn't call htmlspecialchars() on $_POST['firstname'], then a malicious user could break the page by inputting a name that contained double-quotes.

• Ah, beautiful. This is what I assumed, but I wanted to be sure before I did it. So if I had my form in one file, and was posting to another via action="update.php (for example), I'd use the escape method to protect that input data. Makes sense to me! Thanks. – Tania Rascia May 10 '17 at 19:55

You should be doing field-level validation for all of your input fields before inserting into database (and possibly field-level validation in javascript to even help prevent POST from happening with invalid values).

Sanitization and validation are two different things and both are probably applicable to your typical "input form which inserts into database" use case.

The prepared statement will mitigate against SQL injection attacks and alleviate the need to escape characters for database insertion, but it will not do things such as:

• verify that the email is valid
• verify that age is integer (or integer string)
• verify that name fields are not blank
• etc.

It also does not sanitize against:

• potential XSS attack vectors
• overflow attack vectors
• etc.

You should not even attempt a database insert without knowing you have been passed valid data that has been appropriately sanitized.

Some other thoughts:

• Don't echo out system-level error messages directly to end users. First, they are not user-friendly. Second, they could leak out information you don't want getting out there.
• 'htmlspecialchars()' is not an escaping function. It is an encoding function used for rendering content into HTML documents.
• You should get in the habit of using session tokens in all your input forms to mitigate against cross-site request forgery (CSRF) attacks.
• Thanks! I do know I should be doing some JavaScript side validation, but I wanted to make sure the security aspect from the PHP end was secure first. So, are you saying I should change $_POST['email'] to filter_var($_POST['email'], FILTER_SANITIZE_EMAIL), for example? Thanks for the information, all very useful and informative. – Tania Rascia May 10 '17 at 20:17
• filter_var(), filter_input(), filter_input_array() would all be good options. – Mike Brant May 10 '17 at 20:22
• Downvoted for confusing a beginner by suggesting that protecting against XSS is necessary. That is the kind of FUD that leads beginners to apply all kinds of escapes just in case. There is no XSS problem here, and nothing should be done to guard against it. – 200_success May 10 '17 at 20:51
• I don't see why htmlspecialchars() should not be considered an escaping function. – 200_success May 10 '17 at 20:52
• Thanks @200_success, I wanted to avoid the issue of putting a bunch of escapes and sanitization in places they don't belong. – Tania Rascia May 10 '17 at 21:11