I want to implement site which allows user to generate and download CSV file. The page is secured by Spring Security with basic authentication:
@Configuration
@EnableWebSecurity
public class CustomWebSecurityConfigurerAdapter extends WebSecurityConfigurerAdapter {
@Override
protected void configure(HttpSecurity httpSecurity) throws Exception {
httpSecurity.authorizeRequests().anyRequest().authenticated()
.and().httpBasic();
}
@Autowired
public void configureGlobal(AuthenticationManagerBuilder authentication)
throws Exception
{
authentication.inMemoryAuthentication()
.withUser("admin")
.password(passwordEncoder().encode("admin"))
.authorities("ROLE_USER");
}
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
}
I have to enable loading the file directly into Excel via Power Query by entering URL as filename:
After entering URL and providing credentials nothing happens and Excel still asks for them. Furthermore, compiler gives info about exception being thrown:
2019-07-12 14:05:15.610 ERROR 15152 --- [nio-8080-exec-8] o.s.b.w.servlet.support.ErrorPageFilter : Forwarding to error page from request [/worklogs] due to exception [The request was rejected because the HTTP method "PROPFIND" was not included within the whitelist [HEAD, DELETE, POST, GET, OPTIONS, PATCH, PUT]]
org.springframework.security.web.firewall.RequestRejectedException: The request was rejected because the HTTP method "PROPFIND" was not included within the whitelist [HEAD, DELETE, POST, GET, OPTIONS, PATCH, PUT]
at org.springframework.security.web.firewall.StrictHttpFirewall.rejectForbiddenHttpMethod(StrictHttpFirewall.java:316) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.firewall.StrictHttpFirewall.getFirewalledRequest(StrictHttpFirewall.java:292) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:194) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:128) ~[spring-boot-2.1.6.RELEASE.jar:2.1.6.RELEASE]
at org.springframework.boot.web.servlet.support.ErrorPageFilter.access$000(ErrorPageFilter.java:66) ~[spring-boot-2.1.6.RELEASE.jar:2.1.6.RELEASE]
at org.springframework.boot.web.servlet.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:103) ~[spring-boot-2.1.6.RELEASE.jar:2.1.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:121) ~[spring-boot-2.1.6.RELEASE.jar:2.1.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) ~[spring-web-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[catalina.jar:9.0.21]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[catalina.jar:9.0.21]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[catalina.jar:9.0.21]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678) ~[catalina.jar:9.0.21]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[catalina.jar:9.0.21]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[catalina.jar:9.0.21]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) ~[tomcat-coyote.jar:9.0.21]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-coyote.jar:9.0.21]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853) ~[tomcat-coyote.jar:9.0.21]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587) ~[tomcat-coyote.jar:9.0.21]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-coyote.jar:9.0.21]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-util.jar:9.0.21]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Below I put all the code responsible for generating and downloading CSV file:
@GetMapping("/worklogs")
public String passForm(@RequestParam Map<String,String> allRequestParams, HttpServletRequest request, Model model) throws IOException, JSONException {
StringBuilder stringParams = new StringBuilder("?");
for (Map.Entry<String, String> entry : allRequestParams.entrySet()){
stringParams.append(entry.getKey()).append("=").append(entry.getValue()).append("&");
}
// if true, generate and download .csv file, else - display table
if(Objects.equals("on", request.getParameter("csv"))){
return "redirect:/worklogs/csv" + stringParams;
}
return showAsTable(model, request, stringParams.toString());
}
@GetMapping("/csv")
public void showAsCsv(HttpServletRequest request, HttpServletResponse response) throws IOException, JSONException {
currentLogs = getFilteredWorklogs(request);
Collections.sort(currentLogs);
StringBuilder content = new StringBuilder("updated;username;email;comment;hours;issue_id;issue_url" + "\n");
for (Worklog w : currentLogs) {
content
.append("\"")
.append(w.getUpdated())
.append("\";\"")
.append(w.getUpdateAuthor().getName())
.append("\";\"")
.append(w.getUpdateAuthor().getEmailAddress())
.append("\";\"")
.append(w.getComment())
.append("\";\"")
.append(w.getHoursSpent())
.append("\";\"")
.append(w.getIssueKey())
.append("\";\"")
.append(w.getIssueUrl())
.append("\"\n");
}
currentLogs = null;
response.setContentType("text/plain; charset=utf-8");
response.addHeader("Content-disposition", "attachment; filename=" + "worklogs.csv");
response.getWriter().print(content);
}
public List<Worklog> getFilteredWorklogs(HttpServletRequest request) throws IOException, JSONException {
String since = request.getParameter("since");
String days = request.getParameter("days");
String beginString = request.getParameter("begin");
String endString = request.getParameter("end");
String projectName = request.getParameter("project");
String userName = request.getParameter("user");
LocalDate begin, end;
LocalDate[] dates = convertStringInterval(since, days, beginString, endString);
// true if an date input error occured
if (Objects.isNull(dates)){
return null;
}
begin = dates[0];
end = dates[1];
// getting all worklogs updated between begin and end dates
List<Long> updatedIds = getIdsUpdatedBetween(begin, end);
if(Objects.isNull(updatedIds)){
return null;
}
// splitting list of updated worklogs into lists containing 1000 worklogs (Jira REST API limit)
List<List<Long>> groupedUpdatedIds = new ArrayList<>();
int groupBegin = 0, groupEnd = 999;
// if true, grouping is needed
if(updatedIds.size() > 1000){
do{
groupedUpdatedIds.add(updatedIds.subList(groupBegin, groupEnd));
groupBegin = groupEnd + 1;
if(groupEnd + 1000 < updatedIds.size()){
groupEnd += 1000;
} else groupEnd = updatedIds.size() - 1;
} while (groupEnd < updatedIds.size() - 1);
} else groupedUpdatedIds.add(updatedIds);
List<Worklog> filteredLogs = new ArrayList<>();
for(List<Long> list : groupedUpdatedIds){
String body = parseIdsToJSON(list);
filteredLogs.addAll(extractWorklog(body, prefix + "/rest/api/2/worklog/list"));
}
List<Issue> issues = issueController.getIssuesFromIdList(filteredLogs);
for (Worklog filteredLog : filteredLogs) {
for (Issue is : issues) {
if (Objects.equals(is.getId(), filteredLog.getIssueId())) {
filteredLog.setIssueKey(is.getKey());
filteredLog.setIssueUrl(is.getSelf());
filteredLog.setProjectName(is.getProjectName());
break;
}
}
}
return filteredLogs;
}
public LocalDate[] convertStringInterval(String since, String days, String beginString, String endString){
LocalDate[] interval = new LocalDate[2];
if(Objects.isNull(filters)){
filters = new ArrayList<>();
}
switch(since){
case "week":{
interval[0] = LocalDate.now().minusWeeks(1);
interval[1] = LocalDate.now();
filters.add("since last week");
break;
}
case "month":{
interval[0] = LocalDate.now().minusMonths(1);
interval[1] = LocalDate.now();
filters.add("since last month");
break;
}
case "year":{
interval[0] = LocalDate.now().minusYears(1);
interval[1] = LocalDate.now();
filters.add("since last year");
break;
}
case "days":{
if(!Objects.equals("", days)){
long longDays = Long.parseLong(days);
if(longDays > 0){
interval[0] = LocalDate.now().minusDays(longDays);
interval[1] = LocalDate.now();
filters.add("since last " + longDays + " days");
} else {
prompt.addMessage("wrongdays");
return null;
}
} else {
prompt.addMessage("nodays");
return null;
}
break;
}
case "interval":{
if(!Objects.equals("", beginString) && !Objects.equals("", endString)){
interval[0] = LocalDate.parse(beginString);
interval[1] = LocalDate.parse(endString);
filters.add("between " + beginString + " and " + endString);
} else {
prompt.addMessage("nointerval");
return null;
}
break;
}
default:
throw new IllegalStateException("Unexpected value: " + since);
}
return interval;
}
private List<Long> getIdsUpdatedBetween(LocalDate begin, LocalDate end){
// getting worklogs updated since 'begin' to present
String uri = prefix + "/rest/api/2/worklog/updated?since=" + DateConverter.localDateToEpoch(begin);
boolean lastPage;
List<Long> filteredWorklogsIds = new ArrayList<>();
try{
String allWorklogsString;
JSONObject obj;
do{
// parsing JSON object to String
allWorklogsString = invokeGetMethod(hash, uri);
if(Objects.isNull(allWorklogsString)){
prompt.addMessage("notloggedin");
return null;
}
obj = new JSONObject(Objects.requireNonNull(allWorklogsString));
JSONArray arr = obj.getJSONArray("values");
long endAsLong = DateConverter.localDateToEpoch(end);
for (int i = 0; i < arr.length(); i++){
long worklogId = Long.parseLong(arr.getJSONObject(i).getString("worklogId"));
long updatedTime = Long.parseLong(arr.getJSONObject(i).getString("updatedTime"));
// getting worklogs updated until end included
if(updatedTime <= endAsLong)
filteredWorklogsIds.add(worklogId);
}
lastPage = obj.getBoolean("lastPage");
if(!lastPage)
uri = obj.getString("nextPage");
} while(!lastPage);
} catch (JSONException e) {
e.printStackTrace();
}
return filteredWorklogsIds;
}
public List<Worklog> extractWorklog(String body, String url){
MultiValueMap<String, String> multiValueMap = new LinkedMultiValueMap<>();
multiValueMap.add("Content-Type", "application/json");
multiValueMap.add("Authorization", "Basic " + hash);
multiValueMap.add("Accept", "application/json");
HttpEntity<String> httpEntity = new HttpEntity<>(body, multiValueMap);
ResponseEntity<List<Worklog>> responseEntity = restTemplate.exchange(url, HttpMethod.POST, httpEntity, new ParameterizedTypeReference<List<Worklog>>(){});
return responseEntity.getBody();
}
public List<Issue> getIssuesFromIdList(List<Worklog> worklogs) throws JSONException, IOException {
String issuesJqlQuery = "";
List<Issue> issues = new ArrayList<>();
for(int i = 1; i <= worklogs.size(); i++){
if(i%1000 == 1){
issuesJqlQuery = "{\"jql\":\"";
}
issuesJqlQuery += "id=" + worklogs.get(i - 1).getIssueId();
if(i%1000 != 0 && i < worklogs.size()){
issuesJqlQuery += " OR ";
} else {
issuesJqlQuery += "\",\"maxResults\":1000}";
String responseBody = extractIssues(issuesJqlQuery, prefix + "/search");
JSONObject obj = new JSONObject(Objects.requireNonNull(responseBody));
JSONArray arr = obj.getJSONArray("issues");
for(int j = 0; j < arr.length(); j++){
Long id = Long.parseLong(arr.getJSONObject(j).getString("id")); // getting issue id
String key = arr.getJSONObject(j).getString("key"); // getting issue key
String self = arr.getJSONObject(j).getString("self"); // getting issue url
// getting project username
String fields = arr.getJSONObject(j).getString("fields");
JSONObject projectObj = new JSONObject(fields);
String project = projectObj.getString("project");
JSONObject projectObj2 = new JSONObject(project);
String projectName = projectObj2.getString("name");
issues.add(new Issue(id, self, key, projectName));
}
}
}
return issues;
}
EDIT: I have created test controller with hardcoded CSV file content in the same project. It is loaded in Excel without problems, so I guess Spring Security configuration is ok.
@Controller
public class Controller {
@GetMapping("/test")
public void test(HttpServletResponse response) throws IOException {
response.setContentType("text/plain; charset=utf-8");
response.addHeader("Content-disposition", "attachment; filename=" + "worklogs.csv");
response.getWriter().print("updated;username;email;comment;hours;issue_id;issue_url\n" +
"\"2019-07-04T08:37:21.958+0000\";\"test1\";\"test2\";\"\";\"2\";\"test3\";\"test4\"\n");
}
}
Downloading file via browser or Postman works correctly and there aren't any problems with authorization. I have no idea, why authorization via Excel doesn't work, so I will be glad of any sugestions.
