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

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.