Exploring Some Prevalent Stored Procedure Myths

Aug 5, 2022 by Robert Gravelle

Application developers have long held the belief that housing database operations within stored procedured yielded optimum performance and guarded against SQL Injection attacks. It was also thought that these advantages were worth the extra costs associated with maintenance, testing, and migration of database logic to a different vendor. In recent years, the tide has been turning away from stored procedures – or procs – towards Object-relational Mappers (ORM) such as Hibernate or Entity Framework as developers have begin to question these long-standing assumptions.

The Are Stored Procedures an Outdated Tool? article highlighted a few reasons for eschewing stored procedures in favor of application code and ORMs. This week, we’ll explore the two myths introduced above and see if they still stand up to scrutiny today.

In the early days of the Internet, it was common practice to minimize network traffic in order to boost performance. Stored procedures helped reduce network traffic by requiring only the proc name and parameters to be transferred over to the server rather than the full SQL statement. Considering the complexity and length of some production queries, these gains could sometimes be substantial. Today, whatever gains you may garner from this approach are easily offset by the fact that you are all too likely to end up calling the same procedure two or three times with the same parameters in the same request. Meanwhile, an ORM would look in its Identity Map and recognize that it already retrieved that result set, so there’s no need to do another round trip. Moreover, it should be noted that the claim that stored procedures are cached on the server, whereas ad-hoc SQL is not, is a myth that was busted by Frans Bouma in his blog post, Stored Procedures are bad, m’kay?.

It has often been said that stored procedures offer natural protection against SQL injection because they separate data from instructions. This is true, as long as the developer doesn’t use dynamic SQL within the stored procedure where a raw string is passed via the input parameter that replaces the placeholder. Here’s a badly written proc that shows exactly how it could open up the database to SQL injection:

create procedure GetStudents(@School nvarchar(50))
as
begin
    declare @sql nvarchar(100)
    set @sql = 'SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ' + @School
    exec @sql
end

You can write SQL that eliminates SQL injection vulnerabilities by using parameterized queries. Written in a programmatic language such as python, TypeScript, or Java, a prepared statement like the one below can sanitize user input so that it’s safe to use in your queries:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo%");
ResultSet rs = prepStmt.executeQuery();

The lesson here is that protection against SQL injection is not a benefit of stored procedures themselves, but rather the convention of not concatenating SQL strings together.

This blog explored a couple of long-held assumptions about stored procedures that don’t quite hold true today. While not by themselves sufficient reason to hop off the stored procedure bandwagon, they do strongly suggest that it may be time to reevaluate your application architecture.