Nov 17

SQL Injection involves taking advantage of a vulnerability in your code that allows an attacker to inject SQL commands into your code.

For example take the following code to check a user login.

String sql = "select * from user where username='" + username +"' and password='" + password + "'";
statement = conn.createStatement();
rs = statement.executeQuery(sql);

That codes makes it possible for a user to include SQL in their username (or password) that would change the function of the above code. For example consider what happens if the following username is used

anyone' or '1'='1

The resulting SQL would be:

select * from user where username='anyone' OR '1'='1' and password=' '

This would allow an attacker to potentially log in without supplying a password.

The easiest way to protect against this type of attack is to always use a PreparedStatement. So our code becomes:

String sql = "select * from user where username=? and password=?;
statement = conn.prepareStatement(sql);
statement.setString(1, username);
statement.setString(2, password);
rs = statement.executeQuery();

By using a PreparedStatement any meta-characters in our parameters now get escaped correctly which protects us from SQL injection.

written by objects \\ tags: , , , ,

Oct 11

The DBUtil library from Apache provides a set of class for doing a variety of standard database tasks.

eg. To make a query becomes as simple as the following, providing the result set as a List of arrays where each list elements contains a row.

QueryRunner runner = new QueryRunner();
ArrayListHandler handler = new ArrayListHandler();
List<Object[]> result = runner.query(connection,
    "SELECT * FROM MyTable WHERE name=?", handler, "Joe Smith");

written by objects \\ tags: , , , ,

Sep 24

The Connection class has a setReadOnly() that hints to the database that the connection is read only. This will enable any possible database optimisation.

   Connection connection = DriverManager.getConnection(
        connectionString, properties);

written by objects \\ tags: ,