I have been developing a little, private Blog Site to make notes/ stories of pen and paper RPG games available for my players and me.
This is my first project. As I am new to coding in PHP and MySQL I'd very much appreciate your opinions on the code I have written. I am interested in any feedback you can give me - but especially on the security of the script I have written. The input form is actually secured by a randomly created username and password authentication via .htaccess. (Side note: the website uses proper encryption for the user input including HSTS and ranks as an A+ on ssllabs.com.)
I have been reading a lot lately on script injection and SQL injection (for example on https://phpdelusions.net/pdo/sql_injection_example) and using variables to create a query was often considered unsafe. Still - in my inexperiencedness - I've made a script that is using the (static) keys of the $_POST Array to determine the database columns and placeholders for values which are used in my PDO query. Afterwards I bind each submitted value to its PDO placeholder.
I do not expect to enter any HTML Input, which is why I decided to use the stripslashes and htmlspecialchars funcions on the input values before binding it to the PDO querys and I haven't used any str_replace function to escape backtics since every value is directly bound to the designated PDO placeholder.
if (isset ($_POST['session_submit'])) {
$table = 'sessions';
foreach ($_POST as $column=>$value){
$column = input_behandlung_sessions($column);
$secure_value = input_behandlung_sessions($value);
$formular_daten[$column] = $secure_value;
}
$kampagne = $formular_daten['kampagne'];
$count = $pdo->prepare('SELECT COUNT(kampagne) FROM sessions WHERE kampagne = :kampagne');
$count->bindParam(':kampagne', $kampagne, PDO::PARAM_STR);
$count->execute();
$rowcount = $count->fetchColumn(0);
$newrowcount = ++$rowcount;
unset($formular_daten['session_submit']);
foreach ($formular_daten as $column=>$value){
if ($value == "") unset($formular_daten[$column]);
}
foreach ($formular_daten as $column=>$value){
$non_empty_columns[] = $column;
$columns_param = ':'.$column;
$array_columns_param[] = $columns_param;
$to_bind[$columns_param] = $value;
}
$text_non_empty_columns = implode (", ", $non_empty_columns);
$parameter_columns = implode (', ', $array_columns_param);
$sql_string = 'INSERT INTO '.$table.' (session_uid, '.$text_non_empty_columns.') VALUES ('.$newrowcount.', '.$parameter_columns.')';
$stmt = $pdo->prepare($sql_string);
foreach ($to_bind as $key=>$param){
$stmt->bindValue($key, $param);
}
$stmt->execute();
unset($_POST);
}
function input_behandlung_sessions($data) {
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
And here is the form used to submit the data (in case you are interested).
echo '
<form method="post" action="#v-pills-session">
<div class="row">
<div class="form-group col-md-2 col-sm-6"><label for="formGroupExampleInput">Kampagne: </label></div>
<div class="col-md-4 col-sm-6"><input required="true" type="text" class="form-control" id="formGroupExampleInput" name="kampagne" placeholder="Kürzel der Kampagne eintragen"></div>
<div class="form-group col-md-2 col-sm-6"><label for="formGroupExampleInput">Kapitel-Nr.: </label></div>
<div class="col-md-4 col-sm-6"><input required="true" type="text" class="form-control" id="formGroupExampleInput" name="session_story_arc" placeholder="Nummer des Kapitels eintragen"></div>
</div>
<div class="form-group row">
<div class="col-md-1 col-sm-12"><label for="formGroupExampleInput">Name: </label></div>
<div class="col-md-11 col-sm-12"><input required="true" type="text" class="form-control" id="formGroupExampleInput" name="session_name" placeholder="Hier den Namen der Session eintragen"></div>
</div>
<div class="form-group row">
<div class="col-md-1 col-sm-12"<label for="formGroupExampleInput2">Spieler: </label></div>
<div class="col-md-11 col-sm-12"><input type="text" required="true" class="form-control" id="formGroupExampleInput2" name="session_charaktere_aktiv" placeholder="Hier die Namen der aktiven Charaktere eintragen"></div>
</div>
<div class="form-group row">
<div class="col-md-1 col-sm-12"><label for="formGroupExampleInput2">Quests: </label></div>
<div class="col-md-11 col-sm-12"><input type="text" class="form-control" id="formGroupExampleInput2" name="session_quests" placeholder="Hier die Quests in dieser Session eintragen"></div>
</div>
<div class="form-group row">
<div class="col-md-1 col-sm-12"><label for="formGroupExampleInput2">NPCs</label></div>
<div class="col-md-11 col-sm-12"><input type="text" class="form-control" required="true" id="formGroupExampleInput2" name="session_npc" placeholder="Hier die Namen der NPCs aus dieser Session eintragen"></div>
</div>
<div class="form-group row">
<div class="col-md-1 col-sm-12"><label for="formGroupExampleInput2">Orte: </label></div>
<div class="col-md-11 col-sm-12"><input type="text" class="form-control" required="true" id="formGroupExampleInput2" name="session_orte" placeholder="Hier die Orte eintragen, in welchen die Abenteure unterwegs waren"></div>
</div>
<div class="form-group">
<input required="true" type="submit" name="session_submit" class="form-control btn-primary" id="formGroupExampleInput2" placeholder="Submit">
</div>
</form>';
SHOW COLUMNS FROM $table
to generate the schema from the DB and then filter the post array by that. It gets a bit tricky when dealing with multiple tables though. \$\endgroup\$