Austin Austin - 4 months ago 6
HTML Question

Why is this prepared query completely erroring out?

I have recently converted from mysqli statements to PDO prepared statements and some issues have risen. Everything works fine until you get to the submission of the forum which fires the !empty if statement. Please keep in mind, I am making the efforts to prevent SQL injection. If you see any issues, please also adress those.

This is the connection

<?php

$dbhost = 'localhost';
$dbuser = 'hidden';
$dbpass = 'hidden';
$db = 'hidden';

$connect = new PDO("mysqli:host={$dbhost};dbname={$db}", $dbuser, $dbpass);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

?>


This is the page that errors out

<?php

session_start();

if(isset($_SESSION['user_id'])){
header("Location: index.php");
}

function _e($string){
return htmlentities($string, ENT_QUOTES, 'UTF-8');
}

include '../includes/connection.php';
$userName = _e($_POST['userName']);
$Pass = _e($_POST['password']);



if(!empty($userName) && !empty($Pass)){

$stmt = $connect->prepare('SELECT * FROM Admins WHERE Username= :userName');
$stmt = $stmt->execute(array(':userName' => $userName));

while($row = $stmt->fetch(PDO::FETCH_OBJ)){
$dbPass[] = $row['Password'];
}

$hash = password_verify($Pass, $dbPass);

if ($hash == 0){
die("There was no password found matching what you have entered.");
}else{
$records = "SELECT * FROM Admins WHERE Username='$userName' AND Password='$dbPass' AND AdminLevel >=1";
$results = mysqli_query($connect,$records);
if ($results->num_rows == 1){
$row = $results->fetch_assoc();
$_SESSION['user_id'] = $row['ID'];
$_SESSION['admin_level'] = $row['AdminLevel'];
$_SESSION['user_name'] = $row['Username'];
$easyName = $_SESSION['user_name'];
$recordsS = "UPDATE `Admins` SET Status='1' WHERE Username='$userName'";
$resultsS = mysqli_query($connect,$recordsS);
header("Location: index.php");
}else{
$message = "Either you have entered the incorrect login information, or you account has not been approved yet.";
echo "<script type='text/javascript'>alert('$message');</script>";
}
}
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>ServerSide Moderation Services</title>
<link rel="stylesheet" href="../styles/mainStyles.css" type="text/css" />
<link rel="stylesheet" href="../styles/loginFormStyle.css" type="text/css" />
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body class="body">
<?php include '../includes/header.php'; ?>

<div class="mainContent">
<div class="logRegArea">
<article class="leftContent">
<header>
<h2 class="loginArea" style="text-align:center">Login Below:</h2>
</header>
<content>
<div id="login">
<form action="../pages/login.php" method="POST">
<input type="text" placeholder="Enter Your Username" name="userName">
<input type="password" placeholder="Enter Your Password" name="password">
<input type="submit">
</form>
</div>
</content>
</article>
</div>
</div>
<footer class="mainFooter">
<p>This website was developed by ROBLOX user: <a href="https://www.roblox.com/users/8869935/profile" title="Made by: wattleman">wattleman</a></p>
</footer>
</body>
</html>

Answer

To answer your two questions directly:

1) If your message contains data from any source other than your code, sanitize it. Trust NOTHING.

2) Kind of. I can't see any output of your data anywhere, though.

There are two things you need to consider here.

1) By applying htmlspecialchars to the user-supplied data before insertion to the database, you have fixed most of the issues of XSS. However, it is generally better to insert the data "raw", and apply htmlspecialchars at the point when you echo it out to the page. That's because converting html entities makes sense for generating HTML, but nothing else. If you need to run searches, create exports etc., you'll probably find yourself needing to convert these entities back again.

2) You are open to SQL injection attacks. htmlspecialchars offers zero protection against that. You need to be using mysqli_real_escape_string or parametrised queries. More info about that here.