ADO.NET

ADO.NET provides the most direct method of data access within the .NET Framework (link).

To avoid SQL injection in ADO.NET, do not use user input to build the SQL for commands. Instead, do the following:

  1. Use placeholders for values in the SQL of the command,
  2. Add parameters to the command, and
  3. Set the value of the parameter (generally, via the Value property)

Example in C#, against SQL Server: 

// conn refers to an open instance of SqlConnection

var cmd = new SqlCommand() {
   Connection = conn,
   CommandText = "SELECT * FROM Students WHERE FirstName = @FirstName"
};
var prm = cmd.Parameters.Add("StudentName", SqlDbType.NVarChar);
prm.Value = "Robert'; DROP TABLE Students; --";

Because of the shared architecture of ADO.NET-standard implementations (aka data providers), the strategy is the same across all data providers and across all .NET supported languages.

Placeholder syntax, and the method used to bind parameter values to the placeholders, can vary between providers.

For avoiding SQL injection in Entity Framework or other ADO.NET ORMs, see the appropriate pages.

Commands and their uses

In ADO.NET, you specify commands to execute against the data source, via an open connection. Commands consist of a string (read/written via the command's CommandText property), along with other properties. This string can be an SQL statement (it may also contain, a table name, a view name, or some other string understood by the data source); this SQL statement is the primary vector for SQL injection.

A command can:

There is a higher level of abstraction built into ADO.NET: the data set -- an in-memory representation of the data independent of any specific data source or data provider. Data adapters are the bridge between the data source and a data set. Internally, the data adapter makes use of commands in two ways:

  1. When filling the data set with data, the commmand at the data adapter's SelectCommand property is used,

  2. When synchronizing data changes between the data set and the data source, the commands at the InsertCommand, UpdateCommand, and DeleteCommand properties of the data adapter are used.

These commands are also liable to be vulnerable to SQL injection.

ADO.NET architecture

The basic functionality used by ADO.NET to connect to databases and other data sources is defined in a set of abstract (MustInherit in VB.NET) classes in the System.Data.Common namespace. Implementors of this functionality for specific data sources are called ADO.NET data providers, and consist of classes that inherit from these base classes.

For example, the ADO.NET data provider for connecting to SQL Server contains the following classes (among others): in the System.Data.SqlCient namespace:

There are a number of data providers built-in to the .NET Framework, for:

and there are a number of third-party providers for other data sources, for example: SQLite, MySQL, Firebird, PostgreSQL and others.

Placeholder syntax, and binding parameter values to placeholders

Parameter values can be bound to (associated with) placeholders in one of two ways:

  • by name -- the name of the parameter (at the ParameterName propertty) is the same as the name used in the placeholder
  • by position -- based on the order in which parameters are added to the command's parameter collection. In this case, the Name property of the parameter is ignored.
    • The OLE DB and ODBC providers map to the placeholders based on the order of the placeholder usage in the SQL -- the first parameter's value is bound to the first placeholder, the second value to the second placeholder, etc.
    • Other providers make use of placeholders that refer to the number of the parameter -- ?0, ?1, ?2, or $0, $1, $2.

The syntax used for SQL placeholders, and the parameter binding method, varies between providers:

  • SQL Server, Entity SQL, MySQL
    • Named only: SELECT * FROM Students WHERE FirstName = @FirstName
  • OLE DB, ODBC
    • Positional only: SELECT * FROM Students WHERE FirstName = ?
  • Oracle (ODP)
    • Placeholder syntax: SELECT * FROM Students WHERE FirstName = :FirstName
    • Set the OracleCommand.BindByName to true in order to bind by name; otherwise parameters will be bound by position.
  • SQLite
    • Positional:
      • SELECT * FROM Students WHERE FirstName = ?0 OR FirstName = ?1
      • SELECT * FROM Students WHERE FirstName = ?
    • Named:
      • SELECT * FROM Students WHERE FirstName = :FirstName
      • SELECT * FROM Students WHERE FirstName = @FirstName
      • SELECT * FROM Students WHERE FirstName = $FirstName
  • PostgreSQL (npgsl) (other link)
    • Positional:
      • SELECT * FROM Students WHERE FirstName = $0 OR FirstName = $1
    • Named:
      • SELECT * FROM Students WHERE FirstName = :FirstName
      • SELECT * FROM Students WHERE FirstName = @FirstName

Examples

Reading from a data reader

  • Language: IronPython
  • Provider: SQLite
  • imports: from System.Data import DbType; from System.Data.SQLite import SQLiteCommand 
# Note that Python has a built-in module for working with SQLite databases

# conn refers to an open instance of SQLiteConnection

cmd = SQLiteCommand()
cmd.Connection = conn

# SQLite supports multiple placeholder syntaxes, including @name syntax
cmd.CommandText = "SELECT * FROM Students WHERE FirstName = @FirstName"
prm = cmd.Parameters.Add("FirstName", DbType.String)
prm.Value = "Robert' OR 1=1; --"
with cmd.ExecuteReader() as reader:
    while reader.Read():
        print("Last name: %s, first name: %s" %(reader["LastName"], reader["FirstName"]))

Note on with: Objects which might hold onto resources (e.g. memory, or open database connections) need to be explicitly notified to release those resources. Objects indicate this by implementing the IDisposable interface. In IronPython, wrapping the use of those objects in a with block will call the IDisposable.Dispose method once the block exits; the C# using keyword, the VB.NET Using keyword, and the F# use and using idioms all provide similar functionality.

Returning a single value

  • Language: VB.NET
  • Provider: OLE DB
  • Imports: System.Data.OleDb 
' conn refers to an open instance of OleDbConnection

Dim cmd = New OleDbCommand() With {
    .Connection = conn,
    .CommandText = "SELECT COUNT(*) FROM Students WHERE FirstName <> ?"
}
Dim prm = cmd.Parameters.Add("StudentName", OleDbType.VarWChar)
prm.Value = "Robert' OR 1=1; --"
Console.WriteLine($"Number of students not named `Robert' OR 1=1; --`: {cmd.ExecuteScalar}")

Executing without returning a value

  • Language: Powershell
  • Provider: SQL Server 
# $conn refers to an open instance of SqlConnection

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandText = "DELETE FROM Students WHERE FirstName = @FirstName"
$nvarchar = [System.Data.SqlDbType]::NVarChar
$prm = $cmd.Parameters.Add("FirstName", $nvarchar)
$prm.Value = "Robert' OR 1=1; --"
$cmd.ExecuteNonQuery

Fillling a dataset

  • Language: F#
  • Provider: MySQL
  • open: System.Data, MySql.Data.MySqlClient (NuGet: MySql.Data) 
// conn refers to an open instance of MySQLConnection

// Note that the placeholder syntax for MySQL is @name
let sql = "SELECT * FROM Students WHERE FirstName = @FirstName"

// The SQL passed into the constructor of a DataAdapter becomes the CommandText
// for the command at the SelectCommand property
use adapter = new MySqlDataAdapter(sql, conn)
let prm = adapter.SelectCommand.Parameters.Add("FirstName", MySqlDbType.VarChar)
prm.Value <- "Robert' OR 1=1; --"
let students = new DataSet()
adapter.Fill(students, "Students")

Note on use: This is one of two F# idioms for working with IDisposable. See the note on IronPython's use of with in the first example for more details.

References

Microsoft documentation on ADO.NET