Category Archives: Java/Java EE

how to use cells as tab name variables in excel

Posted on by
Reply

I have a co-worker who is an Excel wizard. He set up a system for automatically gathering data using references to other tables. I’ve learned a lot from him like how to use lookup tables, filters, reference other files, etc. Microsoft lists out the syntax for referring to other sheets.

There was one one instance where we needed the tab name to vary based on another cell. We’ve been doing this by hand each time we add a new tab. It’s not hard, but it is boring and tedious. Being a good computer programmer, I want the computer to do tedious for me. The problem is that it doesn’t take long enough to update it by hand so I can never justify looking into it.

I recently installed Office on my Windows 8 VM because I needed to see how something looked in real Word. (vs Open Office.) Since I now have Excel, I decided to try it out. I got it working much faster than I thought.

Just like programming, I did iterations to build up to it.

Iteration 1 – sum another tab

I created file1.xlsx with two tabs: tab1 and tab2. In each, I added some numbers to the first column of each. I then wrote the formula to add them up:

=SUM([file1.xlsx]tab1!$A:$A)

Explaining what this means:

  • [file1.xlsx] – refer to another file
  • tab1 – refer to this tab in that file
  • !$A;$A – all columns in column A. The $ means to always use column A even if I copy paste the formula into multiple rows.

Iteration 2 – try to use a formula based on a String

=SUM(INDIRECT(“[file1.xlsx]tab1!$A:$A”))

  • INDIRECT – the indirect function lets you pass text to refer to an Excel reference

Iteration 3 – append strings to build the expression

=SUM(INDIRECT(“[file1.xlsx]tab”& A3 &”!$A:$A”))

  • & – concatenate strings

fixing JForum XSS error in PM module with quotes

Posted on by
3

A member reported a XSS vulnerability in stock JForum 2.1.9. We confirmed it was a vulnerability/exposure on CodeRanch as well and fixed our fork. Luckily, it was an easy fix unlike the CSRF problems last year.

In addition to saying how to fix the issue in this post, I’m going to outline some of the other techniques JForum uses to defeat XSS.  For the actual (two character) fix, scroll down to “the fix.”

What is XSS?

XSS (Cross site scripting) is a security attack. OWASP describes in well on their XSS page. In brief, XSS injects code into a web page that runs on the target computer. The injected script code can do anything that the web page can do. Which means it can use JavaScript to steal your cookies, mount other attacks, etc. Scary stuff!

  • Reflected XSS – A reflected XSS attack targets specific users but is not stored in the database of the server with the issue. You might see a reflected XSS attack if you click on a link that takes you to the page. Others going to the page normally wouldn’t see the issue.
  • Persistent XSS – A persistent XSS attack gets the attack code stored in the database of the server with the issue. It could still target a specific user (in the case of the private message issue reported here.) Or it could target all users – even non logged in users – if the same attack was made in a post instead of a private message.  I was able to reproduce this problem in posts as well.

Both types of XSS attack are bad and up to the website to prevent. So how does Jforum 2.1.X protect against XSS attacks?

Approach #1 – Use Freemarker HTML escape sequence

JForum uses Freemarker as the view technology. Freemarker allows you to specify that all HTML should be escaped. This means attacks that reply on outputting HTML characters like < (tags) or ” (attributes) will be prevented.  Instead the raw characters of &lt; and &quot; will be output instead. Which the browser will not run. As an example of this technique, the code writes:


${post.subject?default("")?html}

Approach #2 – Escape characters in Java

Approach #1 is very powerful, but it has a limitation. Forum posts typically contain HTML code. For example, you write code in a special format, bold posts, etc. JForum uses Java code to do a search and replace on the special characters in text before adding the HTML formatting. Since the Freemarker view has to be able to render the HTML formatting, it can’t use approach #1. See an example of just one of these transformations:

ViewCommon.replaceAll(text, "<", "&lt;");

This approach is not foolproof because it relies on a blacklist of “not allowed” characters and hackers are creative. But it is really hard to come up with a whitelist of allowed characters in forum posts. And worse, the characters used in attacks are ones that are used in normal writing.

Approach #3 – Limit raw HTML

While JForum does allow HTML in posts, it only allows a limited set of tags and attributes. This one does use a whitelist with code like:


private static Set welcomeTags;

private static Set welcomeAttributes;

Approach #4 – Use BB code instead of HTML

The forum also allows use of BB (bulletin board) codes. This lets you write [b] instead of <b>. If the user isn’t entering HTML, the chance of a problem is lower.

The actual problem here

The XSS vulnerability reported was caused by the interaction between approach #2 and approach #4.

Approach #2 guarantees the quotes are safe with


ViewCommon.replaceAll(tmp, "\"", "&quot;");

Approach #4 contains the following BB mapping code in bb_config.xml


<!-- COLOR -->

<match name="color" removeQuotes="true">

<regex>(?s)(?i)\[color=['"]?(.*?[^'"])['"]?\](.*?)\[/color\]</regex>

<replace>

<![CDATA[

<font color='$1'>$2</font>

]]>

</replace>

</match>

This is a problem because the replace uses single quotes instead of double quotes. The system doesn’t escape single quotes. Allowing all manners of code to be injected in the color attribute.

The fix

Luckily, there is an easy fix. Just change this one line of code in bb_config.xml to:


<font color="$1">$2</font>

I’ve tested and this does in fact solve the problem.

For more learning about XSS

If you want to learn more about XSS, I recommend reading the OWASP cheat sheet.  In particular, notice that you need to escape the code differently depending on whether you are looking at HTML or JavaScript injection. In our case, it was HTML injection because the injection was occurring as a textual HTML attribute. If it was in <script> tag or JavaScript event handler, we’d need to call a JavaScript encoding library. Also, you can learn about DOM based XSS attacks.

using spring RestTemplate and 2 factor authenication to add issues to github

Posted on by
2

Last month, I figured out how to use Spring’s RestTemplate to add issues to github programmatically figuring it would save me time in the future.  Well, the future is here.  I needed to add 16 issues (one per milestone.)  I run my program and get a 401.  In particular, I got

Apr 20, 2014 6:42:18 PM org.springframework.web.client.RestTemplate handleResponseError

WARNING: GET request for "https://api.github.com/repos/boyarsky/repoName/issues" resulted in 401 (Unauthorized); invoking error handler

Exception in thread "main" org.springframework.web.client.HttpClientErrorException: 401 Unauthorized

at org.springframework.web.client.DefaultResponseErrorHandler.handleError(DefaultResponseErrorHandler.java:91)

at org.springframework.web.client.RestTemplate.handleResponseError(RestTemplate.java:588)

at org.springframework.web.client.RestTemplate.doExecute(RestTemplate.java:546)

at org.springframework.web.client.RestTemplate.execute(RestTemplate.java:502)

at org.springframework.web.client.RestTemplate.exchange(RestTemplate.java:444)

at jb.Trial2.main(Trial2.java:29)

Right.  I’ve changed my password AND enabled two factor authentication on github in the past month.  Luckily, it was easy to switch my program to two factor.  Here’s what I did.

Create personal use token

I created a second personal use token just like I did for for my git commmand line use by going to the applications page and clicking “generate new token”.  I chose to create a separate token so I could revoke access as soon as I’m done running the web service.

Testing the token

Per the getting started with oauth doc, I ran a command line to test the token:

curl -i -H 'Authorization: token notMy40CharToken' \

https://api.github.com/user

 Wrote another trial program

I adapted my second trial program from the initial iterations to test out two factor.

package jb;

import org.springframework.http.*;
import org.springframework.web.client.*;

/**
 * Call a web service that uses authenticated user to test passing credentials
 *
 * @author jeanne
 *
 */
public class Trial3 {

	public static void main(String[] args) {
		String url = "https://api.github.com/repos/boyarsky/oca-ocp-book/issues";

		HttpHeaders headers = new HttpHeaders();

		String personalToken = "notMy40CharToken";
		headers.add("Authorization", "token " + personalToken);

		// ignore result because just testing connectivity
		HttpEntity<String> request = new HttpEntity<String>(headers);
		RestTemplate template = new RestTemplate();
		template.exchange(url, HttpMethod.GET, request, Object[].class);
		System.out.println("success");
	}

}

I then replaced the authentication part of my real program and it worked like a charm.  Even with the changes to the program, it was faster than creating 16 issues by hand with the proper text/assignee/milestone.

Delete the personal token

I deleted the token to ensure it never gets used again.  I don’t want to run the program by accident with my credentials.  Or accidentally post the token here.