Avoid SQL Injection in JDBC

Let’s say I’m trying to insert some information into a mySQL table using JDBC.

In mySQL, the type varchar(225) is equivalent to the type String in Java.

Our example table will be of the format:

name varchar(255)
field varchar(255)
university varchar(255)
alive varchar(255)

After I import,

import java.sql.*;

I’ll have to open a connection (I suggest doing this outside of the try-catch).

public class EXAMPLEINSERT {

  public static void insertIntoTable(String jdbcURL, String USER, String PASS) {

    Connection conn = null;
    Statement stmt = null;
    try{
        // Open a connection
        conn = DriverManager.getConnection(jdbcURL, USER, PASS);
      
        // Execute insert
        stmt = conn.createStatement();
        String tableName = "TABLENAME";

        // At this point, I might be tempted to do the following ***
        String insertStatement = String.format("INSERT INTO " + tableName + " VALUES ("%s", "%s", "%s", "%s")", name, field, university, alive);
        stmt.executeUpdate(insertStatement);
        // But this is wrong!
    }
    conn.close();
    } catch(Exception e) {
        System.err.println(e.getMessage());
    }
  }
} 

But will this stand up to an attack? What if I set

String university = "university'); DROP TABLE TABLENAME;--";

(The ;– tells the table that everything after “;” is not part of the query.)

Then my entire table will be deleted! We need to sanitize our inputs.

To avoid SQL injection, (such as the Bobby Tables post from xkcd), we must prepare the statement before execution:

public class EXAMPLEINSERT {

  public static void insertIntoTable(String jdbcURL, String USER, String PASS) {

    Connection conn = null;
    Statement stmt = null;
    // add Prepared statement outside of try-catch
    PreparedStatement prepareStatement = null;
    try{
        // Open a connection
        conn = DriverManager.getConnection(jdbcURL, USER, PASS);
      
        // Execute insert
        stmt = conn.createStatement();
        String tableName = "TABLENAME";

        // Avoid temptation and deliver yourself from the evils of coding Java as if it were Python.
        String template = "INSERT INTO " + tableName + " (name, field, university, alive) VALUES (?, ?, ?, ?)";

        statement = conn.prepareStatement(template);
        statement.setString(1, name);
        statement.setString(2, field);
        statement.setString(3, university);
        statement.setBoolean(4, alive);

        statement.executeUpdate();
        //More hardcoding? Slightly, but also more robust.
    }
    conn.close();
    } catch(Exception e) {
        System.err.println(e.getMessage());
    }
  }
}

CodingBat Java Solutions (Assorted)

I’m brushing up on my Java lately using a wonderful code practice site called CodingBat. Below are a few of the solutions I’ve found to their practice problems.

Given a string, return a string where for every char in the original, there are two chars.

public String doubleChar(String str) {
    String doub = “”;
    for(int i = 0; i<str.length(); i++) {
        doub = doub + str.charAt(i) + str.charAt(i);
    }
    return doub;
}

Return an array that contains exactly the same numbers as the given array, but rearranged so that every 3 is immediately followed by a 4. Do not move the 3′s, but every other number may move. The array contains the same number of 3′s and 4′s, every 3 has a number after it that is not a 3 or 4, and a 3 appears in the array before any 4. 

public int[] fix34(int[] nums) {
    int counter =0;
    for(int i =0; i<nums.length; i++) {
        if (nums[i] == 3) {
            for(int j =counter; j<nums.length; j++) {
                if (nums[j] == 4) {
                    int t = nums[i+1];
                    nums[i+1] = nums[j];
                    nums[j] = t;
                    counter = j;
                }
            }
        }
    }
    return nums;
}

Given n>=0, create an array with the pattern {1,    1, 2,    1, 2, 3,   … 1, 2, 3 .. n} (spaces added to show the grouping). Note that the length of the array will be 1 + 2 + 3 … + n, which is known to sum to exactly n*(n + 1)/2.

public int[] seriesUp(int n) {
    int[] array = new int[n];
    int[] array1 = new int[n*(n+1)/2];
    for (int i = 0; i<n;i++) {
        array[i] = i+1;
    }
    for (int j = 0; j <n; j++) {
        array1[j*(j+1)/2+j] = array[j];
        for (int x = 0; x < j+1; x++) {
            array1[j*(j+1)/2+j-x] = array[j-x];
        }
    }
    return array1;
}

CodingBat Java (Assorted Warmup-1) Solutions

For warmups, CodingBat provides solutions. Some of my solutions differ from the provided.

The parameter weekday is true if it is a weekday, and the parameter vacation is true if we are on vacation. We sleep in if it is not a weekday or we’re on vacation. Return true if we sleep in.

public boolean sleepIn(boolean weekday, boolean vacation) {
    return (!weekday || vacation);
}

We have two monkeys, a and b, and the parameters aSmile and bSmile indicate if each is smiling. We are in trouble if they are both smiling or if neither of them is smiling. Return true if we are in trouble. 

public boolean monkeyTrouble(boolean aSmile, boolean bSmile) {
    return ((aSmile && bSmile) || (!aSmile && !bSmile)); }

Given two int values, return their sum. Unless the two values are the same, then return double their sum.

public int sumDouble(int a, int b) {
    if(a == b) {
        return 2*(a+b);
    }
    return a+b;
}

Given an int n, return the absolute difference between n and 21, except return double the absolute difference if n is over 21.

public int diff21(int n) {
    if (n <= 21) {
        return 21-n;
    }
    else {
        return (n-21)*2;
    }
}

We have a loud talking parrot. The “hour” parameter is the current hour time in the range 0..23. We are in trouble if the parrot is talking and the hour is before 7 or after 20. Return true if we are in trouble.

public boolean parrotTrouble(boolean talking, int hour) {
    return (talking && (hour < 7 || hour > 20));
}

Given 2 ints, a and b, return true if one if them is 10 or if their sum is 10.

public boolean makes10(int a, int b) {
    return (a == 10 || b == 10 || a + b == 10);
}

Given an int n, return true if it is within 10 of 100 or 200. Note: Math.abs(num) computes the absolute value of a number.

public boolean nearHundred(int n) {
    return (Math.abs(100 - n) <= 10 || Math.abs(200 - n) <= 10);
}

Given 2 int values, return true if one is negative and one is positive. Except if the parameter “negative” is true, then return true only if both are negative.

public boolean posNeg(int a, int b, boolean negative) {
    if (!negative) {
        return ((a < 0 && b > 0) || (a > 0 && b <0));
    }
    return (a<0 && b<0);
}

Given a string, return a new string where “not ” has been added to the front. However, if the string already begins with “not”, return the string unchanged. Note: use .equals() to compare 2 strings.

public String notString(String str) {
    if (str.length() >= 3 && str.substring(0,3).equals(“not”)) {
        return str;
    }
    return “not “+str;
}

Given a non-empty string and an int n, return a new string where the char at index n has been removed. The value of n will be a valid index of a char in the original string (i.e. n will be in the range 0..str.length()-1 inclusive).

public String missingChar(String str, int n) {
    return (str.substring(0,n)+str.substring(n+1,str.length()));
}

Given a string, return a new string where the first and last chars have been exchanged.

public String frontBack(String str) {
    if (str.length()>=2) {
        return (str.substring(str.length()-1,str.length())+str.substring(1,str.length()-1)+str.substring(0,1));
    }
    return str;
}

or

public String frontBack(String str) {
    if (str.length()>=2) {
        return (str.charAt(str.length()-1)+str.substring(1,str.length()-1)+str.charAt(0));
    }
    return str;
}

Given 2 int values, return true if they are both in the range 30..40 inclusive, or they are both in the range 40..50 inclusive.

public boolean in3050(int a, int b) {
    return ((30 <= a && a <= 40) && (30 <= b && b <= 40)) || ((40 <= a && a <= 50) && (40 <= b && b <= 50));
}

Given a non-empty string and an int N, return the string made starting with char 0, and then every Nth char of the string. So if N is 3, use char 0, 3, 6, … and so on. N is 1 or more.

public String everyNth(String str, int n) {
    String x = “”;
    for(int i =0; i<str.length(); i=i+n) {
        x = x+str.charAt(i);
    }
    return x;
}

Given a string, return a new string where the last 3 chars are now in upper case. If the string has less than 3 chars, uppercase whatever is there. Note that str.toUpperCase() returns the uppercase version of a string.

public String endUp(String str) {
    if (str.length()<=3) {
        return str.toUpperCase();
    }
    return (str.substring(0, str.length()-3) + (str.substring(str.length()-3, str.length())).toUpperCase());
}