SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Prevent SQL Injection using Parameterized Query in SQL Command

SQL Server database applications are vulnerable by SQL injection methods if SQL commands are build dynamically without parameterized SQLCommand is used. To prevent SQL injection, programmers should use parameterized SQL queries build by SQLCommand object and SQLParameter objects.

In this tutorial, I will provide a sample Windows Forms application where user can update data in SQL Server database table using two methods.
One of them provides a secure way of updating data in a SQL Server databaes where parameterized SQL command is used.
Second method is vulnerable to SQL injections and data in SQL database can be manipulated according to the input provided on GUI

Create Visual Studio Project

Launch Visual Studio
Create new project using menu options: File > New > Project...
From installed templates, I selected Visual Basic template for Windows Forms Application project template.

create new project for SQL Injection test in Visual Studio

Here is how I designed the user interface of the sample Visual Studio project to test how developers can prevent SQL injection in their development projects using parameterized queries with command parameters.

sample Visual Studio project

And following is the VB.NET codes that developers can use in their Visual Studio projects to test SQL injection.

vb.net code to test SQL injection

Please compare how SQL command for Update task is formed in btnParameterized.Click event and btnSQLInjection.Click event codes.

Imports System.Data.SqlClient

Public Class Form1

 Dim conn As SqlConnection
 Dim cmd As SqlCommand

 Private Sub executeUpdate()

   Dim sqlConnBuilder As New SqlConnectionStringBuilder()
   sqlConnBuilder.DataSource = txtSQLServer.Text
   sqlConnBuilder.InitialCatalog = txtDatabase.Text
   sqlConnBuilder.IntegratedSecurity = True

   conn = New SqlConnection(sqlConnBuilder.ToString)

   cmd.Connection = conn
   Dim numberOfAffectedRows As Integer = cmd.ExecuteNonQuery()

 End Sub

 Private Sub btnParameterized_Click(sender As Object, e As EventArgs) Handles btnParameterized.Click

   ' Parameterized SQL command which prevents SQL injections.
   ' It Is best practice to use parameterized commands for security and performance
   cmd = New SqlCommand("update Books set Name = @name where Id = @id")
   cmd.Parameters.Add(New SqlParameter("@name", txtName.Text))
   cmd.Parameters.Add(New SqlParameter("@id", txtId.Text))

 End Sub

 Private Sub btnSQLInjection_Click(sender As Object, e As EventArgs) Handles btnSQLInjection.Click

   'Below VB.NET code is open for SQL injections. Avoid using such code in your projects
   cmd = New SqlCommand("update Books set Name = '" + txtName.Text + "' where Id = " + txtId.Text)

 End Sub

 Private Sub link_Kodyaz_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles link_Kodyaz.LinkClicked
   Dim url As String = "/sql-server-tools/prevent-sql-injection-using-parameterized-query.aspx"
 End Sub
End Class

SQL Server Sample Database Table

In the SQL Server instance and the SQL database you plan to make your test, you will need a table named "Books" for the sake of this tutorial.
You can use the CREATE TABLE command and Insert statements for creating table and populating it with test data.

--create database kodyaz
--use kodyaz

Create Table Books(Id int, Name nvarchar(200))
Insert Into Books Select 1, 'SQL Server 2016 Development'
Insert Into Books Select 2, 'T-SQL Programming'
Select * From Books

sample SQL Server database table

We will test how we can update this sample SQL database table using SqlCommand with Parameters in a secure way.
As a second step, we will see how adhoc queries or dynamic queries are open to SQL injection methods which threat the database application security.

SQL Server Profiler

To see how SQL commands by Windows Forms application are executed on SQL Server, we will use SQL Server Profiler.

Launch SQL Profiler using menu options: Tools > SQL Server Profiler

SQL Server Profiler tool

When logon screen is displayed connect to localhost (if you plan to test SQL injection on your local server) or target SQL Server instance.

When Trace Properties screen is displayed switch to Event Selection tab.
Then mark "Show all columns" checkbox.
As next step, click on "Column Filters..." button.
On Edit Filter screen, find DatabaseName on the left window and enter your test database name on the right in "Like" node as seen on below screenshot.

SQL Server Profiler configuration

Click OK, then press Run to start SQL Server Profiler

This SQL Server Profiler configuration will enable sql developer to trace events only executing on selected database.
Filtering database for SQL Profiler will keep the trace logs less eliminating the traces from other databases enabling developer to focus only on desired SQL logs.

Test SQL Injection Application

Let's start our test's first phase. Using secure parameterized method, by using SQL Command object with Parameters we will update our test table data.

Run the Windows Form application, update the SQL Server and database textboxes if necessary.
Keep the Name and Id textbox values as well.
Name textbox has text " SQL Injection'; -- ' " which is a threat for vulnerable codes enabling SQL injection for attackers.

parameterized SQLCommand execution

Before pressing Update (parameterized) button be sure the SQL Profiler is running.
Press the Update (parameterized) button now.

SQL Profiler will log following SQL execution

exec sp_executesql N'update Books set Name = @name where Id = @id',N'@name nvarchar(20),@id nvarchar(1)',@name=N'SQL Injection''; -- ''',@id=N'1'

Let's check how test table is affected by this SQL Update statement execution

SQL injection sample on SQL Server database table

Programmers can realize that only one row with id value equals to 1 is affected.
And the name field is upadted with the new value that we wanted to do so.
Although the provided name text is "'; -- '" and vulnerable string used for SQL injection, because we used parameterized SQL command in our VB.NET code part we successfully managed to prevent SQL injection attack.

SQL Injection Vulnerable SQL Code Execution

Now we can test our second phase where we build the SQL command without using parameters and without parameterized SQL command.

Before going further you can update the Books table back to its original state with modifying the first row to its first value.

Without changing the Name and Id textbox values, this time we change the button to execute UPDATE statement by using button named "Update (SQL Injection)". While the code behind part of the Windows Forms application is building a dynamic SQL Update statement by concatenating string expressions, we can trace the execution of SQL command using SQL Profiler.

In SQL Profiler, this time I observe that following SQL command is executed

update Books set Name = 'SQL Injection'; -- '' where Id = 1

SQL developers can realize that above Update statement updates all rows with a different text. This was not what we aimed to do. Unfortunately your applications have the risk of being attacked by such SQL injection methods like the one we illustrated here.

Let's check the data in Books table targeted by SQL injection

table targeted by SQL injection

Alter your SQL related codes in your database driven applications to prevent SQL injection.
Instead of using adhoc queries build dynamically, use parameterized SQL commands with Parameters shown in the previous part of this tutorial.

Download Visual Studio Project for SQL Injection Sample

download SQL injection sample Programmers can download SQL Injection sample project for Visual Studio to test on SQL Server using download link: prevent SQL Injection using Parameterized query sample project.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.