Use JAVA SQL Parameters and improve your application security

Use parameters in a JAVA SQL Prepared Statement is a good practice for performance reasons but also is good way to improve the security of your application.

Advertisements

Recently I had to do some maintenance tasks in a legacy system. You know, work with code that you didn’t write can be a very interesting experience… or a nightmare…
In this case, I found several issues about code structure, performance and security. But the most important (I think) were related with the queries used by the application: they didn’t use any SQL parameter to perform the select, insert, update and delete operations.

After finish that work I thought “maybe not everybody knows about this, maybe could be a good idea write a sample code”… So, here is a little example about how to use parameters in a JAVA SQL Prepared Statement. Remember: use parameters is a good way, not only to improve the performance (because the statements “are” in the DB cache), also to improve the security of your application.

Of couse, if you know a better solution for one or more cases or you want to share your best practices with us, please, let me know and I will update this post 🙂

Some Notes about Secutiry

  • use POST restful end-points whenever you can
  • return only one object instead a list of objects (for example if you expect the user’s details from a service return an user object instead a list of user objects)
  • validate the input in each method and, if you can, create a common validator class for that
  • check the size the response: if you expect one and only one user’s record, check it before send the response to the client
  • query for the columns of the table instead use *
  • avoid return extra data: if you need user’s name and birth date, only return that
  • avoid hardcode values in your statements: always user SQL parameters
  • avoid store decrypted password in files, tables, etc.
  • avoid return detailed errors to the client: messages with the table name, complete query or expected values for the where clause

Imagine that you have a table with this structure and data:
2017-06-25-Captura-001
And want to create and Restful end-point to get the user’s details.
Also, imagine that you get the data from table without parameters…

Example of Controller Class

package com.example.sample.db.securestmt.controller;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.example.sample.db.securestmt.dto.ExampleDTO;
import com.example.sample.db.securestmt.service.ExampleService;

/**
 * Handles the request for the example. 
 * @author Gabriel
 *
 */
@RestController
public class ExampleController {

	private static final Logger LOGGER = LoggerFactory.getLogger(ExampleController.class);
	
	@Autowired
	ExampleService exampleService;
	
	/**
	 * Gets all configured names.
	 * @return
	 */
	@RequestMapping(path="/user/info", produces= MediaType.APPLICATION_JSON_VALUE)
	public ResponseEntity<?> getUserInfo(@RequestParam final String user) {
		LOGGER.info("getUserInfo - start");
		
		ResponseEntity<?> responseEntity = null;
		
		try{
			List<ExampleDTO> users = exampleService.getUserInfo(user);
			responseEntity = ResponseEntity.ok(users);
		} catch (Exception e) {
			responseEntity = ResponseEntity.badRequest().body("ERROR : " + e.getMessage());
		}
		
		LOGGER.info("getUserInfo - end");
		return responseEntity;		
	}	
}

Example of Service Class

package com.example.sample.db.securestmt.service;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.example.sample.db.securestmt.dao.ExampleDAO;
import com.example.sample.db.securestmt.dto.ExampleDTO;

/**
 * Services for the example.
 * @author Gabriel
 *
 */
@Service
public class ExampleService {
	
	@Autowired
	@Qualifier("NonSecureDAO")
	ExampleDAO exampleDAO;
	
	/**
	 * Gets all configured names.
	 * @return
	 * @throws SQLException
	 */
	public List<ExampleDTO> getUserInfo(final String user) throws SQLException {
		return exampleDAO.findUserInfoByUser(user);
	}
}

Example of Unsecured DAO Class

package com.example.sample.db.securestmt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.example.sample.db.securestmt.dto.ExampleDTO;

@Repository("NonSecureDAO")
public class ExampleNonSecureDAOImpl implements ExampleDAO {

	private static final Logger LOGGER = LoggerFactory.getLogger(ExampleNonSecureDAOImpl.class);
	
	final static String QUERY_FIND_ALL_UNSECURE = " SELECT * FROM USER_TEST WHERE USER = ";
	
	@Autowired
	DataSource dataSource;
	
	public List<ExampleDTO> findUserInfoByUser(final String user) throws SQLException {
		List<ExampleDTO> records = new ArrayList<>();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(QUERY_FIND_ALL_UNSECURE + "'"+ user +"'");
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				ExampleDTO dto = new ExampleDTO();
				dto.setId(resultSet.getString(1));
				dto.setName(resultSet.getString(2));
				dto.setUser(resultSet.getString(3));
				dto.setPassword(resultSet.getString(4));
				
				records.add(dto);
			}
			
			return records;
		} catch (SQLException sqle) {
			LOGGER.error("Error performing query: " + QUERY_FIND_ALL_UNSECURE, sqle);
			throw sqle;
		} finally {
			closeSQLObject(resultSet, ResultSet.class.getName());
			closeSQLObject(preparedStatement, PreparedStatement.class.getName());
			closeSQLObject(connection, Connection.class.getName());
		}
	}
	
	/**
	 * Closes the given SQL AutoCloseable object.
	 * @param sqlObject
	 * @param sqlObjectType
	 */
	private void closeSQLObject(final AutoCloseable sqlObject, final String sqlObjectType) {
		try {
			sqlObject.close();
		} catch (Exception e) {
			LOGGER.warn("Exception closing : {}", sqlObjectType);
		}
	}
}

In the “happy path” the application or an user should get the user’s details setting the user parameter as user=[User Value]:
2017-06-25-Captura-002

But if someone tries to check if you have a security hole? Maybe could do something like user=’AND (or another non valid SQL syntax) to get an exception:
2017-06-25-Captura-003

And if you or team return detailed exceptions to the client-side, maybe someone could do something worse… like set add an OR clause with a true condition to get the details from all user in the table: user=’ OR ‘ABC’=’ABC’
2017-06-25-Captura-004

Use parameters in your SQL statements can solve this kind of issues quickly:

Example of Secured DAO Class

package com.example.sample.db.securestmt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.example.sample.db.securestmt.dto.ExampleDTO;

@Repository("SecureDAO")
public class ExampleSecureDAOImpl implements ExampleDAO {

	private static final Logger LOGGER = LoggerFactory.getLogger(ExampleSecureDAOImpl.class);
	
	final static String QUERY_FIND_ALL_SECURE = " SELECT ID, NAME, USER, PASSWORD FROM USER_TEST WHERE USER = ?";
	
	@Autowired
	DataSource dataSource;
	
	public List<ExampleDTO> findUserInfoByUser(final String user) throws SQLException {
		List<ExampleDTO> records = new ArrayList<>();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(QUERY_FIND_ALL_SECURE);
			preparedStatement.setString(1, user);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				ExampleDTO dto = new ExampleDTO();
				dto.setId(resultSet.getString(1));
				dto.setName(resultSet.getString(2));
				dto.setUser(resultSet.getString(3));
				dto.setPassword(resultSet.getString(4));
				
				records.add(dto);
			}
			
			return records;
		} catch (SQLException sqle) {
			LOGGER.error("Error performing query. Please contact the User Support Service", sqle);
			throw sqle;
		} finally {
			closeSQLObject(resultSet, ResultSet.class.getName());
			closeSQLObject(preparedStatement, PreparedStatement.class.getName());
			closeSQLObject(connection, Connection.class.getName());
		}
	}
	
	/**
	 * Closes the given SQL AutoCloseable object.
	 * @param sqlObject
	 * @param sqlObjectType
	 */
	private void closeSQLObject(final AutoCloseable sqlObject, final String sqlObjectType) {
		try {
			sqlObject.close();
		} catch (Exception e) {
			LOGGER.warn("Exception closing : {}", sqlObjectType);
		}
	}
}

Final notes
Remember: use parameters in your SQL statements can avoid security issues and also improve the performance of your application… and are easier to develop and maintain that the “hardcoded” where conditions! 🙂

You can get the complete code from my public GitHub repository:
https://github.com/Gabotto/SampleDBSecureStatements

Let me know if you have any problem, comment or new ideas:
WordPress: https://gabelopment.wordpress.com/
Email: gabelopment@gmail.com

Also you can find me at Upwork

See you soon with more development notes…