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.
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.
And following is the VB.NET codes that developers can use in their Visual Studio projects 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)
conn.Open()
cmd.Connection = conn
Dim numberOfAffectedRows As Integer = cmd.ExecuteNonQuery()
conn.Close()
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))
executeUpdate()
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)
executeUpdate()
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"
System.Diagnostics.Process.Start(url)
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
--go
--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
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
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.
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.
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
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
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
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.