-
-
Save jipengxiang/f9044d4f19bb87bfe2d757205f1ed8af to your computer and use it in GitHub Desktop.
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 2 Submission (Joseph, Aloysus, Peter, Joash)
SQLi Recompilation:
String sql = "Select firstname, lastname from sqli_employees where username=? and password=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
request.setAttribute("firstname", rs.getString("firstname"));
request.setAttribute("lastname", rs.getString("lastname"));
}
Mimosa Medium Regex:
String regex = "[a-zA-Z0-9._]{4,}@ichat.sp.edu.sg";
if(email.matches(regex)){
bool = true;
}else{
bool = false;
}
Mimosa Advanced Regex:
String regex = "(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*\\W)[a-zA-Z0-9\\S]{8,16}";
if(password.matches(regex)){
bool = true;
}else{
bool = false;
}
Practical 6a:
String regex = "^[a-zA-Z]+$";
if(search != null && search.matches(regex)){
//Santization Here
search = StringEscapeUtils.escapeHtml4(search);
}
Practical 6b:
Connection conn=DBConnection.getConnection();
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();
Practical 6c:
String regex = "^[0-9]+$";
if(!id.matches(regex)){
out.println("Error! ID is not a number");
return;
}
Practical 6D:
String sqlStr = "Delete from Inventory WHERE ID= ? ";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);
pstmt.setString(1,id);
int rec=pstmt.executeUpdate();
if (rec>0)
out.println("<h2>" + rec + " Record deleted!</h2>");
else
out.println("<h2>No Record deleted!</h2>");
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();
Group 1
Medium Regex:
[a-zA-Z0-9_.]{4,}@ichat.sp.edu.sg
Advanced Regex
^(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*\\p{Punct})[a-zA-Z\\d\\p{Punct}]{8,16}$
SQLi Recompilation:
Practical 6a:
Practical 6b: