Book "SQL Performance Explained"

I just read Markus Winand's wonderful book "SQL Performance Explained" which can be bought as an e-book or print edition and even be read online. The books sole topic is B-tree backed database indices.

The preface already states that "Database indexing is a development task." This is because to define an optimal index one must understand how the application queries the data.

→ This means that every developer should read this book. It will show you how to read execution plans and how to create indices for a wide range of scenarios.




Here are some key points, but you really have to read the book:

1) It is better to have a single index for all columns of a where clause than one index for each column.

2) The column order of a concatenated index has great impact on its usability so it must be chosen carefully.

A database can use a concatenated index when searching with the leading (leftmost) columns. An index with three columns can be used when searching for the first column, when searching with the first two columns together, and when searching using all columns.

→ The most important consideration when defining a concatenated index is how to choose the column order so it can support as many SQL queries as possible.

3) If you use a function (TRUNC, UPPER, ...) on a column in the where clause you must also create an index on the function

4) When searching for ranges index for equality first—then for ranges. This will keep the scanned index range as small as possible.

Example:
   WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
   AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')
   AND subsidiary_id  = ?

→ The index should be on SUBSIDIARY_ID and then on DATE_OF_BIRTH

5) Even if you have a full copy of the production database in your development environment, the concurrent background load can still cause a query to run much slower in production.

→ Careful execution plan inspection yields more confidence than superficial benchmarks.

6) An index can be used for an order-by sort. And because databases can read indexes in both directions this can even be in the other direction. It is only important that the scanned index range is in the exact opposite order to the order by clause.

7) And a last one not only for performance but also for security: Parameterized Queries are the best way to prevent SQL injection.

M101J: MongoDB for Java Developers - Cheatsheet

I took the excellent and free online course "M101J: MongoDB for Java Developers" offered by MongoDB Inc. via https://education.mongodb.com/ and created a cheatsheet which can be download: MongoDB-M101J-Cheatsheet.pdf

Enjoy.

Sencha Touch 2, CSRF & Double Submit Cookies to protect stateless REST-Services

To protect my stateless REST services against Cross Site Request Forgery attacks I use Double Submit Cookies. For more information on this see http://appsandsecurity.blogspot.de/2012/01/stateless-csrf-protection.html

On the (Java) server side I wrote a servlet filter which checks that the cookie value and the HTTP header are specified and that they match:

public class CsrfDoubleSubmitCookieFilter implements Filter {
 private static final Logger logger = LoggerFactory.getLogger(CsrfDoubleSubmitCookieFilter.class);
 private static final String ANTI_CSRF_DOUBLE_SUBMIT_COOKIE = "Anti-Csrf-Double-Submit-Cookie";

 @Override
 public void destroy() {
 }

 @Override
 public void init(FilterConfig config) throws ServletException {
 }

 @Override
 public void doFilter(ServletRequest servletRequest, ServletResponse response, FilterChain chain) throws IOException, ServletException {
  HttpServletRequest request = (HttpServletRequest) servletRequest;
  String antiCsrfHeader = request.getHeader(ANTI_CSRF_DOUBLE_SUBMIT_COOKIE);
  String antiCsrfCookie = getCookie(request.getCookies(), ANTI_CSRF_DOUBLE_SUBMIT_COOKIE);

  logger.debug("Checking CSRF cookie & header for: {}{}", request.getServletPath(), request.getPathInfo());
  checkCrsfToken(antiCsrfHeader, antiCsrfCookie);

  chain.doFilter(request, response);
 }

 /**
  * Checks that the antiCsrfHeader and the antiCsrfCookie are not blank and do match
  */
 private void checkCrsfToken(String antiCsrfHeader, String antiCsrfCookie) {
  if (StringUtils.isBlank(antiCsrfHeader) || StringUtils.isBlank(antiCsrfCookie) || !antiCsrfHeader.equals(antiCsrfCookie)) {
   logger.error("POSSIBLE CSRF (Cross-Site-Request-Forgery) ATTACK !!! -> Anti-Csrf-Header {} and Anti-Csrf-Cookie {} do not match",
     antiCsrfHeader, antiCsrfCookie);
   throw new WebSecurityException("");
  }
 }

 private String getCookie(Cookie[] cookies, String name) {
  String retVal = null;
  for (int i = 0; i < cookies.length; i++) {
   if (name.endsWith(cookies[i].getName())) {
    retVal = cookies[i].getValue();
    break;
   }
  }
  return retVal;
 }
}

On the Senche Touch side I added a 'beforerequest' listener function to Ext.Ajax in the launch-method of app.js. This will add a new CSRF token to the Anti-CSRF Cookie and Header for each Ajax request

  launch: function () {
        "use strict";
        // Destroy the #appLoadingIndicator element
        Ext.fly('appLoadingIndicator').destroy();
        //Add double submit anti-CSRF cookie and header to each Ajax-request

        Ext.Ajax.addListener( 'beforerequest', function(conn, options, eOpts) {  
          "use strict";
          var csrfToken = Math.random();
          document.cookie='Anti-Csrf-Double-Submit-Cookie=' + csrfToken + ';path=/';
          options.headers['Anti-Csrf-Double-Submit-Cookie'] =csrfToken;
        }

    },

Note: When you keep state on the server side it is obviously more secure to let the server generate a new CSRF token for each user session and put it in the cookie. The client has to read the cookie value and set it into the header for each request. The server can than compare the CSRF token value from the server side session with the header value provided by the client.