CVE-2014-125074: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in Nayshlok Voyager. It has been declared as critical. Affected by this vulnerability is an unknown functionality of the file Voyager/src/models/DatabaseAccess.java. The manipulation leads to sql injection. The identifier of the patch is f1249f438cd8c39e7ef2f6c8f2ab76b239a02fae. It is recommended to apply a patch to fix this issue. The identifier VDB-218005 was assigned to this vulnerability.
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-11T15:15Z
Repository: https://github.com/Nayshlok/Voyager
Language: Java
Files changed: Voyager/WebContent/WEB-INF/account/login.jsp, Voyager/WebContent/WEB-INF/register.jsp, Voyager/src/Controllers/RegisterController.java, Voyager/src/models/DatabaseAccess.java
Commit date: 2014-11-17 15:30:35 -0700
Commit message: fixed problems in register controller, and worked at preventing sql-injection in database access

Diff: diff --git a/Voyager/WebContent/WEB-INF/account/login.jsp b/Voyager/WebContent/WEB-INF/account/login.jsp
index 0138975..c9bf09d 100644
--- a/Voyager/WebContent/WEB-INF/account/login.jsp
+++ b/Voyager/WebContent/WEB-INF/account/login.jsp
@@ -24,17 +24,17 @@
 		</nav>
 	</header>
 
 	<article class="bodyContainer">
 		<h1>Register</h1>
-		<p><%= (request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
 		<form method="get" action="<%=request.getContextPath()%>/register">
 			<input type="submit" value="Register" id="submit" />
 		</form>
 		<br /> <br />
 		<hr />
 		<h1>Login</h1>
+		<p><%= (request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
 		<form method="post">
 			<label>Username:</label> <input name="username" /> <label>Password:</label>
 			<input name="password" type="password" /> <input type="submit"
 				value="Login" id="submit" />
 		</form>
diff --git a/Voyager/WebContent/WEB-INF/register.jsp b/Voyager/WebContent/WEB-INF/register.jsp
index 535411f..3598c02 100644
--- a/Voyager/WebContent/WEB-INF/register.jsp
+++ b/Voyager/WebContent/WEB-INF/register.jsp
@@ -23,11 +23,11 @@
 			</ul>
 		</nav>
 	</header>
 	<article class="bodyContainer">
 		<h1>Register New User</h1>
-		<p><%=(((RegisterUserModel)request.getAttribute("errorMessage")).getErrorMessage() == null ? "" : ((RegisterUserModel)request.getAttribute("errorMessage")).getErrorMessage()) %></p>
+		<p><%=(request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
 		<form method="post" enctype="multipart/form-data">
 			<label>Username:</label> <input name="username" type="text" value="<%= current.getUsername() %>"/>
 			<label>Password:</label><input	name="password" type="password" /> 
 			<label>Confirm Password:</label><input name="confirmPassword" type="password" />
 			<label>Email Address:</label><input name="email" type="email" value="<%=current.getEmail() %>"/>
diff --git a/Voyager/src/Controllers/RegisterController.java b/Voyager/src/Controllers/RegisterController.java
index 6ec664a..93bed2a 100644
--- a/Voyager/src/Controllers/RegisterController.java
+++ b/Voyager/src/Controllers/RegisterController.java
@@ -53,29 +53,26 @@ public class RegisterController {
 		
 		RegisterUserModel model = new RegisterUserModel();
 		ModelAndView mv = null;
 		
 		if(!password.equals(confirmPassword)) {
-			model.setErrorMessage("Bad username/password. ");
 			request.setAttribute("attemptedAccount", new Account(username, email, avatarPath, Roles.User, password));
-			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
+			mv = new ModelAndView("Passwords did not match", "/WEB-INF/register.jsp");
 		}
 		if(!email.equals(confirmEmail)){
-			model.setErrorMessage(model.getErrorMessage() + "Emails did not match. ");
 			request.setAttribute("attemptedAccount", new Account(username, email, avatarPath, Roles.User, password));
-			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
+			mv = new ModelAndView("Emails did not match. ", "/WEB-INF/register.jsp");
 		}
 		try {
 			Account user = new Account(username, email, avatarPath, Roles.User, password);
 			dataService.registerUser(user);
 			FileUploadController.processRequest(request, response, filePath);
 			model.setUser(user);
 			mv = new ModelAndView(model, "/WEB-INF/account/profile.jsp");
 		} catch(UsernameAlreadyExistsException e) {
 			request.setAttribute("attemptedAccount", new Account(username, email, avatarPath, Roles.User, password));
-			model.setErrorMessage("Username has already been used.");
-			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
+			mv = new ModelAndView("Username has already been used.", "/WEB-INF/register.jsp");
 		} catch (ServletException e) {
 			e.printStackTrace();
 		} catch (IOException e) {
 			e.printStackTrace();
 		}
diff --git a/Voyager/src/models/DatabaseAccess.java b/Voyager/src/models/DatabaseAccess.java
index 9bb24e1..5185feb 100644
--- a/Voyager/src/models/DatabaseAccess.java
+++ b/Voyager/src/models/DatabaseAccess.java
@@ -13,21 +13,22 @@ import exceptions.BadLoginException;
 import exceptions.UsernameAlreadyExistsException;
 
 
 public class DatabaseAccess implements DataService {
 
+	private final String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 	/* (non-Javadoc)
 	 * @see models.DataService#login(java.lang.String, java.lang.String)
 	 */
 	@Override
 	public Account login(String username, String password){
 		Account account = null;
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
-			PreparedStatement statement = con.prepareStatement("Select userName, userPassword, userEmail, userRole from UserTable where userName = '" + username + "'");
+			PreparedStatement statement = con.prepareStatement("Select userName, userPassword, userEmail, userRole from UserTable where userName = ?");
+			statement.setString(1, username);
 			ResultSet rs = statement.executeQuery();
 			rs.next();
 			String storedPass = rs.getString("userPassword");
 			if(storedPass.equals(password)){
 				System.out.println("Successfully logged in");
@@ -53,15 +54,18 @@ public class DatabaseAccess implements DataService {
 	 * @see models.DataService#registerUser(models.Account)
 	 */
 	@Override
 	public void registerUser(Account user){
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
 			PreparedStatement statement = con.prepareStatement("Insert INTO UserTable (userName, userPassword, userEmail, userRole) "
-					+ "VALUES ('" + user.getUsername() + "', '" + user.getPassword() + "', '" + user.getEmail() + "', '" + user.getRole().toString() + "');");
+					+ "VALUES (?, ?, ?, ?);");
+			statement.setString(1, user.getUsername());
+			statement.setString(2, user.getPassword());
+			statement.setString(3, user.getEmail());
+			statement.setString(4, user.getRole().toString());
 			statement.execute();
 			System.out.println("Registration Successful");
 		} catch (SQLException e) {
 			if(e.getMessage().contains("UNIQUE KEY")){
 				System.err.println("User has already been registered.");
@@ -77,14 +81,14 @@ public class DatabaseAccess implements DataService {
 	 * @see models.DataService#removeUser(models.Account)
 	 */
 	@Override
 	public void removeUser(Account user){
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
-			PreparedStatement statement = con.prepareStatement("DELETE FROM UserTable WHERE userName='" + user.getUsername() + "'");
+			PreparedStatement statement = con.prepareStatement("DELETE FROM UserTable WHERE userName=?");
+			statement.setString(1, user.getUsername());
 			statement.execute();
 			System.out.println("Removal sucessful");
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}	
@@ -94,35 +98,37 @@ public class DatabaseAccess implements DataService {
 	 * @see models.DataService#updateUser(models.Account)
 	 */
 	@Override
 	public void updateUser(Account user){
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
 			PreparedStatement statement = con.prepareStatement("UPDATE UserTable "
-					+ "SET userPassword='" + user.getPassword() + "', userEmail='" + user.getEmail() + "', userRole='" + user.getRole().toString() + "'"
-					+ "WHERE userName='" + user.getUsername() + "'");
+					+ "SET userPassword=?, userEmail=?, userRole=?"
+					+ "WHERE userName=?");
+			statement.setString(1, user.getPassword());
+			statement.setString(2, user.getEmail());
+			statement.setString(3, user.getRole().toString());
+			statement.setString(4, user.getUsername());
 			statement.execute();
 			System.out.println("Update successful");
 		} catch (SQLException e) {
 			e.printStackTrace();
-		}	
+		}
 	}
 	
 	/* (non-Javadoc)
 	 * @see models.DataService#getUserId(java.lang.String)
 	 */
 	@Override
 	public int getUserId(String user){
-		Account account = null;
 		int id = -1;
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
-			PreparedStatement statement = con.prepareStatement("Select userId from UserTable where userName = '" + user + "'");
+			PreparedStatement statement = con.prepareStatement("Select userId from UserTable where userName = ?");
+			statement.setString(1, user);
 			ResultSet rs = statement.executeQuery();
 			rs.next();
 			String storedId = rs.getString("userId");
 			id = Integer.parseInt(storedId);
 		} catch (SQLException e) {
@@ -136,14 +142,14 @@ public class DatabaseAccess implements DataService {
 	 */
 	@Override
 	public String getUserName(int userId){
 		String userName = null;
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
-			PreparedStatement statement = con.prepareStatement("Select userName from UserTable where userId = '" + userId + "'");
+			PreparedStatement statement = con.prepareStatement("Select userName from UserTable where userId = ?");
+			statement.setInt(1, userId);
 			ResultSet rs = statement.executeQuery();
 			rs.next();
 			userName = rs.getString("userName");
 			
 		} catch (SQLException e) {
@@ -157,30 +163,31 @@ public class DatabaseAccess implements DataService {
 	 * @see models.DataService#enterPost(models.Post)
 	 */
 	@Override
 	public void enterPost(Post post){
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
 			PreparedStatement statement = con.prepareStatement("Insert INTO PostTable (postTitle, postAuthorId, postTime, postContent) "
 					+ "VALUES ('" + post.getTitle() + "', '" + this.getUserId(post.getAuthor()) + "', CURRENT_TIMESTAMP, '" + post.getMessage() + "');");
+			statement.setString(1, post.getTitle());
+			statement.setInt(2, this.getUserId(post.getAuthor()));
+			statement.setString(3, post.getMessage());
 			statement.execute();
 			System.out.println("Successful post");
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}	
 	}
-	
+
 	/* (non-Javadoc)
 	 * @see models.DataService#retrievePost(java.lang.String)
 	 */
 	@Override
 	public Post retrievePost(String postTitle){
 		Post post = null;
 		Driver driver = new SQLServerDriver();
-		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
 		try {
 			Connection con = driver.connect(connectionUrl, new Properties());
 			PreparedStatement statement = con.prepareStatement("Select postTitle, postAuthorId, postTime, postContent from PostTable where postTitle = '" + postTitle + "'");
 			ResultSet rs = statement.executeQuery();
 			rs.next();

Vulnerable code: 		<p><%= (request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
		<p><%=(((RegisterUserModel)request.getAttribute("errorMessage")).getErrorMessage() == null ? "" : ((RegisterUserModel)request.getAttribute("errorMessage")).getErrorMessage()) %></p>
			model.setErrorMessage("Bad username/password. ");
			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
			model.setErrorMessage(model.getErrorMessage() + "Emails did not match. ");
			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
			model.setErrorMessage("Username has already been used.");
			mv = new ModelAndView(model, "/WEB-INF/register.jsp");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
			PreparedStatement statement = con.prepareStatement("Select userName, userPassword, userEmail, userRole from UserTable where userName = '" + username + "'");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
					+ "VALUES ('" + user.getUsername() + "', '" + user.getPassword() + "', '" + user.getEmail() + "', '" + user.getRole().toString() + "');");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
			PreparedStatement statement = con.prepareStatement("DELETE FROM UserTable WHERE userName='" + user.getUsername() + "'");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
					+ "SET userPassword='" + user.getPassword() + "', userEmail='" + user.getEmail() + "', userRole='" + user.getRole().toString() + "'"
					+ "WHERE userName='" + user.getUsername() + "'");
		}	
		Account account = null;
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
			PreparedStatement statement = con.prepareStatement("Select userId from UserTable where userName = '" + user + "'");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
			PreparedStatement statement = con.prepareStatement("Select userName from UserTable where userId = '" + userId + "'");
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
	
		String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
Fixed code: 		<p><%= (request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
		<p><%=(request.getAttribute("errorMessage") == null ? "" : request.getAttribute("errorMessage")) %></p>
			mv = new ModelAndView("Passwords did not match", "/WEB-INF/register.jsp");
			mv = new ModelAndView("Emails did not match. ", "/WEB-INF/register.jsp");
			mv = new ModelAndView("Username has already been used.", "/WEB-INF/register.jsp");
	private final String connectionUrl = "jdbc:sqlserver://n8bu1j6855.database.windows.net:1433;database=VoyagerDB;user=VoyageLogin@n8bu1j6855;password={GroupP@ssword};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
			PreparedStatement statement = con.prepareStatement("Select userName, userPassword, userEmail, userRole from UserTable where userName = ?");
			statement.setString(1, username);
					+ "VALUES (?, ?, ?, ?);");
			statement.setString(1, user.getUsername());
			statement.setString(2, user.getPassword());
			statement.setString(3, user.getEmail());
			statement.setString(4, user.getRole().toString());
			PreparedStatement statement = con.prepareStatement("DELETE FROM UserTable WHERE userName=?");
			statement.setString(1, user.getUsername());
					+ "SET userPassword=?, userEmail=?, userRole=?"
					+ "WHERE userName=?");
			statement.setString(1, user.getPassword());
			statement.setString(2, user.getEmail());
			statement.setString(3, user.getRole().toString());
			statement.setString(4, user.getUsername());
		}
			PreparedStatement statement = con.prepareStatement("Select userId from UserTable where userName = ?");
			statement.setString(1, user);
			PreparedStatement statement = con.prepareStatement("Select userName from UserTable where userId = ?");
			statement.setInt(1, userId);
			statement.setString(1, post.getTitle());
			statement.setInt(2, this.getUserId(post.getAuthor()));
			statement.setString(3, post.getMessage());