CVE-2015-10020: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability has been found in ssn2013 cis450Project and classified as critical. This vulnerability affects the function addUser of the file HeatMapServer/src/com/datformers/servlet/AddAppUser.java. The manipulation leads to sql injection. The name of the patch is 39b495011437a105c7670e17e071f99195b4922e. It is recommended to apply a patch to fix this issue. The identifier of this vulnerability is VDB-218380.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-01-14T21:15Z
Repository: https://github.com/ssn2013/cis450Project
Language: Java
Files changed: HeatMapServer/src/com/datformers/database/OracleDBWrapper.java, HeatMapServer/src/com/datformers/servlet/AddAppUser.java, HeatMapServer/src/com/datformers/servlet/HeatMapServlet.java, HeatMapServer/src/com/datformers/servlet/LoginServlet.java
Commit date: 2015-04-29 18:16:24 -0400
Commit message: sql injection changes

Diff: diff --git a/HeatMapServer/src/com/datformers/database/OracleDBWrapper.java b/HeatMapServer/src/com/datformers/database/OracleDBWrapper.java
index 5b3ce23..a91d5fe 100644
--- a/HeatMapServer/src/com/datformers/database/OracleDBWrapper.java
+++ b/HeatMapServer/src/com/datformers/database/OracleDBWrapper.java
@@ -1,8 +1,9 @@
 package com.datformers.database;
 
 import java.sql.*;
+import java.util.ArrayList;
 import java.util.Enumeration;
 
 
 public class OracleDBWrapper {
 	String url;
@@ -44,10 +45,30 @@ public class OracleDBWrapper {
 			e.printStackTrace();
 		}
 		
 			return rs;
 	}
+	public ResultSet executeValidateQuery(String query,ArrayList<String> params) {
+		PreparedStatement  preparedStatement;
+
+		 ResultSet rs = null;
+		 
+		try {
+			preparedStatement=conn.prepareStatement(query) ;
+			for(int i=0;i<params.size();i++) {
+				preparedStatement.setString(i+1,params.get(i));	
+			}
+			
+			rs = preparedStatement.executeQuery();
+			
+		} catch (Exception e) {
+			// TODO Auto-generated catch block
+			e.printStackTrace();
+		}
+		
+			return rs;
+	}
 	public void closeConnection() {
 		try {
 			conn.close();
 		} catch (SQLException e) {
 			// TODO Auto-generated catch block
diff --git a/HeatMapServer/src/com/datformers/servlet/AddAppUser.java b/HeatMapServer/src/com/datformers/servlet/AddAppUser.java
index f61db5c..d6a4b78 100644
--- a/HeatMapServer/src/com/datformers/servlet/AddAppUser.java
+++ b/HeatMapServer/src/com/datformers/servlet/AddAppUser.java
@@ -4,30 +4,33 @@ package com.datformers.servlet;
 
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.util.ArrayList;
 
 import com.datformers.database.OracleDBWrapper;
 import com.datformers.utils.DatabaseUtil;
 
 
 public class AddAppUser {
 	String query = "";
+	ArrayList<String> params=null;
 	OracleDBWrapper wrapper;
 
-	public AddAppUser(String str){
+	public AddAppUser(String str,ArrayList<String> p){
 		query = str;
+		params=p;
 		wrapper = new OracleDBWrapper(DatabaseUtil.getURL(DatabaseUtil.IP), DatabaseUtil.UERNAME, DatabaseUtil.PASSWORD);
 	}
 	public ResultSet addUser(){
 		
 	
 		//String query1 = "Insert into APPUSER(USER_ID,EMAIL,PASSWORD,FIRST_NAME,LAST_NAME,IS_FACEBOOK_LOGIN)"
 		//		+ " values (usr_id.NEXTVAL,'aryaa@seas.upenn.edu','test','ARyaa','Gautam','Y')";
 		
-		ResultSet rs = wrapper.executeQuery(query);
+		ResultSet rs = wrapper.executeValidateQuery(query, params);
 		return rs;
 		
 		
 	}
 	public void closeDb(){
diff --git a/HeatMapServer/src/com/datformers/servlet/HeatMapServlet.java b/HeatMapServer/src/com/datformers/servlet/HeatMapServlet.java
index 957872e..0e90338 100644
--- a/HeatMapServer/src/com/datformers/servlet/HeatMapServlet.java
+++ b/HeatMapServer/src/com/datformers/servlet/HeatMapServlet.java
@@ -34,11 +34,11 @@ public class HeatMapServlet extends HttpServlet {
 					+ "inner join checkin c "
 					+ "on c.bid = business.bid "
 					+ "where business.city='" + city 
 					+ "' and categories.category='"+category +"'"
 					+"order by c.check_in_info DESC";
-			System.out.println("QString: "+queryString);
+			
 			ResultSet set = dbWrapper.executeQuery(queryString);
 			if(set==null) {
 				return;
 			}
 			
diff --git a/HeatMapServer/src/com/datformers/servlet/LoginServlet.java b/HeatMapServer/src/com/datformers/servlet/LoginServlet.java
index 926d32d..11a536d 100644
--- a/HeatMapServer/src/com/datformers/servlet/LoginServlet.java
+++ b/HeatMapServer/src/com/datformers/servlet/LoginServlet.java
@@ -3,10 +3,11 @@ package com.datformers.servlet;
 import java.io.PrintWriter;
 import java.io.UnsupportedEncodingException;
 import java.security.MessageDigest;
 import java.security.NoSuchAlgorithmException;
 import java.sql.ResultSet;
+import java.util.ArrayList;
 import java.util.Formatter;
 
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
@@ -39,26 +40,28 @@ public class LoginServlet extends HttpServlet {
 				String username = request.getParameter("username");
 				String password = request.getParameter("password");
 				String isValidPwd = "";
 				response.setContentType("text/html");
 				PrintWriter out = response.getWriter();
-				System.out.println("user:pwd" + username + password);
+				
 				if (username.isEmpty() || password.isEmpty()) {
 					out.println("<html><head><body><p>A mandatory field is empty!<p></body></head>");
 					System.out.println("EMPTY");
 					return;
 				}
 				AddAppUser tmp = null;
-				String query = "Select password from APPUSER where email LIKE '"
-						+ username + "'";
-				System.out.println(query);
-				tmp = new AddAppUser(query);
+				String query = "Select password from APPUSER where email=?";
+//						+ username + "'";
+				
+				ArrayList<String> params=new ArrayList<String>();
+				params.add(username);
+				tmp = new AddAppUser(query,params);
 				ResultSet res = tmp.addUser();
 				
 				if (res.next()) {
 					isValidPwd = res.getString("password");
-					System.out.println(isValidPwd);
+					
 				}
 				
 				password=encryptPassword(password);
 				
 				if (password.equals(isValidPwd)) {
@@ -99,40 +102,44 @@ public class LoginServlet extends HttpServlet {
 					out = response.getWriter();
 					out.println("<html><head><body><p>A mandatory field is empty!<p></body></head>");
 					// System.out.println("<html><head><body><p>A mandatory field is empty!<p></body></head>");
 
 				} else {
-					String query = "SELECT count(*) as cnt FROM APPUSER WHERE email LIKE '"
-							+ email + "'";
+					String query = "SELECT count(*) as cnt FROM APPUSER WHERE email=?";
+//							+ email + "'";
 					// System.out.println("Signup query" + query);
-					AddAppUser add = new AddAppUser(query);
+					ArrayList<String> params=new ArrayList<String>();
+					params.add(email);
+					AddAppUser add = new AddAppUser(query,params);
 					ResultSet res = add.addUser();
 					int count = 0;
 					if (res.next()) {
 						count = res.getInt("cnt");
 					}
 					
 					add.closeDb();
-					System.out.println("Signup query" + query);
+					
 					if (count > 0) {
 						// System.out.println("Already Registered");
 						out.println("<html><head><body><p>This email id is already registered!!</p></body></head>");
 					} else {
 						// System.out.println("Registering!");
+						params=new ArrayList<String>();
+						params.add(email);
 						String query1 = "Insert into APPUSER(USER_ID,EMAIL,PASSWORD,FIRST_NAME,LAST_NAME,IS_FACEBOOK_LOGIN)"
-								+ " values (usr_id.NEXTVAL,'"
-								+ email
-								+ "','"
+								+ " values (usr_id.NEXTVAL,?"
+//								+ email
+								+ ",'"
 								+ encrypt
 								+ "','"
 								+ first
 								+ "','"
 								+ last
 								+ "','"
 								+ isFbLogin + "')";
 						// System.out.println(query1);
-						AddAppUser tmp = new AddAppUser(query1);
+						AddAppUser tmp = new AddAppUser(query1,params);
 						tmp.addUser();
 						tmp.closeDb();
 						response.sendRedirect("main.html");
 						//out.println("<html><head><body><h3>Registration Successful!</h3></body></head>");
 					}

Vulnerable code: 	public AddAppUser(String str){
		ResultSet rs = wrapper.executeQuery(query);
			System.out.println("QString: "+queryString);
				System.out.println("user:pwd" + username + password);
				String query = "Select password from APPUSER where email LIKE '"
						+ username + "'";
				System.out.println(query);
				tmp = new AddAppUser(query);
					System.out.println(isValidPwd);
					String query = "SELECT count(*) as cnt FROM APPUSER WHERE email LIKE '"
							+ email + "'";
					AddAppUser add = new AddAppUser(query);
					System.out.println("Signup query" + query);
								+ " values (usr_id.NEXTVAL,'"
								+ email
								+ "','"
						AddAppUser tmp = new AddAppUser(query1);
Fixed code: import java.util.ArrayList;
	public ResultSet executeValidateQuery(String query,ArrayList<String> params) {
		PreparedStatement  preparedStatement;

		 ResultSet rs = null;
		 
		try {
			preparedStatement=conn.prepareStatement(query) ;
			for(int i=0;i<params.size();i++) {
				preparedStatement.setString(i+1,params.get(i));	
			}
			
			rs = preparedStatement.executeQuery();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
			return rs;
	}
import java.util.ArrayList;
	ArrayList<String> params=null;
	public AddAppUser(String str,ArrayList<String> p){
		params=p;
		ResultSet rs = wrapper.executeValidateQuery(query, params);
			
import java.util.ArrayList;
				
				String query = "Select password from APPUSER where email=?";
//						+ username + "'";
				
				ArrayList<String> params=new ArrayList<String>();
				params.add(username);
				tmp = new AddAppUser(query,params);
					
					String query = "SELECT count(*) as cnt FROM APPUSER WHERE email=?";
//							+ email + "'";
					ArrayList<String> params=new ArrayList<String>();
					params.add(email);
					AddAppUser add = new AddAppUser(query,params);
					
						params=new ArrayList<String>();
						params.add(email);
								+ " values (usr_id.NEXTVAL,?"
//								+ email
								+ ",'"
						AddAppUser tmp = new AddAppUser(query1,params);