SQL injection remains one of the most critical vulnerabilities in database-driven applications, allowing attackers to manipulate queries and potentially access or compromise sensitive data. In InterSystems IRIS, developers have access to both Dynamic SQL and Embedded SQL, each with distinct characteristics. Understanding how to use them securely is essential for preventing SQL injection.
The Problem: Dynamic SQL and SQL Injection
Dynamic SQL constructs queries as strings at runtime. While this offers flexibility, it also creates a vulnerability if user input is not handled correctly. For example:
Set query = "SELECT Name, Age FROM Patients WHERE Age > "_age
Set statement = ##class(%SQL.Statement).%New()
Set status = statement.%Prepare(query)
If age is user-provided, concatenating it directly into the query string exposes the application to injection. An attacker might supply a malicious value such as 0; DROP TABLE Patients, with disastrous results.
The Solution: Parameterised Queries
Parameterised queries are the best defence against SQL injection. Rather than concatenating inputs into the query, user values are bound as parameters. Here is a secure approach using Dynamic SQL:
Set query = "SELECT Name, Age FROM Patients WHERE Age > ?"
Set statement = ##class(%SQL.Statement).%New()
Set status = statement.%Prepare(query)
If status {
Set result = statement.%Execute(age)
While result.%Next() {
Write "Name: ", result.Name, ", Age: ", result.Age, !
}
}
Here, the ? placeholder ensures the age value is treated strictly as data rather than executable code, significantly reducing the risk of injection.
Embedded SQL: Built-in Safety
Embedded SQL integrates SQL directly into ObjectScript, inherently protecting against SQL injection. The host variable syntax (:variable) securely binds parameters at compile time:
&sql(SELECT Name, Age INTO :name, :age FROM Patients WHERE Age > :minAge)
With Embedded SQL, there is no mechanism to concatenate raw user input directly into the query, thereby preventing injection.
Comparing Embedded SQL and Dynamic SQL
| Feature |
Embedded SQL |
Dynamic SQL |
| Security |
Safe from injection due to host variables |
Secure if parameterised; risky if not |
| Flexibility |
Limited (static queries only) |
Highly flexible for dynamic scenarios |
| Searchability |
Easy to locate in class definitions |
Harder to analyse; queries are in strings |
| Performance |
Compiled at class compile time |
Parsed and optimised at runtime |
When to Use Dynamic SQL
Dynamic SQL is useful when query structures must be determined at runtime, for example when adding optional filters:
Set query = "SELECT Name, Age FROM Patients"
If includeGender {
Set query = query_" WHERE Gender = ?"
}
Set statement = ##class(%SQL.Statement).%New()
Set status = statement.%Prepare(query)
If status {
Set result = statement.%Execute("Male")
}
Always remember to use parameterisation (?) for these dynamically built queries to maintain security.
Conclusion
Dynamic SQL allows for flexible query building but demands responsible usage to avoid SQL injection risks. Parameterised queries address this risk effectively. Meanwhile, Embedded SQL comes with built-in safeguards, making it an excellent choice for static queries. By using these approaches appropriately, developers can build robust, secure applications with InterSystems IRIS.
Open Exchange