Logging Spring Boot request url and response code to database

Question

I'm working on a requirement to log the following details "username, authentication_status, remote_ip, request_url, response_code" to an Oracle database for each GET request made to a Spring Boot application for both successful and unsuccessful requests.

I've currently got the below class which is recording the username, auth status and requesting ip address okay but I'm not able to get the requested url and response code. Not overly familiar with Spring Boot but is it possible for me to get these details easily?

@Component
public class AuditLogger {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @EventListener
    public void auditEventHappened(AuditApplicationEvent auditApplicationEvent) {

        AuditEvent auditEvent = auditApplicationEvent.getAuditEvent();
        WebAuthenticationDetails details = (WebAuthenticationDetails) auditEvent.getData().get("details");

        // Log this to the database
        String sqlStatement = "INSERT INTO API_AUDIT(USERNAME,AUTHENTICATION_STATUS,REMOTE_IP,REQUEST_URL,RESPONSE_CODE) VALUES (" 
                            + "'" + auditEvent.getPrincipal() + "', "
                            + "'" + auditEvent.getType() + "', "
                            + "'" + details.getRemoteAddress() + "',"
                            + "null," // This should be the request url
                            + "null"  // This should be the response code
                            + ")";

        jdbcTemplate.execute(sqlStatement);
    }
}

According to this: https://stackoverflow.com/a/6301038/5695673, you can try to get the current request with `RequestContextHolder.getRequestAttributes().getRequest()` — gWombat, Feb 07 '18 at 10:41
check below two, https://stackoverflow.com/questions/31159075/how-to-find-out-the-currently-logged-in-user-in-spring-boot https://stackoverflow.com/questions/37710557/how-to-get-request-url-in-spring-boot-restcontroller — Manjit Ullal, Feb 07 '18 at 10:43
@gWombat thanks I was able to get the URI following that link! Now just need to see if I can get the response code, although I'm not sure if that can be done at this level as this is just request not response based? — Matt Damon, Feb 07 '18 at 11:08
I'm not sure you can get the response here. But you can have a look [here](https://docs.spring.io/spring-boot/docs/current/reference/html/production-ready-tracing.html), In Spring Boot you actuator have almost all what you want. You just have to find a way to store the results in a db instead of in-memory. — gWombat, Feb 07 '18 at 11:16
I would also suggest you use named or placeholder ("?") parameters instead of string concatenation for your SQL query as it would protect your code from SQL injection attacks. — Simon Berthiaume, Feb 07 '18 at 11:32

score 0 · Accepted Answer · answered Jul 17 '18 at 08:44

Maybe not the most elegant or efficient solution but appears to be working and fits my requirements. auditEventHappened is triggered per request which inserts the request information and pulls back the Oracle rowId which is then used in the doFilterInternal method to update the response code on the database record. Feel free to comment improvements.

// Hold the ID of the inserted audit record.
private Integer rowId; 

protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException {
    ContentCachingRequestWrapper wrappedRequest = new ContentCachingRequestWrapper(request);

    filterChain.doFilter(wrappedRequest, response);
    Integer status = response.getStatus();

    String query = "UPDATE AUDIT_LOG SET RESPONSE = :responseCode where id = :rowId";
    SqlParameterSource parameters = new MapSqlParameterSource()
        .addValue("responseCode", status)
        .addValue("rowId", rowId);

    namedParameterJdbcTemplate.update(query, parameters);
}


@EventListener
public void auditEventHappened(AuditApplicationEvent auditApplicationEvent) {

    AuditEvent auditEvent = auditApplicationEvent.getAuditEvent();

    ServletRequestAttributes sra = (ServletRequestAttributes)RequestContextHolder.getRequestAttributes();
    HttpServletRequest req = sra.getRequest();

    String query = "INSERT INTO AUDIT_LOG (USERNAME,AUTH_STATUS,REQUEST_IP,SERVER_IP,REQUEST_URL,RESPONSE_CODE) VALUES (:1,:2,:3,:4,:5,:6)";
    KeyHolder holder = new GeneratedKeyHolder(); // Required to get the inserted record ID from the database. 

    SqlParameterSource parameters = new MapSqlParameterSource()
            .addValue("1", auditEvent.getPrincipal())
            .addValue("2", auditEvent.getType())
            .addValue("3", req.getRemoteAddr())
            .addValue("4", req.getLocalAddr())
            .addValue("5", req.getRequestURI())
            .addValue("6", null);      

    // Issue the statement to the database. Pull back the ID column of inserted record. Store in keyholder.
    namedParameterJdbcTemplate.update(query, parameters, holder, new String[]{"ID"});
    rowId = holder.getKey().intValue(); // Get the inserted ID from the keyholder, set globally for doFilterInternal which is about to trigger. 

}

Logging Spring Boot request url and response code to database

1 Answers1