I recently learned about SQL Injection and the PHP recommendation to avoid it, using prepare()
and bind_param()
.
Now, I want to prepare SQL queries dynamically, adding both column names and values.
I usted to do it like this, having the name
field of the HTML input with the same name as the MySQL database column.
<input type="text" name="firstname" >
<input type="text" name="lastname" >
And the, create the SQL query dynamically using mysqli.
// Extract values from POST
$parameters = $_POST;
// Organize the values in two strings
foreach ($parameters as $id => $value) {
$fields = $fields . "`" . $id . "`,";
$values = $values . "'" . $value . "',";
/*e.g.
$fields = `firstname`,`lastname`
$values = 'John','Wick'
*/
}
// Write into the database
$sql = "INSERT INTO `user` ($fields) VALUES ($values)";
/*e.g.
INSERT INTO `user` (`firstname`,`lastname`) VALUES ('John','Wick')
*/
I would like to know if there is a way to do this using prepare()
and bind_param()
to avoid SQL injection, may be adding adding some data-type="s"
to the HTML input tag or if there is a better, more best-practices, way to do it.