I have a simple form to add job title in an sql db using php. When I'm adding title with single quote in the db it this replace by '
The column in the db is a nchar(250).
Here's my code:
<?php
include('SQLFunction.php');
?>
<html>
<head>
<title>ITGen Title Creator</title>
<meta http-equiv=content-type content="text/html; charset=utf-8">
<link rel="icon" href="img/symbol.png">
<link rel="stylesheet" type="text/css" href="css/style.css"/>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css"
integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<div class="container-fluid">
<nav class="navbar sticky-top menu border">
<a class="navbar-brand" href="index.php">Home</a>
</nav>
<img class="imageLogo" src="img/coveo-logo.png"/>
<h1 class="center">Create a new title</h1>
<br/><br/>
<form action="addTitle.php" method="POST" class="myForm">
<div class="container">
<div class="form-group row">
<label class="col-sm-1 col-form-label center">Title: </label>
<div class="col-sm-6 center">
<input class="form-control" type="text" name="Title" value="* | *" maxlength='250' required>
</div>
<div class="col-sm-5 center">
<button type="submit" name="addButton" class="btn btn-add">Add</button>
</div>
</div>
</div>
</form>
<br>
<hr align="center" size="5" width="90%" noshade>
<br>
<h1 class="center">All title</h1>
<?php
$sql = "SELECT *
from dbo.TITRE
order by name asc";
//echo '<br>Sql :' .$sql.'<br>We will comment this out after testing<br>';
$link = connectMSDB2();
$getResult = $link->prepare($sql);
$getResult->execute();
$result = $getResult->fetchAll(PDO::FETCH_BOTH);
echo "<div >";
echo "<table class=\"table table-hover tableTest\" style='width:85%; margin-left:auto; margin-right:auto;'>";
echo "<thead>";
echo "<tr>";
echo "<th>Action</th>";
echo "<th>ID</th>";
echo "<th>Title</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
foreach ($result as $row) {
$id = $row['id'];
echo "<tr>";
echo "<td><a href=\"#edit$id\" data-toggle=\"modal\"><button type=\"button\" class=\"btn btn-update\" data-toggle=\"modal\">Update</button></a>
<a href=\"#delete$id\" data-toggle=\"modal\"><button type=\"button\" class=\"btn btn-delete\" data-toggle=\"modal\">Delete</button></a></td>";
echo "<td>{$id}</td>";
echo "<td>{$row['name']}</td>";
echo "</tr>";
echo "<div class=\"modal fade\" id=\"edit$id\" tabindex=\"-1\" role=\"dialog\" aria-labelledby=\"exampleModalLabel\"
aria-hidden=\"true\">
<div class=\"modal-dialog modal-lg\" role=\"document\">
<div class=\"modal-content\">
<div class=\"modal-header\">
<h5 class=\"modal-title\">Update title {$row['id']}</h5>
<button type=\"button\" class=\"close\" data-dismiss=\"modal\" aria-label=\"Close\">
<span aria-hidden=\"true\">×</span>
</button>
</div>
<div class=\"modal-body\">
<form action=\"UpdateTitle.php\" method=\"POST\">
<div class=\"form-group row\">
<input type=\"hidden\" name=\"update_id\" value='{$row['id']}'>
<label class=\"col-sm-2 col-form-label\">Title: </label>
<div class=\"col-sm-10\">
<input class=\"form-control\" type=\"text\" value='{$row['name']}' name=\"Title\" maxlength='250' required>
</div>
</div>
</div>
<div class=\"modal-footer\">
<button type=\"button\" class=\"btn btn-delete\" data-dismiss=\"modal\">Close</button>
<button type=\"submit\" class=\"btn btn-update\">Update title</button>
</div>
</form>
</div>
</div>
</div>";
echo "<div class=\"modal fade\" id=\"delete$id\" role=\"dialog\">
<div class=\"modal-dialog\">
<form action=\"deleteTitle.php\" method=\"POST\">
<!-- Modal content-->
<div class=\"modal-content\">
<div class=\"modal-header\">
<h5 class=\"modal-title\">Delete task {$row['id']}</h5>
<button type=\"button\" class=\"close\" data-dismiss=\"modal\" aria-label=\"Close\">
<span aria-hidden=\"true\">×</span>
</button>
</div>
<div class=\"modal-body\">
<input type=\"hidden\" name=\"delete_id\" value='{$row['id']}'/>
<p>
Are you sure you want to delete: <br>
{$row['name']}
</div>
<div class=\"modal-footer\">
<button type=\"submit\" name=\"delete\" class=\"btn btn-delete\">Delete</button>
<button type=\"button\" class=\"btn btn-default\" data-dismiss=\"modal\">NO</button>
</div>
</div>
</form>
</div>
</div>
</div>";
}
echo "</tbody>";
echo "</table>";
echo "</div>";
$link = null;
?>
<footer class="page-footer font-small blue">
<div class="footer-copyright text-right py-3">
powered by: <img style="width:15%" src="img/machine_learning_icon.png">
</div>
</footer>
</div>
<script src="js/bootstrap.bundle.js"></script>
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"
integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49"
crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"
integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy"
crossorigin="anonymous"></script>
</body>
</html>
Here's the code to add the title in the db:
<?php
require_once('SQLFunction.php');
$title = filter_var($_POST['Title'], FILTER_SANITIZE_STRING);
try {
$link = connectMSDB2();
$sql = "INSERT INTO dbo.TITRE(name)
VALUES (:title)";
$stmt = $link->prepare($sql);
$stmt->bindParam(':title', $title);
if($stmt->execute()){
$message = 'New Title added';
} else {
echo "<br>Error :" . $sql . "<br>" . $link->errorInfo();
}
} catch (Exception $e) {
$message = 'Unable to process request';
var_dump($e);
}
$link = null;
header("Location: indexTitle.php");
?>
What can I do to arrange this?
I've try the htmlspecialchars_decode but it doesn't work.
Thanks for your help
