A user authentication strategy

exercise No. 49

Q:

Our current application for entering Person records lacks authentication: A user simply connects to the database using credentials being hard coded in a properties file. A programmer suggests to implement authentication based on the following extension of the Person table:

CREATE TABLE Person (
   name char(80) NOT NULL
  ,email CHAR(20) NOT NULL UNIQUE
  ,login CHAR(10)  UNIQUE -- login names must be unique --
  ,password CHAR(20)
);

On clicking Connect a user may enter his login name and password, fred and 12345678 in the following example:

Figure 949. Login credentials for database connection
Login credentials for database connection

Based on these input values the following SQL query is being executed by a java.sql.Statement object:

SELECT * FROM Person WHERE login='fred' and password = '12345678'

Since the login attribute is UNIQUE we are sure to receive either 0 or 1 dataset. Our programmer proposes to grant login if the query returns at least one dataset.

Discuss this implementation sketch with a colleague. Do you think this is a sensible approach? Write down your results.

A:

The approach is essentially unusable due to severe security implications. Since it is based on java.sql.Statement rater than on java.sql.PreparedStatement objects it is vulnerable to SQL injection attacks. A user my enter the following password value in the GUI:

sd' OR '1' = '1

Based on the login name fred the following SQL string is being crafted:

SELECT * FROM Person WHERE login='fred' and password = 'sd' OR '1' = '1';

Since the WHERE clause's last component always evaluates to true, all objects from the Person relation are returned thus permitting login.

The implementation approach suffers from a second deficiency: The passwords are stored in clear text. If an attacker gains access to the Person table he'll immediately retrieve the passwords of all users. This problem can be solved by storing hash values of passwords rather than the clear text values themselves.