Invariant Properties

What are stored procedures and CallableStatements?

Stored procedures are bits of code kept in the database. The most common form is a SQL-like scripting language but additional languages are supported – PERL, tcl, ruby, java, etc.

It is important to remember that stored procedures are used in database triggers – you should be aware of them even if you all of your work with hibernate.

Wrong Approach

The wrong approach is to create a dynamic SQL query without sanitization.

DELIMITER $$
DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
                                 IN password VARCHAR(20),
                                 OUT success INT)
BEGIN
  SET @query = CONCAT('SELECT COUNT(credentials.username) INTO @succ
     FROM credentials
     WHERE credentials.username = \'', username,
        '\' AND credentials.password = \'', password, '\'');
   PREPARE stmt FROM @query;
   EXECUTE stmt;
   SELECT @succ;
   SET success = @succ;
END;
$$
DELIMITER ;

(Note: this code fragment is an example comes from the reference below.)

This stored procedure has no benefits over the “wrong answer” in the part 2 with the exception of very modest encapsulation..

Sidenote: This is an example of an oracle. It returns the minimum amount of information about user authentication – a “thumbs up” or “thumbs down”. There’s no information leak in this implementation since the caller already knows the username and password but a more robust implementation could also verify that the user account has not been disabled, etc.

Stored Procedures and Parameterization

The first safe approach is executing the SQL directly instead of creating dynamic SQL.

The second safe approach is parameterization within the stored procedure. This is directly equivalent to Java prepared statements and placeholders.

DELIMITER $$
DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
                                 IN password VARCHAR(20),
                                 OUT success INT)
BEGIN
  SET @query = 'SELECT COUNT(credentials.username) INTO @succ
     FROM credentials
     WHERE credentials.username = ? AND credentials.password = ?';
   PREPARE stmt FROM @query;
   SET @usernm = username;
   SET @pass = password;
   EXECUTE stmt USING @usernm, @pass;
   SELECT @succ;
   SET success = @succ;
END;
$$
DELIMITER ;

PLPSQL Sanitization

There is another alternative if you’re willing to be tied to a specific database vendor. In practice this usually isn’t an issue – hibernate gives you some database transparency but stored procedures will always be tied closely to the database.

In plpsql (PostgreSQL) there are two commands that can be used for sanitizing input: quote_ident and quote_literal. There are undoubtably similar commands in other stored procedure languages.

Updating the wrong answer above we have:

DELIMITER $$
DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
                                 IN password VARCHAR(20),
                                 OUT success INT)
BEGIN
  SET @query = CONCAT('SELECT COUNT(credentials.username) INTO @succ
     FROM credentials
     WHERE credentials.username = ', quote_literal(username),
        'AND credentials.password = ', quote_literal(password));
   PREPARE stmt FROM @query;
   EXECUTE stmt;
   SELECT @succ;
   SET success = @succ;
END;
$$
DELIMITER ;

Direct SQL

The final safe approach is to use direct SQL calls with minimum parameter size. This is mentioned on the CERT website but I would hesitate to use it since it would be so easy to introduce unsafe code by accident.

DELIMITER $$
DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(8),
                                 IN password VARCHAR(20),
                                 OUT success INT)
BEGIN
  SELECT COUNT(credentials.username) INTO success
     FROM credentials
     WHERE credentials.username = username AND credentials.password = password;
END;
$$
DELIMITER ;

Cost/Benefit Analysis

Stored procedures are harder to exploit than naked SQL queries but this often gives people a false sense of security. This should be considered mandatory for sensitive information (user authentication, audit logging) and highly recommended in all other cases.

References

https://www.securecoding.cert.org/confluence/pages/viewpage.action?pageId=70288108