CVE-2015-10034 (Java, published 2023-01-09T21:15Z)
Description: value: A vulnerability has been found in j-nowak workout-organizer and classified as critical. This vulnerability affects unknown code. The manipulation leads to sql injection. The patch is identified as 13cd6c3d1210640bfdb39872b2bb3597aa991279. It is recommended to apply a patch to fix this issue. VDB-217714 is the identifier assigned to this vulnerability.
Severity: nan (CVSS v3 base score: 9.8)
Weakness: CWE-89 \u2014 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
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.
Repository: https://github.com/j-nowak/workout-organizer
Files changed:
src/app/controllers/AccountController.java
src/app/controllers/Application.java
src/app/controllers/UsersController.java
src/app/database/ExerciseDao.java
src/app/database/GymsDao.java
src/app/database/UsersDao.java
src/app/database/WorkoutDao.java
Commit message: Fixed building queries - preventing SQL-injection


Vulnerable code:
import java.util.HashMap;
import java.util.Map;
			if (UsersDao.get().checkPasswordForUser(session().get(Application.USER_ID), oldPassword)) {
				UsersDao.get().changePassword(session().get(Application.USER_ID), newPassword);
		String userId = session(Application.USER_ID);
		String weight = requestData.get("weight");
		String height = requestData.get("height");
		String dateOfBirth = requestData.get("dateOfBirth");

		Map<String, String> toUpdate = new HashMap<String, String>();
		if (!weight.equals("")) {
			toUpdate.put("weight", weight);
		}
		if (!height.equals("")) {
			toUpdate.put("height", height);
		}
		if (!dateOfBirth.equals("")) {
			toUpdate.put("date_of_birth", dateOfBirth);
		
		if (toUpdate.size() > 0 && UsersDao.get().update(userId, toUpdate)) {
			return ok();
		}
		else {
    	User user = UsersDao.get().getById(session(USER_ID)); //TODO change session to userId
    	return ok(account.render(user));
		User user = UsersDao.get().getById(foreignerId + "");
			String sql = connection.nativeSQL("INSERT INTO exercise_ratings(user_id, exercise_id, rating) VALUES" + 
					"  (" + userId + ", " + exerciseId + ", " + rating + ");");
			play.Logger.info("Insert exercise_rating: " + sql);
			connection.createStatement().execute(sql);
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT weight, set_count, reps_per_set" + 
					"		FROM workout_entries" + 
					"		JOIN workouts USING (workout_id)" + 
					"		WHERE user_id = " + userId + " AND exercise_id = " + exerciseId + 
					"		ORDER BY weight" + 
					"		LIMIT 1;");
			
			String sql = connection.nativeSQL("INSERT INTO gym_ratings(user_id, gym_id, rating) VALUES" + 
					"  (" + userId + ", " + gymId + ", " + rating + ");");
			play.Logger.info("Insert gym_rating: " + sql);
			connection.createStatement().execute(sql);
import java.sql.Statement;
import java.util.Map;
import java.util.Map.Entry;
			String sql = connection.nativeSQL("INSERT INTO users(login, email, password_digest, first_name, last_name) VALUES" + 
					"  ('" + user.getLogin() + "','" + user.getEmail() + "','" + passwordDigest + "','" + user.getFirstName() + "','" + user.getLastName() + "')");
			
			play.Logger.info("Insert user: " + sql);
			connection.createStatement().execute(sql);
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT * FROM users WHERE login = '" + user.getLogin() +"'");
			
			Statement getuserStatement = connection.createStatement();
			ResultSet resultUser = getuserStatement.executeQuery("SELECT * FROM users "
					+ "WHERE (login = '" + login + "' OR email = '" + login + "') AND password_digest = '" + passwordDigest + "'");
			getuserStatement.close();
	public User getById(String userId) { //TODO change to userId
			Statement getuserStatement = connection.createStatement();
			ResultSet resultUser = getuserStatement.executeQuery("SELECT * FROM users "
					+ "WHERE user_id = '" + userId + "'");
			getuserStatement.close();
	public boolean checkPasswordForUser(String userId, String password) {
			Statement getuserStatement = connection.createStatement();
			ResultSet resultUser = getuserStatement.executeQuery("SELECT * FROM users "
					+ "WHERE user_id = '" + userId + "' AND password_digest = '" + passwordDigest + "'");
			getuserStatement.close();
	public void changePassword(String userId, String password) {
			Statement statement = connection.createStatement();
			String sql = "UPDATE users SET password_digest = '"+ passwordDigest + "' where user_id = '" + userId + "';";
			statement.executeUpdate(sql);
			statement.close();
	public boolean update(String userId, Map<String, String> toUpdate) {
			Statement statement = connection.createStatement();
			String sql = "UPDATE users SET ";
			for (Entry<String, String> entry : toUpdate.entrySet()) {
				sql += entry.getKey() + " = '" + entry.getValue() + "', ";
			}
			sql += " where user_id = '" + userId + "';";
			play.Logger.info(sql);
			statement.executeUpdate(sql);
			statement.close();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT * FROM random_strangers_of_user(" + userId + ")");
			statement.close();
import java.sql.Statement;
		return getWorkouts("SELECT workouts.*, gyms.gym_name "
				+ "FROM workouts "
				+ "LEFT JOIN gyms using (gym_id) "
				+ "ORDER BY workouts.finished_at DESC");
		return getWorkouts("SELECT workouts.*, gyms.gym_name "
				+ "FROM workouts "
				+ "LEFT JOIN gyms using (gym_id) "
				+ "WHERE user_id = " + userId + " "
				+ "ORDER BY workouts.finished_at DESC");
	private List<Workout> getWorkouts(String sql) {
		
		Connection connection = null;
			connection = DB.getConnection();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			
			
			statement.close();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

Fixed code:
import java.sql.Date;
		int userId;
		try {
			userId = Integer.parseInt(session(Application.USER_ID));
		} catch (Exception e) {
			e.printStackTrace();
			return badRequest();
		}
			if (UsersDao.get().checkPasswordForUser(userId, oldPassword)) {
				UsersDao.get().changePassword(userId, newPassword);
		Double weight = null;
		Double height = null;
		Date dateOfBirth = null;
		int userId;
		try {
			userId = Integer.parseInt(session(Application.USER_ID));
		} catch (Exception e) {
			e.printStackTrace();
			return badRequest();
		if (!requestData.get("weight").equals(""))
			weight = Double.valueOf(requestData.get("weight"));
		if (!requestData.get("height").equals(""))
			height = Double.valueOf(requestData.get("height"));
		if (!requestData.get("dateOfBirth").equals(""))
			dateOfBirth = Date.valueOf(requestData.get("dateOfBirth"));

		try {
			if (UsersDao.get().update(userId, weight, height, dateOfBirth))
				return ok();
			else
				return badRequest();
		} catch (Exception e) {
			e.printStackTrace();
    	try {
	    	User user = UsersDao.get().getById(Integer.parseInt(session(USER_ID))); //TODO change session to userId
	    	return ok(account.render(user));
    	} catch (Exception e) {
    		e.printStackTrace();
    		return badRequest();
    	}
		User user = UsersDao.get().getById(foreignerId);
import java.sql.PreparedStatement;
			PreparedStatement p = connection.prepareStatement("INSERT INTO exercise_ratings(user_id, exercise_id, rating) VALUES (?, ?, ?);");
			p.setInt(1, userId);
			p.setInt(2, exerciseId);
			p.setInt(3, rating);
			
			p.executeQuery();
			p.close();
			PreparedStatement p = connection.prepareStatement("SELECT weight, set_count, reps_per_set"
					+ "FROM workout_entries "
					+ "JOIN workouts USING (workout_id) "
					+ "WHERE user_id = ? AND exercise_id = ? "
					+ "ORDER BY weight"
					+ "LIMIT 1;");
			ResultSet resultSet = p.executeQuery();
			resultSet.close();
			p.close();
import java.sql.PreparedStatement;
			PreparedStatement p = connection.prepareStatement("INSERT INTO gym_ratings(user_id, gym_id, rating) VALUES (?, ?, ?)");
			p.setInt(1, userId);
			p.setInt(2, gymId);
			p.setInt(3, rating);
			p.executeQuery();
			p.close();
import java.sql.Date;
			PreparedStatement p = connection.prepareStatement("INSERT INTO users(login, email, password_digest, first_name, last_name) VALUES (?, ?, ?, ?, ?)");
			p.setString(1, user.getLogin());
			p.setString(2, user.getEmail());
			p.setString(3, passwordDigest);
			p.setString(4, user.getFirstName());
			p.setString(5, user.getLastName());
			p.executeQuery();
			p.close();
			p = connection.prepareStatement("SELECT * FROM users WHERE login = ?");
			p.setString(1, user.getLogin());
			ResultSet result = p.executeQuery();
			result.close();
			p.close();
			PreparedStatement p = connection.prepareStatement("SELECT * FROM users WHERE (login = ? OR email = ?) AND password_digest = ?");
			p.setString(1, login);
			p.setString(2, login);
			p.setString(3, passwordDigest);
			ResultSet resultUser = p.executeQuery();
			p.close();
	public User getById(int userId) {
			PreparedStatement p = connection.prepareStatement("SELECT * FROM users WHERE user_id = ?");
			p.setInt(1, userId);
			
			ResultSet resultUser = p.executeQuery();
			p.close();
	public boolean checkPasswordForUser(int userId, String password) {
			PreparedStatement p = connection.prepareStatement("SELECT * FROM users WHERE user_id = ? AND password_digest = ?");
			p.setInt(1, userId);
			p.setString(2, passwordDigest);
			ResultSet resultUser = p.executeQuery();
			p.close();
	public void changePassword(int userId, String password) {
			PreparedStatement p = connection.prepareStatement("UPDATE users SET password_digest = ? where user_id = ?");
			p.setString(1, passwordDigest);;
			p.setInt(2, userId);
			p.executeQuery();
			p.close();
	public boolean update(int userId, Double weight, Double height, Date dateOfBirth) {
			String sql = "UPDATE users SET   ";
			if (weight != null)
				sql += "weight = ?, ";
			if (height != null)
				sql += "height = ?, ";
			if (dateOfBirth != null)
				sql += "date_of_birth = ?, ";
			sql += " WHERE user_id = ?";
			PreparedStatement p = connection.prepareStatement(sql);

			int i = 1;
			if (weight != null)
				p.setDouble(i++, weight);
			if (height != null)
				p.setDouble(i++, height);
			if (dateOfBirth != null)
				p.setDate(i++, dateOfBirth);
			p.setInt(i, userId);

			play.Logger.info(sql);

			if (i > 1)
				p.executeUpdate();
			p.close();
			PreparedStatement p = connection.prepareStatement("SELECT * FROM random_strangers_of_user(?)");
			p.setInt(1, userId);
			ResultSet resultSet = p.executeQuery();
			p.close();
		List<Workout> workouts = new ArrayList<Workout>();
		Connection connection = null;
		try {
			connection = DB.getConnection();
			PreparedStatement p = connection.prepareStatement("SELECT workouts.*, gyms.gym_name "
					+ "FROM workouts "
					+ "LEFT JOIN gyms using (gym_id) "
					+ "ORDER BY workouts.finished_at DESC");
			workouts = buildWorkouts(p.executeQuery());
			p.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return workouts;
		List<Workout> workouts = new ArrayList<Workout>();
		Connection connection = null;
		try {
			connection = DB.getConnection();
			PreparedStatement p = connection.prepareStatement("SELECT workouts.*, gyms.gym_name "
					+ "FROM workouts "
					+ "LEFT JOIN gyms using (gym_id) "
					+ "WHERE user_id = ? "
					+ "ORDER BY workouts.finished_at DESC");
			p.setInt(1, userId);
			workouts = buildWorkouts(p.executeQuery());
			p.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return workouts;
	private List<Workout> buildWorkouts(ResultSet resultSet) {