Learn How to Write Secure SQL Common Table Expressions
In this tutorial, you will learn how to use Microsoft SQL Server to write common table expressions.
Join the DZone community and get the full member experience.Join For Free
One powerful feature of Microsoft SQL Server is common table, which lets you store a temporary result and execute a statement after using that result set. This can be especially helpful when trying to accomplish complicated processes, like those that SQL Server isn’t well suited to handle. CTEs make challenges easier to solve by breaking difficult operations into two distinct steps.
In this tutorial, you will learn how to use Microsoft SQL Server to write common table expressions. Next, you will learn how to use the statement in a .NET Core MVC web application secured using Okta. Okta works as a powerful but simple single sign-on provider. By using Okta’s Okta.AspNetCore package from Nuget, you will be able to learn how to secure your application and any data from your CTEs properly.
Secure Your SQL CTE With an Okta Application
The first thing you want to do is set up your Okta application to handle your authentication. If you haven’t done so yet, you can sign up for a free developer account here.
Log in to your Okta Developer console and click on Add Application. Select Web and click Next. On the next page, give your Okta application a meaningful name. You will also want to replace your URIs’ ports from 8080 to 3000. Finally, set your Logout redirect URIs to
Click on Done, and you will be taken to your application home screen. Make note of your Client ID, Client secret, and Okta domain, as you will need these in your web application.
Prepare the SQL Database to Use with Your CTE
To work on your database, you will need to have a database first. Microsoft provides several samples via Github. For this project, I used the Wide World Importers sample database v1.0. To use this, you will need to have at least SQL 2016 installed. Microsoft provides
.bacpac files for you to use.
Common Table Expressions (CTEs)
Common table expressions are a temporary result set created by a simple query. Once this result set is obtained, you can perform SELECT, INSERT, DELETE, or MERGE operations on it. CTEs can be used in place of a complicated query - one with difficult joins and logic in it. By operating on a temporary result set, you can simplify the process, making it more readable, easier to optimize, and easier to debug. Let’s take a look at how CTEs work, and how they can make your life easier.
The first thing you notice is that the tax rates are all wrong. You aren’t supposed to charge tax unless the
90490. So, you’ll need to update each line item in the
InvoiceLines table. But, to get the
DeliveryPostalCode, you need a join from the
InvoiceLines table to the
Invoices table; then, a join to the
Customers table. You can round up the
DeliveryPostalCode and associate it to the
InvoiceID with SELECT, using the common table expression below. Next, you can run one statement using the temporary results set
tax_update. After creating your results set of
DeliveryPostalCode, you can update the
InvoiceLines table with the new
TaxRate, and then you can update the
ExtendedPrice with ease.
Next, you can write a select using a CTE. The example below is a bit simple for a CTE (you can accomplish this just with a join) but it serves the purpose of showing you how a CTE is written for selects.
Here, you are selecting the
CustomerID to use to obtain the
CustomerName from the
Customers table, along with the
StockItemId to obtain the
StockItemName in your application.
Create Your ASP.NET Core Web Application
Your SQL database is now set up, and it’s time to begin working on your web application. Open Visual Studio 2019 and Create a new project. Select ASP.NET Core Web Application and press Next. Select Web Application (Model-View-Controller). Ensure your framework is set to .NET Core 3.1 and uncheck Configure for HTTPS. Press Create and wait for your application scaffold.
Once your application is created, open the project properties window and change your App URL to
http://localhost:3000 to match your Okta settings. Next, import the
Okta.AspNetCore package from NuGet.
Install-Package Okta.AspNetCore -Version 3.5.0
Once that is completed, you can begin to add your code. First, take a look at your
appsettings.json file. This is where you will add application-specific variables such as your Okta information or any connection strings. Replace the code in this file with the following. You will need to replace the
WideWorldImporters.ConnectionString information with your connection string.
Next, add a file to hold the SQL Settings for
WideWorldImporters. Add a new folder called
Settings, and add a class file called
SqlSettings.cs to it. Add the following code:
This very simple class will be populated at start-up and injected into your controllers as needed. You can see that process by opening your
Startup.cs file and replacing the code with the following:
Most of this code is boilerplate, but there are a few things you should note. First, the
Configure method doesn’t pre-populate with
app.UseAuthentication(), so you will need to add it here.
Next, you set up your Okta middleware in the
ConfigureServices method. You also register your
WideWorldImporters SQL configuration in this method.
To consume the
WideWorldImporters in your controller, you will need to let .NET Core inject it, and then use it. You can see how this is done in the
DashboardController. Add a file to your
Controllers folder called
DashboardController.cs, and replace the code with the following:
The application is injecting the
IOptions<Settings.SqlSettings> object into this controller. You can reference it later to obtain the connection string for your database. Speaking of your database, this controller also contains the logic for building the model for your view. You will add the model momentarily, but for now, you notice that you are using
ADO.Net to call the CTE you wrote earlier. This works just as well with Dapper or Entity Framework; ADO.Net was chosen here because it’s the simplest to set up.
Add a new class to the
Models folder called DashboardIndexViewModel and add the following code to it:
This model is just taking the items from the query you ran earlier and injecting them into a nice view-model for your view.
Next, add a controller to your
Controllers folder called
AccountController if one does exist. Replace the code with the following.
This code will set your application to use Okta authentication. In the
SignIn method, you look to see if the user is already logged in. If they aren’t, you return a challenge which will redirect them to Okta for authentication. Once the user is logged in, they will be directed to the
Dashboard/Index page. The signout method will redirect users back to the home page.
Finally, you need to add your views. First, open your
Shared/_Layout.cshtml file and replace the code with the following.
This view has some logic that detects if the user is logged in or not. If the user isn’t logged in, you will display a
Login button to them; if they are already logged in, you’ll display a
Next, open your home page and add the following code to it:
There is nothing critical to the application here; it simply provides some extra links for you to read for further learning.
Finally, add or update your
Dashboard/Index.cshtml view with the following code:
This view displays the data in a nice table for your users to see.
Test Your Application
Your application is now ready to start. Press F5 to begin debugging. You should be presented with the home page. From there, you can click on Login or Dashboard. Either should bring you to the Okta login screen. Log in with your Okta account, and you will be presented with the Dashboard.
Check out this project’s repo on GitHub.
Published at DZone with permission of Nickolas Fisher. See the original article here.
Opinions expressed by DZone contributors are their own.