Oracle Named Parameters for SQL Injection in .Net

Injection has dropped in priority for many in recent years. For example, on the OWASP Top 10 it went from #1 in 2017 to #3 in 2021. However, Injection continues to be an issue throughout the software development world. If you look at what replaced it as the #1 concern on the OWASP Top 10 you would be excused if you thought things have gotten worse instead of better.

Personally, I see input handling as having several distinct levels. There is the interface level, where the input received from the source should be validated and filtered. At the other end of the process there is the destination or the ‘sink’. Like the source, which has many contexts to influence how it is handled, the destination could have many contexts that should influence how data is filtered and escaped before being passed to the sink. This means that there are always at least two contexts to be concerned about.

Being mindful about how we take input and use it is important. I have encouraged using Input Interaction Modeling in the past because it helps avoid Injection pitfalls. This should lead a Software Engineer to contemplate the components and methods involved. There are some components that should simply never be custom built. Cryptography is an example of a component that should be used and not built one of a kind. Another example is filter and escaping routines for your database. Why? Because there is a mechanism that does it for you already.

Oracle Bind Parameters

For Oracle there is the bind parameter. A parameter is just like it sounds. You put a placeholder in your query for a value to be bound to. At the most simplistic level, this allows you to execute a query multiple times without redefining the query. From a performance perspective, not only does the .Net runtime like this at scale because you avoid operations like string concatenation, but Oracle (and other RDMS) sees a performance boost as well (TL;DR- bind variable = prepared statement = cache boost).

So, what does this have to do with filtering and escaping? When binding to the variable the value is escaped according to the specific RDMS being used, in this case Oracle. Taking this further, you can set the DbType on the bind variable which will throw an exception if the value cannot be converted. This all happens before the query is sent to the database. Thus, you get filtering and escaping for free simply by using a core feature of ADO.NET.

The Code

So what does this look like?

// C#
using (var connection = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleExpress"].ConnectionString))
{
    var query = "SELECT SOME_COLUMN, ANOTHER_COLUMN, THIRD_COLUMN FROM SOME_TABLE WHERE ANOTHER_COLUMN = :SomeParam AND THIRD_COLUMN = :AnotherParam";
    var command = new OracleCommand(query, connection) { CommandType = CommandType.Text, BindByName = true };
    command.Parameters.Add(":AnotherParam", OracleDbType.Varchar2).Value = "Ping";
    command.Parameters.Add(":SomeParam", OracleDbType.Varchar2).Value = "Foo";
    connection. Open();
    var reader = command.ExecuteReader();
    while (reader.Read())
    {
        // Do things
    }
}

' Visual Basic
Using connection As OracleConnection = New OracleConnection(ConfigurationManager.ConnectionStrings("OracleExpress").ConnectionString)
    Dim query As String = "SELECT SOME_COLUMN, ANOTHER_COLUMN, THIRD_COLUMN FROM SOME_TABLE WHERE ANOTHER_COLUMN = :SomeParam AND THIRD_COLUMN = :AnotherParam"
    Dim command As OracleCommand = New OracleCommand(query, connection) With {.CommandType = CommandType.Text, .BindByName = True}
    command.Parameters.Add(":AnotherParam", OracleDbType.Varchar2).Value = "Ping"
    command.Parameters.Add(":SomeParam", OracleDbType.Varchar2).Value = "Foo"
    connection.Open()
    Dim reader As IDataReader = command.ExecuteReader()
    While reader. Read()
        ' Do things
    End While
End Using

So, let’s break this down. Notice :SomeParam in the query text? That is a named parameter. Then once the command is instantiated, a value is bound to that parameter name via Parameter.Add().

I would like to say ‘it is that simple’ but Bind Parameters do not solve all problems. Some systems need to define dynamic table names, and some add or remove columns on the fly. Those situations must be handled carefully. However, even in those circomstances there is every reason to use Bind Variables in ALL SQL.

How much of your code is not using Bind Variables? I challenge you: eliminate all string concatenation in SQL queries in all code.