[Fortify] Fix SQL Injection

Before

code snippet:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

public List<AccountDtl> getAcctDetail(List<String> acctNos) throws SQLException { List<AccountDtl> result = new ArrayList<AccountDtl>(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String accounts = ""; for (String account : acctNos) { accounts = accounts + "'" + account + "',"; } String sql = "select DISTINCT ( TRIM(REC_ID)) , NO , ID from " + Table.test.getFullName() + " where acct_no in (" + accounts.substring(0, accounts.length() - 1) + ")"; try { conn = getConnection(); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); // ignore code snippet regarding get values } catch (SQLException e) { throw e; } finally { close(rs); close(pst); close(conn); } return result; }

After

Add dependency to your pom.xml

1 2 3 4 5

<dependency> <groupId>org.owasp.esapi</groupId> <artifactId>esapi</artifactId> <version>2.1.0</version> </dependency>

Updated code snippet (If you are not using DB2 as your database, please apply other Codec, ex. OracleCodec):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

import org.owasp.esapi.ESAPI; import org.owasp.esapi.codecs.DB2Codec; // ignore code snippet public List<AccountDtl> getAcctDetail(List<String> acctNos) throws SQLException { List<AccountDtl> result = new ArrayList<AccountDtl>(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String accounts = ""; for (String account : acctNos) { accounts = accounts + "'" + ESAPI.encoder().encodeForSQL(new DB2Codec(), account) + "',"; } String sql = "select DISTINCT ( TRIM(REC_ID)) , NO , ID from " + Table.test.getFullName() + " where acct_no in (" + accounts.substring(0, accounts.length() - 1) + ")"; try { conn = getConnection(); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); // ignore code snippet regarding get values } catch (SQLException e) { throw e; } finally { close(rs); close(pst); close(conn); } return result; }