SQL Injection in Dynamic SQL

Dynamic SQL inside stored procedures can be vulnerable to SQL injection when untrusted input is concatenated into executable SQL text. Parameterized dynamic SQL and strict validation of non-parameterizable elements reduce this risk.

Dynamic SQL means SQL statements are assembled at runtime rather than being fixed when the procedure is defined. A procedure may need this for variable table names, optional predicates, dynamic sorting, or other runtime-dependent structure.

The vulnerability appears when untrusted data is concatenated directly into the SQL command. An attacker can supply SQL syntax that changes the intended meaning of the statement, such as adding a predicate or introducing another operation where the database permits it.

The preferred defense is to keep SQL structure separate from data. Database APIs commonly provide parameter binding for values, so the generated statement contains placeholders while user-controlled values are supplied separately. In Java, JDBC `PreparedStatement` provides this separation for ordinary SQL statements.

Some dynamic elements, such as table names, column names, and SQL keywords, generally cannot be supplied as ordinary value parameters. Those elements should instead come from a fixed allowlist or be mapped from trusted identifiers to known SQL fragments. Escaping input alone is generally less robust than using parameterization and controlled SQL construction.

Key points

• Never concatenate untrusted values directly into executable SQL text.
• Use parameter binding for dynamic values whenever the database API supports it.
• Allowlist identifiers such as column or table names when they cannot be parameterized.
• Treat stored procedures as application code: they can still contain injection vulnerabilities.
• Test dynamic SQL paths with malicious input as well as normal input.

Interview questions

How should you handle a dynamic `ORDER BY` column that comes from a request parameter?

A column identifier generally cannot be supplied through a normal value placeholder. I would map the external value to a fixed set of known column names, for example by using a map or switch statement, and insert only the selected trusted SQL fragment. I would reject values outside that allowlist. Any accompanying direction such as ascending or descending should be handled with the same kind of constrained mapping.

Why can a stored procedure still be vulnerable to SQL injection?

A stored procedure can execute SQL that it constructs dynamically at runtime. If it concatenates an untrusted argument into that SQL text, the database may interpret part of the argument as SQL syntax. Being inside a procedure does not automatically separate data from code. The procedure therefore needs the same injection defenses as application-generated SQL.

How would you safely use a user-supplied value in dynamic SQL?

I would construct the SQL statement with a placeholder for the user-controlled value and bind that value through the database API. With JDBC, that normally means using `PreparedStatement` and a setter such as `setString`. The value is then treated as data rather than being parsed as part of the SQL command. I would also review the procedure or query builder for any other dynamically constructed fragments.

What would you look for when reviewing a stored procedure for SQL injection?

I would first identify every place where SQL text is assembled dynamically and trace each input that can influence it. I would distinguish values, which should normally be parameterized, from identifiers or syntax fragments, which require controlled mappings or allowlists. I would then inspect all callers because a procedure parameter may ultimately originate from HTTP, messaging, or another untrusted boundary. Finally, I would test the dangerous paths and verify that database permissions limit the impact if a flaw is reached.

Is input validation alone a sufficient defense against SQL injection in dynamic SQL?

Validation can reduce the set of accepted inputs, but it should not replace parameterization where parameterization is available. Validation is particularly useful for structural choices such as selecting one of several permitted column names. For ordinary data values, parameter binding provides the stronger separation between data and SQL syntax. A defense-in-depth design can use both appropriate validation and parameterization.