Elearning-Task1819S2-SecureCoding

Please complete the following elearning tasks by 13/1/19 1159pm:

1) Complete the 3 coding exercises at https://mimosa-admin.arcadove.host

2) Complete Practical 6

Pract 6 submission to the Elearning Practical Submission Folder

Group 3

SQL

String sql = "select * from sqli_employees where username = ? and password = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
pstmt.setString(1,username);
			
pstmt.setString(2,password);
			
ResultSet rs = pstmt.executeQuery(sql);

---

Medium Regex

String pattern = "[a-zA-Z0-9._]{4,}@ichat.sp.edu.sg"; //continue from here
		 
bool = email.matches(pattern);

---

Advanced Regex

bool= false;
		
String pattern = "^(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*[@$!%*?&])[A-Za-z\\d@$!%*?&]{8,16}$";

bool = password.matches(pattern));

---

Prac 6

//(a)Do validation and output sanitization
String regex = "^[a-zA-Z]+$";		
if (!search.matches(regex)){
	search = StringEscapeUtils.escapeHtml4(search);
	}

//(b)Wrong use of preparedStatement, to fix
String sqlStr = "Select * from inventory where functions like ? order by brand, model";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);			
pstmt.setString(1,search);
ResultSet rs = pstmt.executeQuery();

//(c)validation of id
String id = StringEscapeUtils.escapeHtml4(request.getParameter("hiddenID"));

//(d)Wrong use of preparedStatement, to fix
String sqlStr = "Delete from Inventory WHERE ID = ?";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);
pstmt.setString(1,id);
int rec=pstmt.executeUpdate();

Group 4

MIMOSA

SQLI RECOMPILATION:

String sql = "select * from sqli_employees where username = ? and password = ?";
	PreparedStatement stmt = conn.prepareStatement();
	pstmt.setString(1,username);
	pstmt.setString(2,password);
	ResultSet rs = pstmt.executeQuery(sql);

MEDIUM REGEX:

String pattern = "^[a-zA-Z0-9._]{4,}@ichat.sp.edu.sg$";
	if(email.matches(pattern)){
		bool = true;
	}else{
		bool = false; 
	}

ADVANCED REGEX:

String pattern = "(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*\\W)[a-zA-Z0-9\\S]{8,16}";
	if(password.matches(pattern)){
		bool = true;
	}else{
		bool = false; 
	}

Practical 6

        String search = request.getParameter("searchString");
	boolean firstSearch=false;
	if(search==null){
	search="";
	firstSearch=true;
}
		
Pattern p = Pattern.compile("[^a-z0-9 ]", Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(search);
boolean b = m.find();
		
if (b) {
	search="";
	System.out.println("invalid string");
}
//(a)Do validation and output sanitization

if(!firstSearch){
	out.println("Search results for "+search );
}
try{
	Connection conn=DBConnection.getConnection();
				
//(b)Wrong use of preparedStatement, to fix
String sqlStr = "Select * from inventory where functions "
+ "like ? order by brand, model";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);
pstmt.setString(1, "%" + search + "%");

ResultSet rs = pstmt.executeQuery();

//(d)Wrong use of preparedStatement, to fix
		String sqlStr = "Delete from Inventory WHERE ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sqlStr);
		pstmt.setString(1, id);
		int rec=pstmt.executeUpdate();
		conn.close();

		if (rec>0)
			out.println("<h2>" + rec + " Record deleted!</h2>");
		else
			out.println("<h2>No Record deleted!</h2>");
	} catch (Exception e) {
		out.println(e.getMessage());
	}

Medium Regex

String pattern = "([\\w\\.]{4,}@ichat\\.sp\\.edu\\.sg)"; //continue from here
		bool=email.matches(pattern);

Advanced Regex

EXPLAINATION		
(?=.*[0-9]) a digit must occur at least once
(?=.*[a-z]) a lower case letter must occur at least once
(?=.*[A-Z]) an upper case letter must occur at least once
(?=.*[@#$%^&+=]) a special character must occur at least once
(?=\\S+$) no whitespace allowed in the entire string
.{8,} at least 8 characters

Full ANSWER
String pattern = "^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-])(?!.* ).{8,16}$";
		

Sqli

import java.io.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet("/EmployeeController")
public class EmployeeController extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Connection conn=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String connURL = "jdbc:mysql://localhost/light?user=test&password=test"; //password and user are for showing purpose
			conn = DriverManager.getConnection(connURL);

			String username = request.getParameter("username");
			String password = request.getParameter("password");

			String sql = "select * from sqli_employees where username = ? and password = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery(sql);

			if (rs.next()) {
				request.setAttribute("firstname", rs.getString("firstname"));
				request.setAttribute("lastname", rs.getString("lastname"));
			}

			RequestDispatcher rd = request.getRequestDispatcher("employees.jsp");
			rd.forward(request, response);
		} catch (Exception e) {
			response.sendRedirect("error.jsp");
		}finally{
			
			try{				
				conn.close();
			}catch(Exception e){
				e.printStackTrace();

			}
			
		}
	}

}

Practical 6

//(a)Do validation and output sanitization

		String regex = "[a-zA-Z]+";
		
		if(search != null && search.matches(regex)){
			String searchClean = StringEscapeUtils.escapeHtml4(search);
		}
		
	
			//(b)Wrong use of preparedStatement, to fix
			String sqlStr = "Select * from inventory where functions like ? order by brand, model";

			PreparedStatement pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1,"%"+search+"%");
			ResultSet rs = pstmt.executeQuery();

	//(c)validation of id
		
		String regex = "[a-zA-Z]+";
		
		if(id != null && id.matches(regex)){
			String idClean = StringEscapeUtils.escapeHtml4(id);
		}
		
		//(d)Wrong use of preparedStatement, to fix
		
		String sqlStr = "Delete from Inventory WHERE ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sqlStr);
		pstmt.setString(1,StringEscapeUtils.escapeHtml4(id));
		int rec=pstmt.executeUpdate();
		conn.close();

Ken Tong, Augustus, Keith, How Chong, Jun Hong

MIMOSA
sql:
String sql = "select * from sqli_employees where username = ? and password = ?"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); ResultSet rs = stmt.executeQuery();

Medium Regax
([a-zA-Z._0-9]{4,})+@(ichat)+.(sp)+.(edu)+.(sg)

Advance Regax
^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-])(?!.* ).{8,16}$

Prac 6 (a)

try{
			Validate.matchesPattern(search, "^[a-zA-Z]+$");
		}
		catch(IllegalArgumentException e){
			search = "";
			firstSearch = true;	
		}

Prac 6 (b)

//(b)Wrong use of preparedStatement, to fix
			String sqlStr = "Select * from inventory where functions like ? order by brand, model";

			PreparedStatement pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1,'%' + search + '%');
			ResultSet rs = pstmt.executeQuery();

Prac 6 (c)
if(!id.matches("^[0-9]*$")){ response.sendRedirect("login.jsp"); }
Prac 6 (d)

//(d)Wrong use of preparedStatement, to fix
		String sqlStr = "Delete from Inventory WHERE ID= ?";
		PreparedStatement pstmt = conn.prepareStatement(sqlStr);
		pstmt.setString(1, realid);
		
		int rec=pstmt.executeUpdate();
		conn.close();

Ken, Eileen, Amiran

Mimosa:

Medium Regex:

`import java.io.*;

import javax.servlet.;
import javax.servlet.annotation.;
import javax.servlet.http.;
import java.util.regex.;

@WebServlet("/MediumRegex")
public class MediumRegex extends HttpServlet {

public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

	String email = request.getParameter("email");
	boolean bool;
	                               
	String pattern = "\\w{4,}.+" + //4 Words
	"\\D(ichat.sp.edu.sg)";//Looks for the entire string of characteers
	
	
	bool = Pattern.matches(pattern,email);
	String output = String.valueOf(bool);
	
	
	request.setAttribute("output", output);

}

}`

Advanced Regex

`import java.io.*;

import javax.servlet.;
import javax.servlet.annotation.;
import javax.servlet.http.*;

@WebServlet("/AdvancedRegex")
public class AdvancedRegex extends HttpServlet {

public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

	String password = request.getParameter("password");
	boolean bool;
	
	//Fill in your codes here...
	                
	String pattern = "^(?=.*[A-Z])" //Uppercase 
	+ "(?=.*[a-z])"+ // Lowercase 
	"(?=.*\\d)"+//One digit
	"(?=.[#@$!%?&])"+ //Checks
	"[A-Za-z\\d#@$!%*?&]{8,16}$"; //Looks for captial letter, lenght and whitespace 
	bool = password.matches(pattern);
	String output = String.valueOf(bool);
	
	request.setAttribute("output", output);

}

}

`

Practical 6

//(a)Do validation and output sanitization

		String p = "/[A-Za-z]/g";
		
		firstSearch = Pattern.matches(search, p);
		
		search = search.replaceAll("[<>()\"\";/]", "");
		
		System.out.println(search);

//(b)Wrong use of preparedStatement, to fix
```
String sqlStr = "Select * from inventory where functions like ? order by brand, model";

		PreparedStatement pstmt = conn.prepareStatement(sqlStr);
		pstmt.setString(1, "%" + search + "%");

		ResultSet rs = pstmt.executeQuery();

//(c)validation of id
		```
String sqlStr1 = "SELECT * FROM inventory WHERE id = ?";
		PreparedStatement pstmt1 = conn.prepareStatement(sqlStr1);
		
		pstmt1.setString(1, id);
		ResultSet rs = pstmt1.executeQuery();
		if (!rs.next()) {
			out.println("<h2>Invalid ID!!</h2>");
		}

//(d)Wrong use of preparedStatement, to fix
```
String sqlStr2 = "delete from inventory where id = ?";
PreparedStatement pstmt2 = conn.prepareStatement(sqlStr2);

	pstmt2.setString(1, id);
	
	int rec=pstmt2.executeUpdate();
	conn.close();

Tian Le, Keane, Ryan, Afzal, Jerrod

PreparedStatement for mimosa

String sql = "select * from sqli_employees where username = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
ResultSet rs = pstmt.executeQuery();

Medium regex mimosa

String email = request.getParameter("email");
boolean bool;
String pattern = "^([A-Za-z0-9._]{4,})@ichat.sp.edu.sg$"; //continue from here
bool = email.matches(pattern);
String output = String.valueOf(bool);
request.setAttribute("output", output);

Hard regex mimosa

String password = request.getParameter("password");
boolean bool;
String pattern="(^(?=.[0-9])(?=.[a-z])(?=.[A-Z])(?=.[a-zA-Z])(?=.[!@#$%^&])(?!.*\s).{8,16}$)";
bool = password.matches(pattern);
String output = String.valueOf(bool);
request.setAttribute("output", output);

Practical 6 e-learning

Part A
//(a)Do validation and output sanitization
else{
//only allow letters
String p = "[a-zA-Z]+";
if(search.matches(p)){
String searchClean = StringEscapeUtils.escapeHtml4(search);
}
else{

			search="";
			out.println("Invalid Search Query");
		}
	}

Part B
//(b)Wrong use of preparedStatement, to fix
String sqlStr = "Select * from inventory where functions like ? order by brand, model";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);
pstmt.setString(1,"%"+search+"%");
ResultSet rs = pstmt.executeQuery();

Part C
//(c)validation of id
String p = "[0-9]*";
if(id.matches(p)){

Part D
//(d)Wrong use of preparedStatement, to fix
String sqlStr = "Delete from Inventory WHERE ID= ?";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);
pstmt.setString(1,id);

		int rec=pstmt.executeUpdate();
		conn.close();