I'm using mysqli_real_escape_string to stop sql injections but I read that this is not full proof and am trying to add more security for this insert into database code. I'm fairly new to php but need to use it for this particular project. I have been reading about other methods but am not sure of the right way to insert these methods or if I should use another sanitizing feature. Below is my code any suggestions will help me in my attempt to provide the best security for this program.
<?php
$servername = "localhost";
$username = "hidden";
$password = "hidden";
$db = "hidden";
//Connection using MySQLi Object oriented
$conn = new mysqli($servername, $username, $password, $db);
//If connection fails give error
if ($conn->connect_error){
die("Connection failed: ". $conn->connect_error);
}
//Defined variable for success message
$sucess = "";
//Check if form submit button submitted
if(isset($_POST['submit'])){
//Set-up variable to be inserted in table and add escape string using mysqli function for security purposes
$name = $conn->real_escape_string($_POST['name']);
$issue = $conn->real_escape_string($_POST['issue']);
$solution = $conn->real_escape_string($_POST['solution']);
$os = $conn->real_escape_string($_POST['os']);
$category = $conn->real_escape_string($_POST['category']);
// Define three database tables based on the os chosen
if($os == "Windows"){
$table = "windows";
}elseif($os == "Linux"){
$table = "linux";
}elseif($os == "macOS"){
$table = "macos";
}
// Define category options to be chosen
if($category == "Backup"){
$category = "Backup";
}elseif($category == "Database"){
$category = "Database";
}elseif($category == "Hardware"){
$category = "Hardware";
}elseif($category == "Internet"){
$category = "Internet";
}elseif($category == "Networking"){
$category = "Networking";
}elseif($category == "Security"){
$category = "Security";
}elseif($category == "Servers"){
$category = "Servers";
}elseif($category == "Software"){
$category = "Software";
}
//Inserts into database based on chosen os, name, issue, category in respective table
$sql = "insert into $table (name,issue,solution,os,category) values('$name','$issue','$solution','$os','$category')";
//Check if record inserted then assign success msg to variable and print.
if ($conn->query($sql) === TRUE) {
//echo "ADDED: $name .", ". $issue .", ". $solution .", ". $os .", ". $category";
$sucess = "Solution successfully saved!!";
} else {
//Insert failure error
echo "Error: ".$sql."<br>".$conn->error;
}
//close connection
$conn->close();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert Solutions Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</head>
<body>
<h1>Insert Solutions Database</h1>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="POST">
<table class="table table-bordered">
<thead>
<tr>
<th>Issue Name :</th>
<td><input type="text" name="name" value="" size="50" /></td>
</tr>
</thead>
<tbody>
<tr>
<th>Issue :</th>
<td><input type="text" name="issue" value="" size="50" /></td>
</tr>
<tr>
<th>Solution :</th>
<td><input type="text" name="solution" value="" size="50" /></td>
</tr>
<tr>
<th>Operating System :</th>
<td>
<select name="os">
<option value="">Please select os</option>
<option value="Windows">Window</option>
<option value="Linux">Linux</option>
<option value="macOS">MacOS</option>
</select>
</td>
</tr>
<tr>
<th>Category :</th>
<td>
<select name="category">
<option value="">Please select category</option>
<option value="Backup">Backup</option>
<option value="Database">Database</option>
<option value="Hardware">Hardware</option>
<option value="Internet">Internet</option>
<option value="Networking">Networking</option>
<option value="Security">Security</option>
<option value="Servers">Servers</option>
<option value="Software">Software</option>
</td>
</tr>
</tbody>
</table>
<br>
<!-- <input type="reset" value="Clear" name="clear" /> -->
<a href="connect.php" class="btn btn-info mb-2" style="margin-top: 22px">Clear</a>
<input type="submit" class="btn btn-info mb-2" value="Submit" name="submit" style="margin-top: 22px">
</form>
<!-- If record inserted successfully then it shoud print success message -->
<?php if(isset($sucess)){
echo "<h1>$sucess</h1>";
} ?>
</body>
</html>
