Code Security Report: 24 high severity findings, 41 total findings

[mend-for-github-com]

Code Security Report

Scan Metadata

Latest Scan: 2023-11-21 12:44pm
Total Findings: 41 | New Findings: 0 | Resolved Findings: 2
Tested Project Files: 135
Detected Programming Languages: 2 (JavaScript / Node.js, Java*)

• Check this box to manually trigger a scan

Most Relevant Findings

The below list presents the 10 most relevant findings that need your attention. To view information on the remaining findings, navigate to the Mend SAST Application.

Automatic Remediation Available (6)

Severity	Vulnerability Type	CWE	File	Data Flows	Date
High	SQL Injection	CWE-89	DBUtil.java:506	2	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 501 to 506 in eb96a35 public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); 2 Data Flow/s detected View Data Flow 1 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 91 in eb96a35 String password1 = request.getParameter("password1"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 103 in eb96a35 String error = DBUtil.changePassword(username, password1); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 502 in eb96a35 public static String changePassword(String username, String password) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 506 in eb96a35 statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 506 in eb96a35 statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); View Data Flow 2 AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 22 in eb96a35 public Response changePassword(String bodyJSON, @Context HttpServletRequest request) throws IOException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 32 in eb96a35 bodyJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 32 in eb96a35 bodyJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 35 in eb96a35 username = bodyJson.get("username").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 35 in eb96a35 username = bodyJson.get("username").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 35 in eb96a35 username = bodyJson.get("username").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 35 in eb96a35 username = bodyJson.get("username").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 56 in eb96a35 error = DBUtil.changePassword(username, password1); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 502 in eb96a35 public static String changePassword(String username, String password) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 506 in eb96a35 statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 506 in eb96a35 statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); ⛑️ Remediation Suggestion AltoroJ/diffs/18500e04-1e34-49e2-a57a-cd06821123bd/DBUtil.java.diff Lines 1 to 541 in 6877891 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); @@ -505,2 +506,4 @@ - Statement statement = connection.createStatement(); - statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); + PreparedStatement statement = connection + .prepareStatement("UPDATE PEOPLE SET PASSWORD = '" + password + "' WHERE USER_ID = " + "?"); + statement.setString(1, username); + statement.execute(); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request AltoroJ/diffs/b5e1b64e-21b8-482d-bff2-623d505c1d58/DBUtil.java.diff Lines 1 to 541 in 2318900 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); @@ -505,2 +506,4 @@ - Statement statement = connection.createStatement(); - statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); + PreparedStatement statement = connection + .prepareStatement("UPDATE PEOPLE SET PASSWORD = " + "?" + " WHERE USER_ID = '" + username + "'"); + statement.setString(1, password); + statement.execute(); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request
High	SQL Injection	CWE-89	DBUtil.java:494	2	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 489 to 494 in eb96a35 public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); 2 Data Flow/s detected View Data Flow 1 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 59 in eb96a35 String username = request.getParameter("username"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 80 in eb96a35 String error = DBUtil.addUser(username, password1, firstname, lastname); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 490 in eb96a35 public static String addUser(String username, String password, String firstname, String lastname) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 494 in eb96a35 statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 494 in eb96a35 statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); View Data Flow 2 AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 67 in eb96a35 public Response addUser(String bodyJSON, @Context HttpServletRequest request) throws IOException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 80 in eb96a35 bodyJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 80 in eb96a35 bodyJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 83 in eb96a35 lastname = bodyJson.get("lastname").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 83 in eb96a35 lastname = bodyJson.get("lastname").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 83 in eb96a35 lastname = bodyJson.get("lastname").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 83 in eb96a35 lastname = bodyJson.get("lastname").toString(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AdminAPI.java Line 103 in eb96a35 error = DBUtil.addUser(username, password1, firstname, lastname); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 490 in eb96a35 public static String addUser(String username, String password, String firstname, String lastname) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 494 in eb96a35 statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 494 in eb96a35 statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); ⛑️ Remediation Suggestion AltoroJ/diffs/d13231ca-d653-4da4-8b9c-618317813671/DBUtil.java.diff Lines 1 to 542 in e14a803 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); @@ -493,2 +494,5 @@ - Statement statement = connection.createStatement(); - statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); + PreparedStatement statement = connection + .prepareStatement("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('" + + username + "','" + password + "', '" + firstname + "', " + "?" + ",'user')"); + statement.setString(1, lastname); + statement.execute(); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request AltoroJ/diffs/19f7e4c2-78e3-4818-964c-123fc8c2cd23/DBUtil.java.diff Lines 1 to 542 in 6d71b31 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); @@ -493,2 +494,5 @@ - Statement statement = connection.createStatement(); - statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); + PreparedStatement statement = connection + .prepareStatement("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES (" + "?" + + ",'" + password + "', '" + firstname + "', '" + lastname + "','user')"); + statement.setString(1, username); + statement.execute(); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request
High	SQL Injection	CWE-89	DBUtil.java:242	2	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 237 to 242 in eb96a35 if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ 2 Data Flow/s detected View Data Flow 1 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 75 in eb96a35 username = request.getParameter("uid"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 94 in eb96a35 Cookie accountCookie = ServletUtil.establishSession(username,session); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 340 in eb96a35 public static Cookie establishSession(String username, HttpSession session){ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 342 in eb96a35 User user = DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 236 in eb96a35 public static User getUserInfo(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 242 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 242 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ View Data Flow 2 AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 140 in eb96a35 StringTokenizer tokenizer = new StringTokenizer(decodedToken,":"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 140 in eb96a35 StringTokenizer tokenizer = new StringTokenizer(decodedToken,":"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 142 in eb96a35 return DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 236 in eb96a35 public static User getUserInfo(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 242 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 242 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ⛑️ Remediation Suggestion AltoroJ/diffs/c777a8b5-5b0f-4a2a-bc95-4d28bdb746ff/DBUtil.java.diff Lines 1 to 541 in 98b5f78 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); @@ -241,2 +242,4 @@ - Statement statement = connection.createStatement(); - ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ + PreparedStatement statement = connection + .prepareStatement("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = " + "?"); + statement.setString(1, username); + ResultSet resultSet =statement.executeQuery(); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request
High	SQL Injection	CWE-89	DBUtil.java:519	1	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 514 to 519 in eb96a35 public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); 1 Data Flow/s detected AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/FeedbackServlet.java Line 49 in eb96a35 String name = request.getParameter("name"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/FeedbackServlet.java Line 57 in eb96a35 String feedbackId = OperationsUtil.sendFeedback(name, email, subject, comments); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 119 in eb96a35 public static String sendFeedback(String name, String email, AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 127 in eb96a35 long id = DBUtil.storeFeedback(name, email, subject, comments); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 515 in eb96a35 public static long storeFeedback(String name, String email, String subject, String comments) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 519 in eb96a35 statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 519 in eb96a35 statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ⛑️ Remediation Suggestion AltoroJ/diffs/2c3bddf1-1afc-4716-81a9-bf89aa79b622/DBUtil.java.diff Lines 1 to 542 in 32e893c --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); @@ -518,2 +519,5 @@ - Statement statement = connection.createStatement(); - statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); + PreparedStatement statement = connection + .prepareStatement("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES (" + "?" + ", '" + + email + "', '" + subject + "', '" + comments + "')", Statement.RETURN_GENERATED_KEYS); + statement.setString(1, name); + statement.execute(); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request
High	SQL Injection	CWE-89	DBUtil.java:471	1	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 466 to 471 in eb96a35 public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); 1 Data Flow/s detected AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 44 in eb96a35 String username = request.getParameter("username"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/AdminServlet.java Line 49 in eb96a35 String error = DBUtil.addAccount(username, acctType); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 467 in eb96a35 public static String addAccount(String username, String acctType) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 471 in eb96a35 statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 471 in eb96a35 statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); ⛑️ Remediation Suggestion AltoroJ/diffs/1a910b2b-0a1f-4d44-b0fc-f408ee5e5b7d/DBUtil.java.diff Lines 1 to 541 in bbc088b --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); @@ -470,2 +471,4 @@ - Statement statement = connection.createStatement(); - statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); + PreparedStatement statement = connection.prepareStatement( + "INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES (" + "?" + ",'" + acctType + "', 0)"); + statement.setString(1, username); + statement.execute(); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request
High	SQL Injection	CWE-89	DBUtil.java:219	3	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 214 to 219 in eb96a35 return false; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ 3 Data Flow/s detected View Data Flow 1 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/CCApplyServlet.java Line 47 in eb96a35 String passwd = request.getParameter("passwd"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/CCApplyServlet.java Line 51 in eb96a35 if (DBUtil.isValidUser(user.getUsername(), passwd.trim())) { AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/CCApplyServlet.java Line 51 in eb96a35 if (DBUtil.isValidUser(user.getUsername(), passwd.trim())) { AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/CCApplyServlet.java Line 51 in eb96a35 if (DBUtil.isValidUser(user.getUsername(), passwd.trim())) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 212 in eb96a35 public static boolean isValidUser(String user, String password) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ View Data Flow 2 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 79 in eb96a35 String password = request.getParameter("passw"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 80 in eb96a35 password = password.trim().toLowerCase(); //in real life the password usually is case sensitive and this cast would not be done AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 80 in eb96a35 password = password.trim().toLowerCase(); //in real life the password usually is case sensitive and this cast would not be done AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 80 in eb96a35 password = password.trim().toLowerCase(); //in real life the password usually is case sensitive and this cast would not be done AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 80 in eb96a35 password = password.trim().toLowerCase(); //in real life the password usually is case sensitive and this cast would not be done AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 82 in eb96a35 if (!DBUtil.isValidUser(username, password)){ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 212 in eb96a35 public static boolean isValidUser(String user, String password) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ View Data Flow 3 AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 31 in eb96a35 public Response login(String bodyJSON, @Context HttpServletRequest request) throws JSONException { AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 35 in eb96a35 myJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 35 in eb96a35 myJson =new JSONObject(bodyJSON); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 52 in eb96a35 password = myJson.get("password").toString().toLowerCase(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/LoginAPI.java Line 57 in eb96a35 if (!DBUtil.isValidUser(username, password)) { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 212 in eb96a35 public static boolean isValidUser(String user, String password) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 219 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ ⛑️ Remediation Suggestion AltoroJ/diffs/c17e70f0-145b-455a-a3e4-8b137cdfe790/DBUtil.java.diff Lines 1 to 542 in d0dd576 --- original +++ remediated @@ -0,0 +0,0 @@ /** This application is for demonstration use only. It contains known application security vulnerabilities that were created expressly for demonstrating the functionality of application security testing tools. These vulnerabilities may present risks to the technical environment in which the application is installed. You must delete and uninstall this demonstration application upon completion of the demonstration for which it is intended. IBM DISCLAIMS ALL LIABILITY OF ANY KIND RESULTING FROM YOUR USE OF THE APPLICATION OR YOUR FAILURE TO DELETE THE APPLICATION FROM YOUR ENVIRONMENT UPON COMPLETION OF A DEMONSTRATION. IT IS YOUR RESPONSIBILITY TO DETERMINE IF THE PROGRAM IS APPROPRIATE OR SAFE FOR YOUR TECHNICAL ENVIRONMENT. NEVER INSTALL THE APPLICATION IN A PRODUCTION ENVIRONMENT. YOU ACKNOWLEDGE AND ACCEPT ALL RISKS ASSOCIATED WITH THE USE OF THE APPLICATION. IBM AltoroJ (c) Copyright IBM Corp. 2008, 2013 All Rights Reserved. */ package com.ibm.security.appscan.altoromutual.util; import java.sql.Connection; @@ -22,0 +22,1 @@ +import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import com.ibm.security.appscan.Log4AltoroJ; import com.ibm.security.appscan.altoromutual.model.Account; import com.ibm.security.appscan.altoromutual.model.Feedback; import com.ibm.security.appscan.altoromutual.model.Transaction; import com.ibm.security.appscan.altoromutual.model.User; import com.ibm.security.appscan.altoromutual.model.User.Role; /** * Utility class for database operations * @author Alexei * */ public class DBUtil { private static final String PROTOCOL = "jdbc:derby:"; private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; public static final String CREDIT_CARD_ACCOUNT_NAME = "Credit Card"; public static final String CHECKING_ACCOUNT_NAME = "Checking"; public static final String SAVINGS_ACCOUNT_NAME = "Savings"; public static final double CASH_ADVANCE_FEE = 2.50; private static DBUtil instance = null; private Connection connection = null; private DataSource dataSource = null; //private constructor private DBUtil(){ /* ** ** Default location for the database is current directory: ** System.out.println(System.getProperty("user.home")); ** to change DB location, set derby.system.home property: ** System.setProperty("derby.system.home", "[new_DB_location]"); ** */ String dataSourceName = ServletUtil.getAppProperty("database.alternateDataSource"); /* Connect to an external database (e.g. DB2) */ if (dataSourceName != null && dataSourceName.trim().length() > 0){ try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext.lookup("java:comp/env"); dataSource = (DataSource)environmentContext.lookup(dataSourceName.trim()); } catch (Exception e) { e.printStackTrace(); Log4AltoroJ.getInstance().logError(e.getMessage()); } /* Initialize connection to the integrated Apache Derby DB*/ } else { System.setProperty("derby.system.home", System.getProperty("user.home")+"/altoro/"); System.out.println("Derby Home=" + System.getProperty("derby.system.home")); try { //load JDBC driver Class.forName(DRIVER).newInstance(); } catch (Exception e) { Log4AltoroJ.getInstance().logError(e.getMessage()); e.printStackTrace(); } } } private static Connection getConnection() throws SQLException{ if (instance == null) instance = new DBUtil(); if (instance.connection == null || instance.connection.isClosed()){ //If there is a custom data source configured use it to initialize if (instance.dataSource != null){ instance.connection = instance.dataSource.getConnection(); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } return instance.connection; } // otherwise initialize connection to the built-in Derby database try { //attempt to connect to the database instance.connection = DriverManager.getConnection(PROTOCOL+"altoro"); if (ServletUtil.isAppPropertyTrue("database.reinitializeOnStart")){ instance.initDB(); } } catch (SQLException e){ //if database does not exist, create it an initialize it if (e.getErrorCode() == 40000){ instance.connection = DriverManager.getConnection(PROTOCOL+"altoro;create=true"); instance.initDB(); //otherwise pass along the exception } else { throw e; } } } return instance.connection; } /* * Create and initialize the database */ private void initDB() throws SQLException{ Statement statement = connection.createStatement(); try { statement.execute("DROP TABLE PEOPLE"); statement.execute("DROP TABLE ACCOUNTS"); statement.execute("DROP TABLE TRANSACTIONS"); statement.execute("DROP TABLE FEEDBACK"); } catch (SQLException e) { // not a problem } statement.execute("CREATE TABLE PEOPLE (USER_ID VARCHAR(50) NOT NULL, PASSWORD VARCHAR(20) NOT NULL, FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, ROLE VARCHAR(50) NOT NULL, PRIMARY KEY (USER_ID))"); statement.execute("CREATE TABLE FEEDBACK (FEEDBACK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1022, INCREMENT BY 1), NAME VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, COMMENTS VARCHAR(500) NOT NULL, PRIMARY KEY (FEEDBACK_ID))"); statement.execute("CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 800000, INCREMENT BY 1), USERID VARCHAR(50) NOT NULL, ACCOUNT_NAME VARCHAR(100) NOT NULL, BALANCE DOUBLE NOT NULL, PRIMARY KEY (ACCOUNT_ID))"); statement.execute("CREATE TABLE TRANSACTIONS (TRANSACTION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2311, INCREMENT BY 1), ACCOUNTID BIGINT NOT NULL, DATE TIMESTAMP NOT NULL, TYPE VARCHAR(100) NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (TRANSACTION_ID))"); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('admin', 'admin', 'Admin', 'User','admin'), ('jsmith','demo1234', 'John', 'Smith','user'),('jdoe','demo1234', 'Jane', 'Doe','user'),('sspeed','demo1234', 'Sam', 'Speed','user'),('tuser','tuser','Test', 'User','user')"); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('admin','Corporate', 52394783.61), ('admin','"+CHECKING_ACCOUNT_NAME+"', 93820.44), ('jsmith','"+SAVINGS_ACCOUNT_NAME+"', 10000.42), ('jsmith','"+CHECKING_ACCOUNT_NAME+"', 15000.39), ('jdoe','"+SAVINGS_ACCOUNT_NAME+"', 10.00), ('jdoe','"+CHECKING_ACCOUNT_NAME+"', 25.00), ('sspeed','"+SAVINGS_ACCOUNT_NAME+"', 59102.00), ('sspeed','"+CHECKING_ACCOUNT_NAME+"', 150.00)"); statement.execute("INSERT INTO ACCOUNTS (ACCOUNT_ID,USERID,ACCOUNT_NAME,BALANCE) VALUES (4539082039396288,'jsmith','"+CREDIT_CARD_ACCOUNT_NAME+"', 100.42),(4485983356242217,'jdoe','"+CREDIT_CARD_ACCOUNT_NAME+"', 10000.97)"); statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID,DATE,TYPE,AMOUNT) VALUES (800003,'2017-03-19 15:02:19.47','Withdrawal', -100.72), (800002,'2017-03-19 15:02:19.47','Deposit', 100.72), (800003,'2018-03-19 11:33:19.21','Withdrawal', -1100.00), (800002,'2018-03-19 11:33:19.21','Deposit', 1100.00), (800003,'2018-03-19 18:00:00.33','Withdrawal', -600.88), (800002,'2018-03-19 18:00:00.33','Deposit', 600.88), (800002,'2019-03-07 04:22:19.22','Withdrawal', -400.00), (800003,'2019-03-07 04:22:19.22','Deposit', 400.00), (800002,'2019-03-08 09:00:00.22','Withdrawal', -100.00), (800003,'2019-03-08 09:22:00.22','Deposit', 100.00), (800002,'2019-03-11 16:00:00.10','Withdrawal', -400.00), (800003,'2019-03-11 16:00:00.10','Deposit', 400.00), (800005,'2018-01-10 15:02:19.47','Withdrawal', -100.00), (800004,'2018-01-10 15:02:19.47','Deposit', 100.00), (800004,'2018-04-14 04:22:19.22','Withdrawal', -10.00), (800005,'2018-04-14 04:22:19.22','Deposit', 10.00), (800004,'2018-05-15 09:00:00.22','Withdrawal', -10.00), (800005,'2018-05-15 09:22:00.22','Deposit', 10.00), (800004,'2018-06-11 11:01:30.10','Withdrawal', -10.00), (800005,'2018-06-11 11:01:30.10','Deposit', 10.00)"); Log4AltoroJ.getInstance().logInfo("Database initialized"); } /** * Retrieve feedback details * @param feedbackId specific feedback ID to retrieve or Feedback.FEEDBACK_ALL to retrieve all stored feedback submissions */ public static ArrayList<Feedback> getFeedback (long feedbackId){ ArrayList<Feedback> feedbackList = new ArrayList<Feedback>(); try { Connection connection = getConnection(); Statement statement = connection.createStatement(); String query = "SELECT * FROM FEEDBACK"; if (feedbackId != Feedback.FEEDBACK_ALL){ query = query + " WHERE FEEDBACK_ID = "+ feedbackId +""; } ResultSet resultSet = statement.executeQuery(query); while (resultSet.next()){ String name = resultSet.getString("NAME"); String email = resultSet.getString("EMAIL"); String subject = resultSet.getString("SUBJECT"); String message = resultSet.getString("COMMENTS"); long id = resultSet.getLong("FEEDBACK_ID"); Feedback feedback = new Feedback(id, name, email, subject, message); feedbackList.add(feedback); } } catch (SQLException e) { Log4AltoroJ.getInstance().logError("Error retrieving feedback: " + e.getMessage()); } return feedbackList; } /** * Authenticate user * @param user user name * @param password password * @return true if valid user, false otherwise * @throws SQLException */ public static boolean isValidUser(String user, String password) throws SQLException{ if (user == null || password == null || user.trim().length() == 0 || password.trim().length() == 0) return false; Connection connection = getConnection(); @@ -217,1 +218,4 @@ - Statement statement = connection.createStatement(); + PreparedStatement statement = connection + .prepareStatement("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '" + user + "' AND PASSWORD=" + "?"); + statement.setString(1, password); + ResultSet resultSet =statement.executeQuery(); /* BAD - user input should always be sanitized */ @@ -219,2 +223,0 @@ - ResultSet resultSet =statement.executeQuery("SELECT COUNT(*)FROM PEOPLE WHERE USER_ID = '"+ user +"' AND PASSWORD='" + password + "'"); /* BAD - user input should always be sanitized */ - if (resultSet.next()){ if (resultSet.getInt(1) > 0) return true; } return false; } /** * Get user information * @param username * @return user information * @throws SQLException */ public static User getUserInfo(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT FIRST_NAME,LAST_NAME,ROLE FROM PEOPLE WHERE USER_ID = '"+ username +"' "); /* BAD - user input should always be sanitized */ String firstName = null; String lastName = null; String roleString = null; if (resultSet.next()){ firstName = resultSet.getString("FIRST_NAME"); lastName = resultSet.getString("LAST_NAME"); roleString = resultSet.getString("ROLE"); } if (firstName == null || lastName == null) return null; User user = new User(username, firstName, lastName); if (roleString.equalsIgnoreCase("admin")) user.setRole(Role.Admin); return user; } /** * Get all accounts for the specified user * @param username * @return * @throws SQLException */ public static Account[] getAccounts(String username) throws SQLException{ if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ long accountId = resultSet.getLong("ACCOUNT_ID"); String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountId, name, balance); accounts.add(newAccount); } return accounts.toArray(new Account[accounts.size()]); } /** * Transfer funds between specified accounts * @param username * @param creditActId * @param debitActId * @param amount * @return */ public static String transferFunds(String username, long creditActId, long debitActId, double amount) { try { User user = getUserInfo(username); Connection connection = getConnection(); Statement statement = connection.createStatement(); Account debitAccount = Account.getAccount(debitActId); Account creditAccount = Account.getAccount(creditActId); if (debitAccount == null){ return "Originating account is invalid"; } if (creditAccount == null) return "Destination account is invalid"; java.sql.Timestamp date = new Timestamp(new java.util.Date().getTime()); //in real life we would want to do these updates and transaction entry creation //as one atomic operation long userCC = user.getCreditCardNumber(); /* this is the account that the payment will be made from, thus negative amount!*/ double debitAmount = -amount; /* this is the account that the payment will be made to, thus positive amount!*/ double creditAmount = amount; /* Credit card account balance is the amount owed, not amount owned * (reverse of other accounts). Therefore we have to process balances differently*/ if (debitAccount.getAccountId() == userCC) debitAmount = -debitAmount; //create transaction record statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"',"+((debitAccount.getAccountId() == userCC)?"'Cash Advance'":"'Withdrawal'")+","+debitAmount+")," + "("+creditAccount.getAccountId()+",'"+date+"',"+((creditAccount.getAccountId() == userCC)?"'Payment'":"'Deposit'")+","+creditAmount+")"); Log4AltoroJ.getInstance().logTransaction(debitAccount.getAccountId()+" - "+ debitAccount.getAccountName(), creditAccount.getAccountId()+" - "+ creditAccount.getAccountName(), amount); if (creditAccount.getAccountId() == userCC) creditAmount = -creditAmount; //add cash advance fee since the money transfer was made from the credit card if (debitAccount.getAccountId() == userCC){ statement.execute("INSERT INTO TRANSACTIONS (ACCOUNTID, DATE, TYPE, AMOUNT) VALUES ("+debitAccount.getAccountId()+",'"+date+"','Cash Advance Fee',"+CASH_ADVANCE_FEE+")"); debitAmount += CASH_ADVANCE_FEE; Log4AltoroJ.getInstance().logTransaction(String.valueOf(userCC), "N/A", CASH_ADVANCE_FEE); } //update account balances statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (debitAccount.getBalance()+debitAmount) + " WHERE ACCOUNT_ID = " + debitAccount.getAccountId()); statement.execute("UPDATE ACCOUNTS SET BALANCE = " + (creditAccount.getBalance()+creditAmount) + " WHERE ACCOUNT_ID = " + creditAccount.getAccountId()); return null; } catch (SQLException e) { return "Transaction failed. Please try again later."; } } /** * Get transaction information for the specified accounts in the date range (non-inclusive of the dates) * @param startDate * @param endDate * @param accounts * @param rowCount * @return */ public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { if (accounts == null || accounts.length == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); if (rowCount > 0) statement.setMaxRows(rowCount); StringBuffer acctIds = new StringBuffer(); acctIds.append("ACCOUNTID = " + accounts[0].getAccountId()); for (int i=1; i<accounts.length; i++){ acctIds.append(" OR ACCOUNTID = "+accounts[i].getAccountId()); } String dateString = null; if (startDate != null && startDate.length()>0 && endDate != null && endDate.length()>0){ dateString = "DATE BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59'"; } else if (startDate != null && startDate.length()>0){ dateString = "DATE > '" + startDate +" 00:00:00'"; } else if (endDate != null && endDate.length()>0){ dateString = "DATE < '" + endDate + " 23:59:59'"; } String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); } catch (SQLException e){ int errorCode = e.getErrorCode(); if (errorCode == 30000) throw new SQLException("Date-time query must be in the format of yyyy-mm-dd HH:mm:ss", e); throw e; } ArrayList<Transaction> transactions = new ArrayList<Transaction>(); while (resultSet.next()){ int transId = resultSet.getInt("TRANSACTION_ID"); long actId = resultSet.getLong("ACCOUNTID"); Timestamp date = resultSet.getTimestamp("DATE"); String desc = resultSet.getString("TYPE"); double amount = resultSet.getDouble("AMOUNT"); transactions.add(new Transaction(transId, actId, date, desc, amount)); } return transactions.toArray(new Transaction[transactions.size()]); } public static String[] getBankUsernames() { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); //at the moment this query limits transfers to //transfers between two user accounts ResultSet resultSet =statement.executeQuery("SELECT USER_ID FROM PEOPLE"); ArrayList<String> users = new ArrayList<String>(); while (resultSet.next()){ String name = resultSet.getString("USER_ID"); users.add(name); } return users.toArray(new String[users.size()]); } catch (SQLException e){ e.printStackTrace(); return new String[0]; } } public static Account getAccount(long accountNo) throws SQLException { Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = "+ accountNo +" "); /* BAD - user input should always be sanitized */ ArrayList<Account> accounts = new ArrayList<Account>(3); while (resultSet.next()){ String name = resultSet.getString("ACCOUNT_NAME"); double balance = resultSet.getDouble("BALANCE"); Account newAccount = new Account(accountNo, name, balance); accounts.add(newAccount); } if (accounts.size()==0) return null; return accounts.get(0); } public static String addAccount(String username, String acctType) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ACCOUNTS (USERID,ACCOUNT_NAME,BALANCE) VALUES ('"+username+"','"+acctType+"', 0)"); return null; } catch (SQLException e){ return e.toString(); } } public static String addSpecialUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO SPECIAL_CUSTOMERS (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String addUser(String username, String password, String firstname, String lastname) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO PEOPLE (USER_ID,PASSWORD,FIRST_NAME,LAST_NAME,ROLE) VALUES ('"+username+"','"+password+"', '"+firstname+"', '"+lastname+"','user')"); return null; } catch (SQLException e){ return e.toString(); } } public static String changePassword(String username, String password) { try { Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("UPDATE PEOPLE SET PASSWORD = '"+ password +"' WHERE USER_ID = '"+username+"'"); return null; } catch (SQLException e){ return e.toString(); } } public static long storeFeedback(String name, String email, String subject, String comments) { try{ Connection connection = getConnection(); Statement statement = connection.createStatement(); statement.execute("INSERT INTO FEEDBACK (NAME,EMAIL,SUBJECT,COMMENTS) VALUES ('"+name+"', '"+email+"', '"+subject+"', '"+comments+"')", Statement.RETURN_GENERATED_KEYS); ResultSet rs= statement.getGeneratedKeys(); long id = -1; if (rs.next()){ id = rs.getLong(1); } return id; } catch (SQLException e){ Log4AltoroJ.getInstance().logError(e.getMessage()); return -1; } } } Create Pull Request

No Automatic Remediation (4)

Severity	Vulnerability Type	CWE	File	Data Flows	Date
High	Command Injection	CWE-78	index.jsp:65	2	2023-11-21 04:36am
Vulnerable Code AltoroJ/WebContent/index.jsp Lines 60 to 65 in eb96a35 shell = "bash"; shellarg = "-c"; command = "cat '" + path + "/" + content +"'"; } Process proc = Runtime.getRuntime().exec(new String[] {shell, shellarg, command}); 2 Data Flow/s detected View Data Flow 1 AltoroJ/WebContent/index.jsp Line 38 in eb96a35 content = request.getParameter("content"); AltoroJ/WebContent/index.jsp Line 62 in eb96a35 command = "cat '" + path + "/" + content +"'"; AltoroJ/WebContent/index.jsp Line 65 in eb96a35 Process proc = Runtime.getRuntime().exec(new String[] {shell, shellarg, command}); AltoroJ/WebContent/index.jsp Line 65 in eb96a35 Process proc = Runtime.getRuntime().exec(new String[] {shell, shellarg, command}); View Data Flow 2 AltoroJ/WebContent/index.jsp Line 38 in eb96a35 content = request.getParameter("content"); AltoroJ/WebContent/index.jsp Line 62 in eb96a35 command = "cat '" + path + "/" + content +"'"; AltoroJ/WebContent/index.jsp Line 65 in eb96a35 Process proc = Runtime.getRuntime().exec(new String[] {shell, shellarg, command}); AltoroJ/WebContent/index.jsp Line 65 in eb96a35 Process proc = Runtime.getRuntime().exec(new String[] {shell, shellarg, command});
High	Code Injection	CWE-94	serverStatusCheck.html:41	1	2023-11-21 04:27am
Vulnerable Code AltoroJ/WebContent/util/serverStatusCheck.html Lines 36 to 41 in eb96a35 } function StateChangeForJSON() { if(xmlHttp.readyState == 4 && xmlHttp.status == 200) { var jsonObj = eval('('+ xmlHttp.responseText + ')'); 1 Data Flow/s detected AltoroJ/WebContent/util/serverStatusCheck.html Line 41 in eb96a35 var jsonObj = eval('('+ xmlHttp.responseText + ')');
High	SQL Injection	CWE-89	DBUtil.java:276	2	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 271 to 276 in eb96a35 if (username == null || username.trim().length() == 0) return null; Connection connection = getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ 2 Data Flow/s detected View Data Flow 1 AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 75 in eb96a35 username = request.getParameter("uid"); AltoroJ/src/com/ibm/security/appscan/altoromutual/servlet/LoginServlet.java Line 94 in eb96a35 Cookie accountCookie = ServletUtil.establishSession(username,session); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 340 in eb96a35 public static Cookie establishSession(String username, HttpSession session){ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 342 in eb96a35 User user = DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 236 in eb96a35 public static User getUserInfo(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 256 in eb96a35 User user = new User(username, firstName, lastName); AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 41 in eb96a35 public User(String username, String firstName, String lastName) { AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 42 in eb96a35 this.username = username; AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 256 in eb96a35 User user = new User(username, firstName, lastName); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 342 in eb96a35 User user = DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/ServletUtil.java Line 343 in eb96a35 Account[] accounts = user.getAccounts(); AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 76 in eb96a35 public Account[] getAccounts(){ AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 78 in eb96a35 return DBUtil.getAccounts(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 270 in eb96a35 public static Account[] getAccounts(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 276 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 276 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ View Data Flow 2 AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 136 in eb96a35 String accessToken = request.getHeader("Authorization").replaceAll("Bearer ", ""); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 139 in eb96a35 String decodedToken = new String(Base64.decodeBase64(accessToken)); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 140 in eb96a35 StringTokenizer tokenizer = new StringTokenizer(decodedToken,":"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 140 in eb96a35 StringTokenizer tokenizer = new StringTokenizer(decodedToken,":"); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 141 in eb96a35 String username = new String(Base64.decodeBase64(tokenizer.nextToken())); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 142 in eb96a35 return DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 236 in eb96a35 public static User getUserInfo(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 256 in eb96a35 User user = new User(username, firstName, lastName); AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 41 in eb96a35 public User(String username, String firstName, String lastName) { AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 42 in eb96a35 this.username = username; AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 256 in eb96a35 User user = new User(username, firstName, lastName); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/OperationsUtil.java Line 142 in eb96a35 return DBUtil.getUserInfo(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AccountAPI.java Line 35 in eb96a35 Account[] account = (OperationsUtil.getUser(request)).getAccounts(); AltoroJ/src/com/ibm/security/appscan/altoromutual/api/AccountAPI.java Line 35 in eb96a35 Account[] account = (OperationsUtil.getUser(request)).getAccounts(); AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 76 in eb96a35 public Account[] getAccounts(){ AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 78 in eb96a35 return DBUtil.getAccounts(username); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 270 in eb96a35 public static Account[] getAccounts(String username) throws SQLException{ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 276 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */ AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 276 in eb96a35 ResultSet resultSet =statement.executeQuery("SELECT ACCOUNT_ID, ACCOUNT_NAME, BALANCE FROM ACCOUNTS WHERE USERID = '"+ username +"' "); /* BAD - user input should always be sanitized */
High	SQL Injection	CWE-89	DBUtil.java:403	1	2023-11-21 04:36am
Vulnerable Code AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Lines 398 to 403 in eb96a35 String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; ResultSet resultSet = null; try { resultSet = statement.executeQuery(query); 1 Data Flow/s detected AltoroJ/WebContent/bank/transaction.jsp Line 41 in eb96a35 String endString = request.getParameter("endTime"); AltoroJ/WebContent/bank/transaction.jsp Line 47 in eb96a35 transactions = user.getUserTransactions(startString, endString, user.getAccounts()); AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 101 in eb96a35 public Transaction[] getUserTransactions(String startDate, String endDate, Account[] accounts) throws SQLException { AltoroJ/src/com/ibm/security/appscan/altoromutual/model/User.java Line 104 in eb96a35 transactions = DBUtil.getTransactions(startDate, endDate, accounts, -1); AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 370 in eb96a35 public static Transaction[] getTransactions(String startDate, String endDate, Account[] accounts, int rowCount) throws SQLException { AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 396 in eb96a35 dateString = "DATE < '" + endDate + " 23:59:59'"; AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 399 in eb96a35 String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 399 in eb96a35 String query = "SELECT * FROM TRANSACTIONS WHERE (" + acctIds.toString() + ") " + ((dateString==null)?"": "AND (" + dateString + ") ") + "ORDER BY DATE DESC" ; AltoroJ/src/com/ibm/security/appscan/altoromutual/util/DBUtil.java Line 403 in eb96a35 resultSet = statement.executeQuery(query);

Findings Overview

Severity	Vulnerability Type	CWE	Language	Count
High	Cross-Site Scripting	CWE-79	Java*	11
High	Command Injection	CWE-78	Java*	1
High	SQL Injection	CWE-89	Java*	8
High	DOM Based Cross-Site Scripting	CWE-79	JavaScript / Node.js	3
High	Code Injection	CWE-94	JavaScript / Node.js	1
Medium	Trust Boundary Violation	CWE-501	Java*	2
Medium	Error Messages Information Exposure	CWE-209	Java*	10
Medium	Hardcoded Password/Credentials	CWE-798	JavaScript / Node.js	1
Low	Unvalidated/Open Redirect	CWE-601	JavaScript / Node.js	4