How to Fix SQL Injection Using Java PreparedStatement parameterized queries?

What is SQL injection?

SQL injection happens when you inject some content into a SQL query string, and the result modifies the syntax of your query in ways you didn't intend.

Injected SQL commands can alter SQL statement and compromise the security of an application.

SQL Injection Based on 1=1 is Always True

SELECT * FROM Users WHERE UserId = 105

Injection using “1=1”

SELECT * FROM Users WHERE UserId = 105 or 1=1;

SQL Injection Based on ""="" is Always True

SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"

Injection using “=”

SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

Vulnerable Usage

Example #1

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

This code is vulnerable to SQL Injection because it uses dynamic queries to concatenate malicious data to the query itself.

Example #2

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

PreparedStatement stmt = connection.prepareStatement(query);

ResultSet rs = stmt.executeQuery();

This code is also vulnerable to SQL Injection. Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.

Fix SQL injection using PreparedStatement

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times without having to recompile for every execution.

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");

stmt.setString(1, userid);

stmt.setString(2, password);

ResultSet rs = stmt.executeQuery();

This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. By utilizing Java's PreparedStatement class, bind variables (i.e. the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.

Advantage of PreparedStatement?

Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

Program to generate unique CAPTCHA to verify an user

A CAPTCHA (Completely Automated Public Turing test to tell Computers and Humans Apart) is a test to determine whether the user is human or not.

CAPTCHA provides a way to block bots from interacting with your site by providing something that's hard for them to read, but easy for people to read.

import java.util.Random;

public class GenerateCaptcha {

      /**

       * Generate Length between 5 and 8.

       * @return return length.

       */

      private static int generateRandomLength() {

            int length = 5 + Math.abs((random.nextInt()%4));

            return length;

      }

      /**

       *  Generate a CAPTCHA String consisting of random

       *  lower case & upper case letters, and numbers.

       */

      private static String generateCaptchaString(int length) {

           

            StringBuffer captchaBuffer = new StringBuffer();

           

            for (int i = 0; i < length; i++) {

                 

                  /** Generate the Random Character between 0 to 61.

                   * NOTE: Take abs value, because there may

                   * be ArrayIndexOutOfBount

                   * Exception for negative number*/

                  int rndCharIdx = Math.abs(random.nextInt()) % 62;

                 

                  char character = characters[rndCharIdx];

                 

                  captchaBuffer.append(character);

            }

            return captchaBuffer.toString();

      }

      private static Character[] characters = {'a','b','c','d','e','f',

            'g','h','i','j','k','l','m','n','o','p','q','r','s','t','u',

            'v','w','x','y','z','A','B','C','D','E','F','G','H','I','J',

            'K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y',

            'Z','0','1','2','3','4','5','6','7','8','9'};

      private static Random random= new Random();

      public static void main(String[] args) {

            int rndmCaptchaLen = generateRandomLength();

            String captcha = generateCaptchaString(rndmCaptchaLen);

            System.out.println("Random Captcha #"+captcha);

      }

}

Hash-based message authentication code

In cryptography, a keyed-hash message authentication code (HMAC) is a specific construction for calculating a message authentication code (MAC) involving a cryptographic hash function in combination with a secret cryptographic key.

As with any MAC, it may be used to simultaneously verify both the data integrity and the authentication of a message.

Any cryptographic hash function, such as MD5 or SHA-1, may be used in the calculation of an HMAC; the resulting MAC algorithm is termed HMAC-MD5 or HMAC-SHA1 accordingly.

The cryptographic strength of the HMAC depends upon the cryptographic strength of the underlying hash function, the size of its hash output, and on the size and quality of the key.

An iterative hash function breaks up a message into blocks of a fixed size and iterates over them with a compression function.

For example, MD5 and SHA-1 operate on 512-bit blocks. The size of the output of HMAC is the same as that of the underlying hash function (128 or 160 bits in the case of MD5 or SHA-1, respectively), although it can be truncated if desired.

HMAC-SHA1 and HMAC-MD5 are used within the IPsec and TLS protocols.

Signature generating algorithm on Client and Server side

import java.security.InvalidKeyException;

import java.security.NoSuchAlgorithmException;

import javax.crypto.Mac;

import javax.crypto.spec.SecretKeySpec;

import org.apache.commons.codec.binary.Base64;

import org.apache.log4j.Logger;

import com.francetelecom.csrtool.model.logging.FuncLogging;

import com.francetelecom.csrtool.utils.CSRToolUtil;

/**

 * Utility class to generate HMAC message digest based on key and (SHA256/SHA512) algorithm 

 */

public class HMACGenerator {

        /** HMAC256 **/

        public static final String HMAC256 = "HmacSHA256";

        /** HMAC512 **/

        public static final String HMAC512 = "HmacSHA512";

        /** LOGGER Constant */

        private static final Logger LOGGER = Logger.getLogger(HMACGenerator.class);

        /**

         * Generates a encrypted message digest based on HMACSHA256 or HMACSHA512 algorithm on the data                                           and specific key

         * @param base64Key - base64 private key

         * @param data refers to the data need to be encrypted

         * @param algorithm refers to the message digest algorithm been used (256 -> HMACSHA256) and (512                                                                   -> HMACSHA512)

         * @return HMAC encrypted String

         */

        public static String generateHMACUsingBase64Key(String base64Key, String data) {

                if(Util.isStringNullOrEmpty(base64Key) || Util.isStringNullOrEmpty(data)) {

                        return null;

                }

                // Decode Base64 key (string) to bytes

                byte[] secretkeyByte = Base64.decodeBase64(base64Key.getBytes());

                // Generate secret key specific to algorithm

                SecretKeySpec signingKey = new SecretKeySpec(secretkeyByte, HMAC256);

                String base64Digest = null;

                byte[] dataBytes = data.getBytes();

                try {

                        // Load and initialize algorithm using signing key

                        Mac mac = Mac.getInstance(HMAC256);

                        mac.init(signingKey);

                        // Generate the HMAC using input data bytes

                        byte[] rawHmac = mac.doFinal(dataBytes);

                        // Convert raw HMAC into Base64 HMAC

                        byte[] hash = Base64.encodeBase64(rawHmac);

                        base64Digest = new String(hash);

                } catch (NoSuchAlgorithmException e) {

                } catch (InvalidKeyException e) {

                }

               

                return base64Digest;

        }

}

https://en.wikipedia.org/wiki/Hash-based_message_authentication_code