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:

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]:

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:

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’

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. Please, comment or contact me if you have any issue or new ideas about this post.

Also you can find me at Upwork

See you soon with more development notes…