Getting started with query parameterization

When web applications rely on user-supplied data, there is a potential risk of SQL injection attacks. SQL injection is a technique used to alter a SQL statement by manipulating user input. Specifically, attackers send malicious SQL code to the database to bypass security measures and gain unauthorized access to data. The consequences can be severe, ranging from sensitive information leaks to complete system loss.  

There are a number of ways to prevent a SQL injection attack, a key method being SQL query parameterization. This involves separating SQL code from the user input values using placeholders (parameters) so that the input is treated as data, not SQL code. The key is that prepared statements are used with parameterized queries — a critically beneficial combination.

For an additional layer of security, we can use parameterized stored procedures. Stored procedures are precompiled SQL statements stored in the database. We can grant permissions to execute the stored procedure while restricting direct access to the underlying table. When we’re ready to use a procedure, we pass the parameters it expects from our application.

In this hands-on article, we’ll review how to leverage SQL query parameterization and stored procedures to prevent injection attacks, as well as some additional security measures that help keep our code safe.

Implementing query parameterization

To implement query parameterization, placeholders in the SQL queries are used to represent the user-supplied input. Placeholders are temporary markers that the SQL engine fills with actual variable values. When you execute the query, the SQL engine binds the actual variables to the placeholders before executing the query, ensuring variables are treated as literal data.

You can use query parameterization with or without prepared statements. The difference between these two approaches is that prepared statements are precompiled, and you can call them multiple times with different parameters. 

More importantly, using prepared statements can result in better query parameterization, as the database ensures the statement is precompiled, reducing execution time. And because the execution plan is already pre-created, parameters won’t influence the execution plan — a boon to your security posture.

If you need to call a query multiple times, the database won’t need to recompile the query again — you can just pass the parameters you want to use to the prepared statement. Additionally, using bound parameters minimizes bandwidth since only the parameters are sent to the server — not the entire query. 

The following sections demonstrate how to implement query parameterization in a web application.

Prerequisites

To follow along with this demonstration, ensure you have the following:

• Working knowledge of .NET

• Working knowledge of writing SQL queries.

• Visual Studio with the Snyk extension installed

Query parameterization without prepared statements 

For demonstration purposes, we’re working with a .NET console application that accepts user input from the command line and then uses this input as one of the values in a SQL query statement.

Start by reviewing how to parametrize the query without using a prepared statement. 

Using the code below, get the username with `Console.Readline()`, then define the query. Instead of concatenating the username to the query, you use a placeholder marked by the `@` character. Later, we’ll use the `AddWithValue()` method to bind the username to the query before executing it:

1try
2{
3    string? username = Console.ReadLine();
4    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
5    using (SqlConnection connection = new SqlConnection(connectionString))
6    {
7        connection.Open();
8        SqlCommand command = new SqlCommand(query, connection);
9
10        // Bind the SQL parameters.
11        command.Parameters.AddWithValue("@username", username);
12        command.Parameters.AddWithValue("@id", 20);
13
14        // Execute the query
15        command.ExecuteNonQuery();
16        connection.Close();
17    }
18
19}
20catch (Exception ex)
21{
22    //display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

By passing the parameter values after defining the query, we ensure the SQL server interprets these values literally.

Query parameterization with prepared statements

Using the same scenario, let’s review how to update the search query to use a prepared statement.

The query string remains the same and continues to use `@id` and `@username` placeholders. But in contrast with the above example, after setting the parameters, we call the `Prepare` method on the `SqlCommand` object.

Before you call `Prepare()`, specify each parameter’s data type in the statement. You must also set its size property to the maximum value to prevent an error.

Then, use the `ExecuteNonQuery` method to execute the `INSERT` statement with the provided parameters, inserting the user data into the `Users` table, as demonstrated below:

1try
2{
3    string? username = Console.ReadLine();
4
5    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
6
7    using (SqlConnection connection = new SqlConnection(connectionString))
8    {
9        connection.Open();
10        SqlCommand command = new SqlCommand(query, connection);
11
12        // Bind the SQL parameters.
13        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
14        SqlParameter nameParam = new SqlParameter("@username", SqlDbType.Text, 100);
15        idParam.Value = 8;
16        nameParam.Value = "First Region";
17        command.Parameters.Add(idParam);
18        command.Parameters.Add(nameParam);
19
20        // Call Prepare 
21        command.Prepare();
22        command.ExecuteNonQuery();
23
24        // Change parameter values and call ExecuteNonQuery again.
25        command.Parameters[0].Value = 21;
26        command.Parameters[1].Value = "Alex";
27        command.ExecuteNonQuery();
28    }
29
30}
31catch (Exception ex)
32{
33    //display error message
34    Console.WriteLine("Exception: " + ex.Message);
35}

With prepared statements, we only need to call the `Prepare()` method once. After that, we can change the parameter values and execute the query multiple times without compiling it again. 

Working with stored procedures 

Prepared statements allow us to reuse query statements within our application. However, we can’t call a prepared statement from multiple applications or implement access control measures as you would with stored procedures.

Stored procedures are precompiled queries stored on the database server that we can repeatedly call from several applications. When implemented correctly, stored procedures offer the same protection from SQL injection attacks as prepared statements. We can control who has permission to execute a stored procedure and even restrict direct access to the underlying table, thereby reducing the impact of SQL injection if it occurs.

Like prepared statements, stored procedures also support parameterized queries and consider user input as data instead of SQL code. Additionally, the database automatically sanitizes the parameters passed to the procedure. This process prevents attackers from sending malicious code to the database.

We must use stored procedures alongside query parameters rather than interpolating the dynamic values directly in the query. By maintaining a clear separation between the user input and the SQL statement, we prevent the SQL engine from interpreting it as code. 

To demonstrate, let’s create a stored procedure in our database that accepts an `Id` and `Name` value:

1USE db;  
2GO  
3CREATE PROCEDURE InsertUser
4    @Id INT,
5    @Name VARCHAR(50)  
6AS   
7    INSERT INTO Users (pk_id, Name)
8    VALUES (@Id, @Name);
9GO

If you’re using the SQL Server Management Studio, run the procedure in the query editor and remember to replace db with the name of your database.

Here’s an example of how to use the stored procedure to insert new users into the table.

First, use `Console.Readline()` to prompt for a username via the command line. Then, establish a connection to the database using the `SQLConnection` command. Next, call the `InsertUser` stored procedure you created in the example above. 

To bind the parameters to a stored procedure, use the `Parameters.AddWithValue` method. In this example, we provide a hard coded value for the `@id` parameter and pass the username from the user input for the `@name` parameter.

1try
2{
3    string? username = Console.ReadLine();
4    using (SqlConnection connection = new SqlConnection(connectionString))
5    {
6        connection.Open();
7
8        // Call the stored procedure
9        SqlCommand command = new SqlCommand("InsertUser", connection);
10        command.CommandType = CommandType.StoredProcedure;
11
12        // Bind the SQL parameters.
13        command.Parameters.AddWithValue("@id", SqlDbType.Int).Value = 134;
14        command.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = username;
15
16        // Execute the query
17        command.ExecuteNonQuery();
18    }
19}
20catch (Exception ex)
21{
22    // Display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

After calling the stored procedure and passing in the variables, we execute the query using the `ExecuteNonQuery` method.

Additional security measures for database querying 

In addition to using parameterization with prepared statements and stored procedures, there are other security measures that keep SQL queries secure.

Implement input sanitization and validation

As mentioned, attackers can exploit input data to execute malicious code on the database. This makes input sanitization and validation very important. 

Sanitization allows us to remove any unwanted characters (for example, `{}`, `'`, and `/`) or unsafe HTML from user-supplied data, while validation ensures that the user-supplied data is in the format the database expects. For example, we can check for the length of the input and reject inputs that are too long. We can also enforce formats for email addresses and dates. This technique prevents an attacker from submitting specially formed input values that include SQL statements.

Sanitizing and validating input helps control the input we use in your SQL queries. However, it’s not foolproof — attackers can use techniques like double encoding to bypass our safeguarding attempts. For example, an attacker may encode a single quote (`'`) as `%2527` instead of the typical `%27` to bypass input filters that only decode once. So, avoid using input sanitization as a SQL injection defense and, instead, parameterize your queries using input sanitization as an additional security support.

Use output encoding

When echoing user input to a web application, always encode it first. Output encoding prevents user data from being interpreted as HTML, JavaScript, or SQL.

Use allowlists

Query parameterization isn’t always possible. It doesn’t support certain portions of SQL queries, like table or column names. In such cases, it’s better to validate input using an allowlist. An allowlist lets us define strict rules that only allow certain characters or patterns in the input, ensuring malicious input is rejected.

An allowlist is a better approach to preventing SQL injection attacks than a denylist. An allowlist explicitly defines what type of input is allowed, while a denylist leaves room to maneuver — meaning attackers can easily find new ways to bypass it using input variation. Ultimately, an allowlist gives you more control over what’s accepted.

Implement the principle of least privilege

To minimize the effect of a successful SQL injection attack, always use the principle of least privilege (PoLP). The PoLP restricts users’ access rights to what’s needed to perform their work.

For example, if a user account only requires read access to a table, then following PoLP, we’d grant read-only permission just to that table. If the user needs access to only a portion of the data in a table, we can create a view that includes just that, granting account access to that view instead of the entire table. 

If you’re using stored procedures, limit the execution permissions of your application to the stored procedures it requires. This way, even if SQL injection occurs, you limit the attacker’s access, minimizing the potential impact.

Securing your code with Snyk

Conducting SQL injection tests can provide extra protection from SQL injection vulnerabilities. Automatic static application security testing (SAST) tools like Snyk Code help you catch security problems early on.

Snyk has a vulnerability scanner that analyzes our code right in the IDE. There’s also a Visual Studio Code extension you can find in the extensions manager. Follow the steps to install and configure it.

Once the configuration is complete, you can start using it by clicking the Snyk logo on the sidebar. You should see a menu bar like in the image below. Each menu item has a dropdown arrow you can click to get started. We’ll be using CODE SECURITY.

If we run the code security analysis on a .NET application that doesn’t use parameterized queries, Snyk will alert us that our project has a SQL injection vulnerability.

Consider the following method that uses string formatting to concatenate the user input to the query:

1public void withoutParameters(string dataSource, string initialCat, string persistSecurity, string userId, string password)
2    {
3        string connectionString = $"Data Source={dataSource};Initial Catalog={initialCat};Persist Security Info={persistSecurity};User ID={userId};Password={password}";
4        try
5        {
6
7            string username = Console.ReadLine();
8
9            using (SqlConnection connection = new SqlConnection(connectionString))
10            {
11            connection.Open();
12            string query = $"INSERT INTO Users(pk_id, name) VALUES (48,'{username}');";
13                using (SqlCommand command = new SqlCommand(query, connection))
14                {
15                    command.Parameters.AddWithValue("@username", username);
16
17                    using (SqlDataReader reader = command.ExecuteReader())
18                    {
19                        while (reader.Read())
20                        {
21                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
22                        }
23                    }
24                }
25            }
26        }
27        catch (Exception ex)
28            {
29                //display error message
30                Console.WriteLine("Exception: " + ex.Message);
31            }
32 }

If we run the code security analysis on the code above, Snyk finds one vulnerability, as shown in the screenshot below.

The analysis report indicates an unsanitized input from the command line argument used in the SQL query, possibly resulting in a SQL injection vulnerability. Snyk specifies the line containing the vulnerability and the file. It also shows examples of possible fixes so we can remediate vulnerabilities faster.

Conclusion

Query parameterization is crucial for preventing SQL injection attacks. It ensures that user input is treated as data, not executable code, effectively mitigating the risk of malicious SQL injection. 

We’ve discussed the merits of implementing query parameterization with or without prepared statements — noting that the first option offers better security benefits as it separates SQL logic from user input, eliminating the need for manual escaping or sanitization. Prepared statements also improve performance as queries are precompiled. 

An alternative to prepared statements is stored procedures, which provide an extra layer of security when used with query parameters, as you can control who has access. 

To further enhance the security of your application, we can incorporate Snyk’s vulnerability scanner into the development workflow. By regularly scanning our codebase, we can identify vulnerabilities that may result in SQL injection. This practice enables us to address potential security risks during development, reducing the chance of exposing our application to attackers.