CVE-2014-125047 (Java, published 2023-01-06T12:15Z)
Description: value: A vulnerability classified as critical has been found in tbezman school-store. This affects an unknown part. The manipulation leads to sql injection. The identifier of the patch is 2957fc97054216d3a393f1775efd01ae2b072001. It is recommended to apply a patch to fix this issue. The identifier VDB-217557 was 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/tbezman/school-store
Files changed:
src/com/bezman/background/DailySubmission.java
src/com/bezman/servlet/Attendance.java
src/com/bezman/servlet/DailyServlet.java
src/com/bezman/servlet/IndexServlet.java
src/com/bezman/servlet/ItemRecapServlet.java
src/com/bezman/servlet/ItemSettings.java
src/com/bezman/servlet/Login.java
src/com/bezman/servlet/MonthlyRecapServlet.java
src/com/bezman/servlet/StudentSettingsServlet.java
src/com/bezman/servlet/SubmitDaily.java
Commit message: SQL Injection

Changed all of the SQL Queries to be safe from SQL Injeection


Vulnerable code:
        ResultSet salesSet = IndexServlet.execQuery("select * from sales where date='" + date + "'");
import javax.servlet.http.Cookie;
            ResultSet resultSet = IndexServlet.execQuery("select * from daily where MONTH(date)='" + month + "' and DAY(date)='" + day + "' and YEAR(date)='" + year + "' order by period");
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                System.out.println("Username : " + username);
                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
import javax.servlet.http.Cookie;
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;
                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                System.out.println("Username : " + username);
                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }

                ResultSet itemSet = IndexServlet.execQuery("select * from items");
                JSONArray jsonArray = new JSONArray();
                while(itemSet.next()){
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("itemName", itemSet.getString("name"));
                    jsonObject.put("priceOfItem", itemSet.getDouble("price"));
                    jsonArray.add(jsonObject);
                }

                model.addAttribute("itemNames", StringEscapeUtils.escapeJavaScript(jsonArray.toJSONString()));
            } catch (SQLException e) {
                e.printStackTrace();
            String query = period == null ? "select * from students" : "select * from students where period='" + period + "' ORDER BY period ASC";
            ResultSet resultSet = IndexServlet.execQuery(query);
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + sessionID + "'");
            ResultSet accountsSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");
import javax.servlet.http.Cookie;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.stream.Collectors;
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (month != null)
            params.add("MONTH(date)='" + month + "' ");
        if (day != null)
            params.add("DAY(date)='" + day + "' ");
        if (year != null)
            params.add("YEAR(date)='" + year + "' ");
        if (month == null && day == null && year == null)
            query = "select * from sales";
        else query += params.stream().collect(Collectors.joining(" and "));
        if (order == null)
            order = "";
            ResultSet resultSet = IndexServlet.execQuery(query);
import javax.servlet.http.Cookie;
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                System.out.println("Username : " + username);
                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            ResultSet resultSet = IndexServlet.execQuery("select * from accounts where username='" + username + "' and password='" + password + "'");
                IndexServlet.execUpdate("delete from sessions where username='" + username + "'");
                IndexServlet.execUpdate("insert into sessions values('" + username + "', '" + sessionID + "')");
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                System.out.println("Username : " + username);
                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(month != null)
            params.add("MONTH(date)='" + month + "' ");
        if(day != null)
            params.add("DAY(date)='" + day + "' ");
        if(year != null)
           params.add("YEAR(date)='" + year +"' ");
        System.out.println(query);
        ResultSet resultSet = null;
        try {
            resultSet = IndexServlet.execQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            IndexServlet.execUpdate("delete from daily where date='" + date + "'");
            IndexServlet.execUpdate("delete from sales where date='" + date + "'");
import javax.servlet.http.Cookie;
import java.sql.ResultSet;
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                ResultSet resultSet = IndexServlet.execQuery("select * from sessions where sessionID='" + cookie.getValue() + "'");
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                System.out.println("Username : " + username);
                ResultSet accountSet = IndexServlet.execQuery("select * from accounts where username='" + username + "'");

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            IndexServlet.execUpdate("delete from students where name='" + name + "'");
            IndexServlet.execUpdate("insert into students values('" + StringEscapeUtils.escapeHtml(name) + "', '" + StringEscapeUtils.escapeHtml(period) + "')");
            IndexServlet.execUpdate("update students set name='" + StringEscapeUtils.escapeHtml(newName) + "',period='" + StringEscapeUtils.escapeHtml(newPeriod) + "' where name='" + oldName + "' and period='" + oldPeriod + "'");
import java.util.Calendar;
import java.util.Map;
import java.util.TimeZone;
        String query = "insert into daily (date, ";

        String columnsNames = (String) allParams.keySet().stream().collect(Collectors.joining(", "));
        query += columnsNames;
        query += ") values('" + timestamp + "', " + (String) allParams.keySet().stream().map(a -> allParams.get(a)).collect(Collectors.joining("', '", "'", "'")) + ")";
            IndexServlet.execUpdate(query);
                query = "insert into sales values('" + timestamp + "', '" + currentSale + "')";
                System.out.println(query);
                IndexServlet.execUpdate(query);

Fixed code:
import java.sql.PreparedStatement;
        PreparedStatement statement = IndexServlet.connection.prepareStatement("select * from sales where date=?");
        statement.setTimestamp(1, date);

        ResultSet salesSet = statement.executeQuery();

import java.sql.PreparedStatement;
            PreparedStatement statement = IndexServlet.connection.prepareStatement("select * from daily where MONTH(date)=? and DAY(date)=? and YEAR(date)=? order by period");
            statement.setString(1, month);
            statement.setString(2, day);
            statement.setString(3, year);

            ResultSet resultSet = statement.executeQuery();
        IndexServlet.servletLoginCheck(model, request);
import java.sql.PreparedStatement;
        IndexServlet.servletLoginCheck(model, request);
        try {
            ResultSet itemSet = IndexServlet.execQuery("select * from items");
            JSONArray jsonArray = new JSONArray();
            while (itemSet.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("itemName", itemSet.getString("name"));
                jsonObject.put("priceOfItem", itemSet.getDouble("price"));
                jsonArray.add(jsonObject);

            model.addAttribute("itemNames", StringEscapeUtils.escapeJavaScript(jsonArray.toJSONString()));
        }catch (SQLException e){
            e.printStackTrace();
            PreparedStatement statement;

            if (period == null){
                statement = IndexServlet.connection.prepareStatement("SELECT * FROM students");
            }else{
                statement = IndexServlet.connection.prepareStatement("SELECT * FROM students WHERE period=? ORDER BY period ASC");
                statement.setString(1, period);
            }

            ResultSet resultSet = statement.executeQuery();
        IndexServlet.servletLoginCheck(model, request);
            PreparedStatement sessionStatement = IndexServlet.connection.prepareStatement("SELECT * from sessions WHERE sessionID=?");
            sessionStatement.setString(1, sessionID);

            ResultSet resultSet = sessionStatement.executeQuery();
            PreparedStatement roleStatement = IndexServlet.connection.prepareStatement("SELECT  * from accounts where username=?");
            roleStatement.setString(1, username);

            ResultSet accountsSet = roleStatement.executeQuery();

    public static void servletLoginCheck(Model model, HttpServletRequest request){
        Cookie cookie = IndexServlet.getCookie(request.getCookies(), "sessionID");
        if (cookie != null){
            try {
                PreparedStatement statement = IndexServlet.connection.prepareStatement("SELECT  * from sessions where sessionID=?");
                statement.setString(1, cookie.getValue());

                ResultSet resultSet = statement.executeQuery();
                String username = null;

                while(resultSet.next()){
                    model.addAttribute("username", resultSet.getString("username"));
                    username = resultSet.getString("username");
                }

                PreparedStatement roleStatement = IndexServlet.connection.prepareStatement("SELECT * from accounts where username=?");
                roleStatement.setString(1, username);

                ResultSet accountSet = roleStatement.executeQuery();

                while(accountSet.next()){
                    model.addAttribute("role", accountSet.getString("role"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
import java.sql.PreparedStatement;
import java.util.*;
        IndexServlet.servletLoginCheck(model, request);
        HashMap<Integer, String> valueMap = new HashMap<>();
        Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("America/New_York"));
        int predCount = 1;
        if (month != null) {
            query += "MONTH(date)=?";

            valueMap.put(predCount, month);

            predCount++;
        }

        if (day != null) {

            if (predCount > 1)
                query += " and ";

            query += "DAY(date)=?";

            valueMap.put(predCount, day);
            predCount++;
        }

        if (year != null) {

            if (predCount > 1)
                query += " and ";

            query += "YEAR(date)=?";

            valueMap.put(predCount, year);

            predCount++;
        }

        if (month == null && day == null && year == null)
            query = "SELECT * from sales";

            PreparedStatement statement = IndexServlet.connection.prepareStatement(query);

            for(Integer integer : valueMap.keySet()){
                statement.setString(integer, valueMap.get(integer));
            }

            System.out.println(statement.toString());


            ResultSet resultSet = statement.executeQuery();
        IndexServlet.servletLoginCheck(model, request);
import java.sql.PreparedStatement;
            PreparedStatement statement = IndexServlet.connection.prepareStatement("select * from accounts where username=? and password=?");
            statement.setString(1, username);
            statement.setString(2, password);

            ResultSet resultSet = statement.executeQuery();
                PreparedStatement deleteSessions = IndexServlet.connection.prepareStatement("DELETE from sessions WHERE username=?");
                deleteSessions.setString(1, username);

                deleteSessions.executeUpdate();

                PreparedStatement insertSession = IndexServlet.connection.prepareStatement("insert into sessions VALUES(?, ?)");
                insertSession.setString(1, username);
                insertSession.setString(2, sessionID);

                insertSession.executeUpdate();
import java.sql.PreparedStatement;
import java.util.HashMap;
        IndexServlet.servletLoginCheck(model, request);
        HashMap<Integer, String> valueMap = new HashMap<>();

        int predCount = 1;

        if (month != null) {
            query += "MONTH(date)=?";

            valueMap.put(predCount, month);

            predCount++;
        }

        if (day != null) {

            if (predCount > 1)
                query += " and ";

            query += "DAY(date)=?";

            valueMap.put(predCount, day);

            predCount++;
        }

        if (year != null) {

            if (predCount > 1)
                query += " and ";
            query += "YEAR(date)=?";
            valueMap.put(predCount, year);

            predCount++;
        }
        try {
            PreparedStatement statement = IndexServlet.connection.prepareStatement(query);
            for(Integer integer : valueMap.keySet()){
                statement.setString(integer, valueMap.get(integer));
            }

            System.out.println(statement);

            ResultSet resultSet = statement.executeQuery();
            PreparedStatement deleteDaily = IndexServlet.connection.prepareStatement("DELETE from daily where date=?");
            deleteDaily.setString(1, date);

            deleteDaily.executeUpdate();

            PreparedStatement deleteSales = IndexServlet.connection.prepareStatement("DELETE from sales where date=?");
            deleteSales.setString(1, date);

            deleteSales.executeUpdate();
import java.sql.PreparedStatement;
        IndexServlet.servletLoginCheck(model, request);
            PreparedStatement statement = IndexServlet.connection.prepareStatement("DELETE  from students where name=?");
            statement.setString(1, name);

            statement.executeUpdate();
            PreparedStatement statement = IndexServlet.connection.prepareStatement("insert into students VALUES(?, ?)");
            statement.setString(1, StringEscapeUtils.escapeHtml(name));
            statement.setString(2, StringEscapeUtils.escapeHtml(period));

            statement.executeUpdate();
            PreparedStatement statement = IndexServlet.connection.prepareStatement("update students set name=?, period=? where name=? and period=?");
            statement.setString(1, newName);
            statement.setString(2, newPeriod);
            statement.setString(3, oldName);
            statement.setString(4, oldPeriod);

            statement.executeUpdate();
import java.sql.PreparedStatement;
import java.util.*;
import java.util.stream.IntStream;
        HashMap<Integer, String> keyMap = new HashMap<>();
        HashMap<Integer, String> valueMap = new HashMap<>();

        int i = 1;
        for(Object obj : allParams.keySet()){
            keyMap.put(i, (String) obj);
            valueMap.put(i, (String) allParams.get(obj));
            i++;
        }

        String columnNames = (String) keyMap.values().stream().collect(Collectors.joining(", "));

        ArrayList questionMarks = new ArrayList();
        IntStream.range(0, allParams.size()).forEach(a -> questionMarks.add("?"));

        String query = "insert into daily (date, " + columnNames + ") VALUES('" + timestamp + "', " + questionMarks.stream().collect(Collectors.joining(", ")) + ")";
            PreparedStatement statement = IndexServlet.connection.prepareStatement(query);

            for(Integer integer: valueMap.keySet()){
                statement.setString(integer, valueMap.get(integer));
            }

            System.out.println(statement);

            statement.executeUpdate();
                query = "insert into sales values(?, ?)";

                PreparedStatement salesStatement = IndexServlet.connection.prepareStatement(query);
                salesStatement.setTimestamp(1, timestamp);
                salesStatement.setString(2, currentSale);

                salesStatement.executeUpdate();