SQL Injection in Stored Procedures

Introduction

In our last lesson, we established that parameterized stored procedures are a robust defense against SQL injection. We saw how they treat user input as harmless data, preventing it from being executed as code. However, we also noted a critical caveat: a stored procedure is only secure if it's written correctly.

This lesson flips the script. We will now move from defensive coding to offensive analysis. Your goal as a security analyst is not just to know how to build secure systems, but also to identify weaknesses in existing ones. Today, you will learn to spot the tell-tale signs of an SQL injection vulnerability within a stored procedure. We'll dissect the insecure use of dynamic SQL, learn the specific code patterns that signal danger, and, most importantly, practice the reconnaissance techniques used to hunt for these flawed procedures in a database.

The Lure of Dynamic SQL

First, let's clarify what "dynamic SQL" is. Dynamic SQL refers to SQL statements that are constructed and executed at runtime as a string. Developers sometimes use this technique when they need to build flexible queries where parts of the query itself (like table or column names) might change based on user input.

While powerful, this flexibility comes at a high price if not handled with extreme care. When user-controlled data is concatenated directly into a query string, the door to SQL injection is thrown wide open.

Let's watch a demonstration of this exact problem. While the initial setup is in application code, the principle of building a query via string concatenation is identical to how it's done insecurely inside a stored procedure.

Prevent sql injection with dynamic sql

This video, "Prevent sql injection with dynamic sql," provides a crystal-clear demonstration of the vulnerability.

First, watch the segment that shows the vulnerable code being built (the code change). Notice how the @SQL variable is constructed by adding strings together, including the raw text from input fields. Next, watch the attack demonstration (the injection). See how a carefully crafted input string ';drop database salesDB -- is concatenated into the query, breaking out of the intended logic and executing a destructive DROP DATABASE command. This is the classic, high-impact risk of this vulnerability.

The key takeaway is that the database engine cannot distinguish the attacker's malicious command from the developer's intended query because they have been merged into a single, executable string.

Anatomy of a Vulnerable Stored Procedure

Now, let's see what this vulnerability pattern looks like inside a stored procedure. A stored procedure is not a magic shield; if it contains code that performs string concatenation with user input to build a query, it is vulnerable.

The resource "Hacking SQL Server Stored Procedures" provides a perfect example.

Hacking SQL Server Stored Procedures – Part 3

This section from NetSPI's blog shows how to create a stored procedure that is intentionally vulnerable to SQL injection.

Focus on the T-SQL code provided to create the sp_sqli procedure. Let's break down the key components that constitute the vulnerability: Declare @query as varchar(max): A string variable is declared to hold the SQL command. SET @query = 'SELECT ... ''%' + @DbName + '%'' ...': This is the critical flaw. The input parameter @DbName is concatenated directly into the @query string. An attacker controls the value of @DbName. EXECUTE(@query): The dynamically constructed string stored in @query is executed. This three-step pattern—declare a string, build it via concatenation, and execute it—is the primary indicator of this vulnerability.

Furthermore, notice the WITH EXECUTE AS OWNER clause. This means the procedure runs with the permissions of its owner, not the user who calls it. If the owner is a high-privilege account like a sysadmin (sa), an attacker exploiting this procedure can escalate their privileges and potentially take full control of the database server.

Hunting for Vulnerabilities: Database Reconnaissance

As a security analyst, you won't always have the source code conveniently available. You need methods to actively hunt for these vulnerabilities by querying the database's own metadata. SQL Server, for example, stores the source code of its stored procedures in system tables.

You can write queries against these tables to find procedures containing keywords associated with dynamic SQL.

SQL Injection - SQL Server | Microsoft Learn

The Microsoft documentation provides a straightforward query for an initial reconnaissance sweep.

Review the section Review code for SQL injection. The query provided searches the syscomments table for the source code of objects that contain keywords like EXEC or sp_executesql. Running this query gives you a list of potential targets for a more detailed manual review.

While that query is a good start, we can refine our hunt. A procedure that uses sp_executesql might be secure if it's parameterized correctly. A more reliable indicator of vulnerability is the combination of dynamic execution and a high-privilege context.

The NetSPI article provides excellent, targeted queries for exactly this purpose.

Hacking SQL Server Stored Procedures – Part 3

This article details advanced queries for finding potentially vulnerable stored procedures.

First, read the query under the heading "Finding Stored Procedures that use the WITH EXECUTE AS and Dynamic SQL". This query is powerful because it specifically looks for procedures that both use the risky WITH EXECUTE AS clause and contain tell-tale signs of dynamic SQL execution, such as exec ( or concatenating single quotes (''''''+). This narrows the search to high-risk targets. Next, examine the more complex query under the heading "Finding Potentially Vulnerable Stored Procedures Signed with a Certificate". This query hunts for another high-privilege scenario: procedures signed with a certificate that also appear to use dynamic SQL. These queries are practical tools for your analyst toolkit, allowing you to systematically audit a database for these specific misconfigurations.

The output of such a query gives you a list of procedure names and their source code, allowing you to quickly zero in on potential vulnerabilities.

This image shows the results of a query designed to find stored procedures. Note the columns for the procedure name (`SP_NAME`) and its source code (`SP_CODE`), which are essential for manual inspection.

A More Subtle Flaw: Injection by Truncation

Finally, a skilled analyst should be aware of more subtle vulnerabilities. An SQL injection can sometimes be achieved not by breaking out of a string, but by exploiting how the database handles data types and variable lengths. This is known as an injection via truncation.

Imagine a stored procedure that builds a dynamic SQL query inside a variable, say VARCHAR(200). If an attacker provides a very long input string, the database might truncate it to fit into the variable. A clever attacker can craft this long string so that the truncated portion forms a new, malicious command.

SQL Injection - SQL Server | Microsoft Learn

The Microsoft documentation explains this advanced technique with a clear example.

Please read the section titled Injection enabled by data truncation. Pay close attention to the sp_MySetPassword procedure. The @command variable is VARCHAR(200), but the @new password parameter is SYSNAME, which can hold up to 128 characters. The attacker provides a password string that is longer than the remaining space in the @command buffer. When the database constructs the command, the attacker's string gets cut off at a precise point, truncating the original AND password= clause and allowing them to change the sa password without knowing the old one.

This type of vulnerability is harder to spot and requires a deep understanding of how the database server handles data, making it a prime example of the analytical depth required in security assessments.

Conclusion

Today we've moved to the offensive side of database security, learning how to identify SQL injection flaws within stored procedures. You now know that the mere presence of a stored procedure guarantees nothing about security; the implementation is what matters.

Key Takeaways:

Stored procedures are vulnerable to SQL injection if they construct dynamic SQL by concatenating user-controlled input into a query string.
The vulnerable pattern is typically: DECLARE @query ..., SET @query = '...' + @parameter, and EXEC(@query).
As an analyst, you can proactively hunt for these vulnerabilities by querying database metadata (INFORMATION_SCHEMA, sys.objects, etc.) for keywords like EXEC, sp_executesql, and string concatenation patterns.
Vulnerabilities are especially dangerous in high-privilege contexts, such as procedures using WITH EXECUTE AS or those signed by a certificate linked to a privileged user.
Be aware of advanced attack vectors like injection by data truncation, which exploit variable size limits.

In our final lesson of this module, you will apply this knowledge in a more holistic exercise. We will move beyond a single component and task you with conducting a code review of a sample application to identify and document a range of potential SQL injection flaws, bringing together everything you've learned about defensive coding and vulnerability identification.