Invariant Properties

  • rss
  • Home

Database and Webapp Security, part 3: SQL Injection in Stored Procedures

Bear Giles | December 15, 2011

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.

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
  3. CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
  4.                                  IN password VARCHAR(20),
  5.                                  OUT success INT)
  6. BEGIN
  7.   SET @query = CONCAT('SELECT COUNT(credentials.username) INTO @succ
  8.     FROM credentials
  9.     WHERE credentials.username = \'', username,
  10.         '\' AND credentials.password = \'', password, '\'');
  11.    PREPARE stmt FROM @query;
  12.    EXECUTE stmt;
  13.    SELECT @succ;
  14.    SET success = @succ;
  15. END;
  16. $$
  17. DELIMITER ;
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.

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
  3. CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
  4.                                  IN password VARCHAR(20),
  5.                                  OUT success INT)
  6. BEGIN
  7.   SET @query = 'SELECT COUNT(credentials.username) INTO @succ
  8.     FROM credentials
  9.     WHERE credentials.username = ? AND credentials.password = ?';
  10.    PREPARE stmt FROM @query;
  11.    SET @usernm = username;
  12.    SET @pass = password;
  13.    EXECUTE stmt USING @usernm, @pass;
  14.    SELECT @succ;
  15.    SET success = @succ;
  16. END;
  17. $$
  18. DELIMITER ;
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:

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
  3. CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(20),
  4.                                  IN password VARCHAR(20),
  5.                                  OUT success INT)
  6. BEGIN
  7.   SET @query = CONCAT('SELECT COUNT(credentials.username) INTO @succ
  8.     FROM credentials
  9.     WHERE credentials.username = ', quote_literal(username),
  10.         'AND credentials.password = ', quote_literal(password));
  11.    PREPARE stmt FROM @query;
  12.    EXECUTE stmt;
  13.    SELECT @succ;
  14.    SET success = @succ;
  15. END;
  16. $$
  17. DELIMITER ;
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.

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS SP_AUTHENTICATE$$
  3. CREATE PROCEDURE SP_AUTHENTICATE(IN username VARCHAR(8),
  4.                                  IN password VARCHAR(20),
  5.                                  OUT success INT)
  6. BEGIN
  7.   SELECT COUNT(credentials.username) INTO success
  8.      FROM credentials
  9.      WHERE credentials.username = username AND credentials.password = password;
  10. END;
  11. $$
  12. DELIMITER ;
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

Categories
java, linux, security
Comments rss
Comments rss
Trackback
Trackback

« Database and Webapp Security, part 2: SQL Injection Database and Webapp Security, part 4: Schema Ownership »

Leave a Reply

Click here to cancel reply.

You must be logged in to post a comment.

Archives

  • May 2020 (1)
  • March 2019 (1)
  • August 2018 (1)
  • May 2018 (1)
  • February 2018 (1)
  • November 2017 (4)
  • January 2017 (3)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (3)
  • January 2016 (6)
  • December 2015 (2)
  • November 2015 (3)
  • October 2015 (2)
  • August 2015 (4)
  • July 2015 (2)
  • June 2015 (2)
  • January 2015 (1)
  • December 2014 (6)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (3)
  • January 2014 (6)
  • December 2013 (13)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (2)
  • August 2013 (5)
  • June 2013 (1)
  • May 2013 (2)
  • March 2013 (1)
  • November 2012 (1)
  • October 2012 (3)
  • September 2012 (2)
  • May 2012 (6)
  • January 2012 (2)
  • December 2011 (12)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (6)
  • March 2011 (4)
  • February 2011 (3)
  • October 2010 (6)
  • September 2010 (8)

Recent Posts

  • 8-bit Breadboard Computer: Good Encapsulation!
  • Where are all the posts?
  • Better Ad Blocking Through Pi-Hole and Local Caching
  • The difference between APIs and SPIs
  • Hadoop: User Impersonation with Kerberos Authentication

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Pages

  • About Me
  • Notebook: Common XML Tasks
  • Notebook: Database/Webapp Security
  • Notebook: Development Tips

Syndication

Java Code Geeks

Know Your Rights

Support Bloggers' Rights
Demand Your dotRIGHTS

Security

  • Dark Reading
  • Krebs On Security Krebs On Security
  • Naked Security Naked Security
  • Schneier on Security Schneier on Security
  • TaoSecurity TaoSecurity

Politics

  • ACLU ACLU
  • EFF EFF

News

  • Ars technica Ars technica
  • Kevin Drum at Mother Jones Kevin Drum at Mother Jones
  • Raw Story Raw Story
  • Tech Dirt Tech Dirt
  • Vice Vice

Spam Blocked

53,793 spam blocked by Akismet
rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox