John Davidson

Message:

my code looks like this (just a example, at a few parts of my projekt there are a lot of variables more then in this example):

$pdo = new PDO('mysql:host=localhost;dbname=nameofdb;charset=utf8','dbuser','dbpass');



$surname = htmlspecialchars($_POST["surname"]);

$lastname = htmlspecialchars($_POST["lastname"]);

$street = htmlspecialchars($_POST["street"]);

$username = htmlspecialchars($_POST["username"]);



$sql = $pdo->prepare("UPDATE customer SET surname = ?,lastname = ?,street = ? WHERE username = ?");

$sql->execute(array($surname, $lastname, $street,$username));



$pdo->close();

$sql->close();

All POST variables come from forms that users can(must) fill out, so it is important that it is as safe as possible.

Sorry for this (maybe) beginner question(s), but i'm new in the PDO game, still read a lot but want to see what you people say to that code.

Please tell me what i can optimize, and above all WHY, so i can learn!

---

My Answer:
Your code is a good start in terms of using PDO and prepared statements to update data securely. However, there are a few things you can optimize and improve:

1. Use bindParam() instead of execute() with an array:
Instead of passing an array to the execute() method, you can use bindParam() to bind each parameter individually. This allows you to specify the data type of the parameter, which can help prevent SQL injection attacks.

Here's an example of how you can use bindParam():

php
$sql = $pdo->prepare("UPDATE customer SET surname = :surname, lastname = :lastname, street = :street WHERE username = :username");
$sql->bindParam(':surname', $surname, PDO::PARAM_STR);
$sql->bindParam(':lastname', $lastname, PDO::PARAM_STR);
$sql->bindParam(':street', $street, PDO::PARAM_STR);
$sql->bindParam(':username', $username, PDO::PARAM_STR);
$sql->execute();


2. Remove unnecessary closing statements:
There is no need to call $pdo->close() or $sql->close() in your code. PDO automatically closes the connection when the script finishes executing.

3. Use filter_input() instead of htmlspecialchars():
Instead of using htmlspecialchars() to sanitize user input, you can use filter_input() to retrieve and sanitize input data. This function allows you to specify the type of input you expect (e.g., string, integer) and apply filters to sanitize the data.

Here's an example of how you can use filter_input():

php
$surname = filter_input(INPUT_POST, 'surname', FILTER_SANITIZE_STRING);
$lastname = filter_input(INPUT_POST, 'lastname', FILTER_SANITIZE_STRING);
$street = filter_input(INPUT_POST, 'street', FILTER_SANITIZE_STRING);
$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);


By making these optimizations, you can improve the security and readability of your code. Remember to always validate and sanitize user input to prevent SQL injection and other security vulnerabilities.