Week 7 Discussions – SQL Injection
The instuctions are long for this assignment/discussion board. They are below and attached as a word document called Week 7 Discussions – SQL Injection which is the directions for my discussion and for two other students I need to respond to that you may download.
Both of the students I chose are hyperlinked in the word document (Week 7 Discussions – SQL Injection) for me to reference for myself who I am going to be replying to which you cannot access.
NOTE: Student A has an image file and sql file provided so are also attached but student B did not provide one so you will just see the code in directions. Also, provide screen shots to prove when a script is volunerable for the one you make and how to hack it and the one that it migrated so you cannot do so. Same as providing screen shots for the 2 students who I will reply to as prof along with anything else I need.
Directions for what to do and what the students did below and attached:
SQL Injection is in the top 10 OWASP and Common Weakness Enumeration. Using MySQL and PHP, show your own very short and simple application that is vulnerable to this attack.
Provide another version that mitigates this issue. (Again keep this simple)
Screen shot(s) would be helpful!
Students should respond with specific tests (e.g. data input) that shows how they could break into your database application with your first example but were unsuccessful for your mitigated example.
Screen shot(s) would be helpful!
I have to respond to 2 other students and be sure to cover how you were able to break in to their first version but not the mitigated one. Here are the two I chose.
Student A:
SQL Injection Discussion – JGrimard (Reminder: Screen shot(s) would be helpful!)
I have created an insecure login page which is vulnerable to SQL injection. I then created another login web application which uses prepared statements to prevent SQL injection attacks. I tried to keep it as simple as possible, but posting data then accessing a database is not all that simple to begin with.
Just an FYI, ZAP doesn’t automatically find the SQL injection vulnerability, however if you use the login name, ie admin, along with some injection parameters you can easily bypass the password.
The SQL and index.php are the same for both secure and insecure versions. The processLogin.php file is the only one that is different.
Please let me know if you need me to explain any part of my code or need any tips on ‘breaking into’ my database. Here is a hint: this is the line of code that makes my web app vulnerable to sql injection:
$sql = “SELECT * FROM WebUsers WHERE UserID = ‘$userName’ AND Password = ‘$password'”;
-- This is the same for both secure and insecure
-- Week7Discussion.sql
-- June 27, 2016
-- Jason Grimard
-- UMUC SDEV300
-- -
-- Create a table of users and passwords for Week 7 Discussion post
-- Use the sdev database
USE sdev;
-- Delete the table if it already exists
DROP TABLE IF EXISTS WebUsers;
-- Create table - WebUsers
CREATE TABLE IF NOT EXISTS WebUsers (
UserID VARCHAR(30) PRIMARY KEY,
Password VARCHAR(100),
FirstName VARCHAR(30),
LastName VARCHAR(30)
);
-- Insert WebUser into table
INSERT INTO WebUsers
VALUES ('admin','SuPeR_StRoNg_PaSsWoRd_jkh234','Jason','Grimard');
INSERT INTO WebUsers
VALUES ('bfranklin','SuPeR_StRoNg_PaSsWoRd_jasd3234dsa','Ben','Franklin');
<?php
//File: index.php This is the same for both secure and insecure
//Author: Jason Grimard
//Course: UMUC SDEV 300
//Project: Week 7 Discussion Post
//Due Date: 07/03/2016
//Description: A form that takes login information from the user_error
//and passes it to processLogin.php. This could be an HTML file however
//LEO alters HTML files, so a PHP file was used.
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Week 7 Discussion</title>
</head>
<body>
<div align="center">
<h3>SDEV300<br>
Week 7 Discussion<br>
Jason Grimard
</h3>
<br>
<br>
<h4> Please enter login information then click login </h4>
<form action="processLogin.php" method="POST">
<table border="0">
<tbody>
<tr>
<td>UserName</td>
<td><input type="text" name="userName"/></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password"/></td>
</tr>
<td colspan="2" align="center">
<input type="submit" value="Login" />
</td>
</tr>
</tbody>
</table>
</form>
</div>
</body>
</html>
<?php
//NOT SECURE VERSION
//File: processLogin.php
//Author: Jason Grimard
//Course: UMUC SDEV 300
//Project: Week 7 Discission Post
//Due Date: 07/03/2016
//Description: Page that receives POSTed data from the form page,
//access MySQL database, and logs user in.
//
//Create connection to database
$SQLservername = "localhost";
$SQLusername = "sdev_owner";
$SQLpassword = "sdev300";
$SQLdatabase = "sdev";
$conn = mysqli_connect($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase);
// Check connection
if (!$conn)
{
die("MySQL Connection failed: " . mysqli_connect_error());
}
//Define variables
$fName = ""; //first name
$lName = ""; //last name
$userName = ""; //user name
$password = ""; //password
//Retrieve POST data
if (!empty($_POST["userName"]))
{
$userName = $_POST["userName"];
}
if (!empty($_POST["password"]))
{
$password = $_POST["password"];
}
//Check login against database
$sql = "SELECT * FROM WebUsers WHERE UserID = '$userName' AND Password = '$password'";
//Perform the query and store the results
$result = mysqli_query($conn, $sql);
//If the query failed kill the page and display error
if ($result === false)
{
die("SQL Error");
}
//close connection since we are done with it.
mysqli_close($conn);
//Create an associative array using the results row
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$count = mysqli_num_rows($result);
//If count == 1 then the username and password match the database and 1 row has been returned
//The user is logged in
if ($count == 1)
{
$fName = $row['FirstName'];
$lName = $row['LastName'];
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Week 7 Discussion</title>
</head>
<body>
<div align="center">
<h3>Welcome <?php echo "$fName $lName!"; ?></h3><br>
You are Logged in as <?php echo $userName ?><br><br>
This page shows super secret confidential information...
</h3>
</div>
</body>
</html>
<?php
} else
{
// Show the login form again.
include('index.php');
echo "Sorry, the username and password did not match. Try Again.";
}
?>
<?php
//SECURE VERSION
//File: processLogin.php
//Author: Jason Grimard
//Course: UMUC SDEV 300
//Project: Week 7 Discission Post
//Due Date: 07/03/2016
//Description: Page that receives POSTed data from the login form page,
//access MySQL database, and logs user in.
//
//Create connection to database
$SQLservername = "localhost";
$SQLusername = "sdev_owner";
$SQLpassword = "sdev300";
$SQLdatabase = "sdev";
$conn = new mysqli($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase);
// Check connection
if ($conn->connect_error)
{
die("MySQL Connection failed: " . mysqli_connect_error());
}
//Define variables
$fName = ""; //first name
$lName = ""; //last name
$userName = ""; //user name
$password = ""; //password
//Retrieve POST data
if (!empty($_POST["userName"]))
{
$userName = $_POST["userName"];
}
if (!empty($_POST["password"]))
{
$password = $_POST["password"];
}
//Check login against database
//Prepare statement and bind
if ($stmt = $conn->prepare("SELECT UserID, FirstName, LastName FROM WebUsers WHERE UserID = ? AND Password = ?"))
{
$stmt->bind_param("ss", $userName, $password);
//Execute the query and bind the results
$stmt->execute();
$stmt->bind_result($userName, $fName, $lName);
//store results so we can access number of rows returned
$stmt->store_result();
$numOfRows = $stmt->num_rows;
//if 1 row returned then user name and password were correct, display secret data
if ($numOfRows == 1)
{
$stmt->fetch()
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Week 7 Discussion</title>
</head>
<body>
<div align="center">
<h3>Welcome <?php echo "$fName $lName!"; ?></h3><br>
You are Logged in as <?php echo $userName ?><br><br>
This page shows super secret confidential information...
</h3>
</div>
</body>
</html>
<?php
} else
{
// Show the login form again.
include('index.php');
echo "Sorry, the username and password did not match. Try Again.";
}
$stmt->close();
}//end if stmt
$conn->close();
?>
Attached is the downloadable Zip file and image witch is the URL I suppled way above and the files for this discussion are called: (Please keep the zip file as the same name I guess. Obviously up to you.
· 2016-06-27_13-12-16.png(47.16 KB)
Student B:
Graded Discussion: SQL Injection” href=”https://learn.umuc.edu/d2l/le/144686/discussions/threads/7419788/View”>Graded Discussion: SQL Injection (Reminder: Screen shot(s) would be helpful!)
sql injection.
run this sql to setup the database:
create table users(userName varchar(30));
insert into users (userName) values(‘hello’);
insert into users (userName) values(‘goodbye’);
insert into users (userName) values(‘nowsayhi’);
create sqlinjection.php page with this code:
<?php
if ($_SERVER[“REQUEST_METHOD”] == “POST”){
$userName = $_POST[‘userName’];
$output = “failure: can’t find you”;
// Try to connect
$mysqli = new mysqli(‘localhost’, ‘sdev_owner’,’sdev300′,’sdev’);
if ($mysqli->connect_error) {
die(‘Connect Error (‘ . $mysqli->connect_errno . ‘) ‘
. $mysqli->connect_error);
}
// For Windows MYSQL String is case insensitive
$Myquery = “SELECT ‘true’ as userName from users where userName=”$userName””;
if ($result = $mysqli->query($Myquery))
{
/* Fetch the results of the query */
while( $row = $result->fetch_assoc() )
{
if($row[“userName”] == ‘true’){
$output = “Success: Found you!”;
}
}
/* Destroy the result set and free the memory used for it */
$result->close();
}
$mysqli->close();
}
?>
<html>
<body>
<form action=”sqlinjection.php” method=”post”>
Enter your name and click submit to see if you’re in the database<br>
<input type=”text” name=”userName” id=”userName” value=”<?=$userName?>”><br><br>
<input name=”submit” type=”submit” value=”submit” />
<br><br>
<?php print $output; ?>
</body>
</html>
to eliminate this vulnerability, call this function when setting the $userName var:
function getNameText($tbValue, $maxLen){
$output=””;
$chars = str_split($tbValue);
foreach($chars as $char){
$charNum = ord($char);
if(($charNum >= 65 && $charNum <= 90) || ($charNum >= 97 && $charNum <= 122) || $charNum == 39 ||($charNum >= 44 && $charNum <=46)||$charNum == 32){
if($charNum == 39){ //apostrophe
$char = chr(239);
}
$output .= $char;
}
}
$output = substr($output,0,$maxLen);
return trim($output);
}
This student did not provide the SQL file or any screen shots to attached so the attachments apply to student one only.
PLEASE, place all answers and name of attachments that go with either my discussion, student A’s or Student B’s. All answers can be placed on tne Week 7 Discussions word document that I labed for where to put the answers in it for you and send it back with everthing else.
Doing so as a zip file or not is just fine. Either way works for me!
