MySQL – PHP Prepared Statements – Eli the Computer Guy

Prepared statements help prevent SQL Injection attacks. Essentially they send a template for your SQL Statement, and then inserts the variables separately. This prevents hackers from escaping out of the original SQL statement and being able to run their own.

Prepared statements are tools to be used with other security measures to protect your MySQL server. There are reasons not to use Prepared Statements and you should do your own research before deploying a production web application.

Escaping and SQL injection
Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.
Such a separation sometimes considered as the only security feature to prevent SQL injection, but the same degree of security can be achieved with non-prepared statements, if all the values are formatted correctly. It should be noted that correct formatting is not the same as escaping and involves more logic than simple escaping. Thus, prepared statements are simply a more convenient and less error-prone approach to this element of database security.
https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

statementForm.html

<html>
<body>

<form action="phpPreparedStatementInsert.php" method="post">

Name: <input type="text" name="name"><br>
Age: <input type="text" name="age"><br>
Gender: <select name="gender">
	<option value=" "> </option>
	<option value="boy">Boy</option>
	<option value="girl">Girl</option>
	</select><br>
<input type ="submit">

</body>
</html>

phpPreparedStatementInsert.php

Bind Parameter data types can be:

i – Interger
d – Double (Number with decimal point)
s – String
b – Blob

<?php

$name = $_POST['name'];
$age = $_POST['age'];
$gender = $_POST['gender'];

$servername = "localhost";
$username = "bob";
$password = "123456";
$db = "classDB";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
	die("Connection failed: ". $conn->connect_error);
}

$statement = $conn->prepare("insert into students(name,age,gender) values(?,?,?)");

$statement->bind_param("sis",$name,$age,$gender);

$statement->execute();

echo "ADDED: ".$name.", ".$age.", ".$gender."<br>";

$statement->close();
$conn->close();

?>