Securing your SQL Server Data Lookups
SQL query form actions and Data Lookups can be used to integrate SQL Server data sources into your web forms. However, it is important that sensitive data is masked or hidden to limit unwanted access. The suggestions in this guide can help make sure that your form is not revealing unnecessary information to your users.
A web application is only as secure as the networks it uses. The suggestions in this guide are best practices for building web forms. They do not guarantee protection from cyber attack, malware, or network security flaws. Consult with a network security specialist or IT professional if you are concerned about the security of your network.
Steps Not Covered in this Guide
How to Configure a Data Lookup
This guide assumes you are familiar with how to implement a data lookup using the methods in our Data Lookup guide. Instructions on how to configure/implement a data lookup are not covered within the scope of this guide. For more information, please reference the following documentation: How To Perform a Data Lookup in GlobalForms 10
Building Data Lookups with Security in Mind
GlobalForms SSL
The first step to improving the security of your forms is to configure your GlobalForms instance with SSL. However, this is not covered within the scope of this guide. For more information, please reference the following documentation: How to Configure SSL with GlobalForms
Starting Example
For our example, we will be using the SQL table shown below.
This table can be created using the following SQL script.
CREATE TABLE [dbo].[CustomerInfo](
[CustomerID] [int] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[CustomerContact] [nvarchar](50) NOT NULL,
[CustomerAddress] [nvarchar](50) NULL,
[CustomerCity] [nvarchar](50) NULL,
[CustomerState] [nvarchar](50) NULL,
[LookupPassword] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CustomerInfo] ([CustomerID], [CustomerName], [CustomerContact], [CustomerAddress], [CustomerCity], [CustomerState], [LookupPassword])
VALUES
(12345, N'First Customer', N'Jane Doe', N'123 Center St.', N'New York', N'NY', N'password1'),
(54321, N'Second Customer', N'John Doe', N'123 Main St.', N'Hartford', N'CT', N'password2')
GOWe will also be starting with the same form from the How To Perform a Data Lookup in GlobalForms 10 documentation. An importable version of the form is available for download at the bottom of this guide.
The example form in its current state looks like this:
And it currently uses the following route for the Data Lookup
"routes": [{
"method": "get",
"endpoint": "/CustomerInfo/:getID",
"query": "SELECT TOP 1 * FROM CustomerInfo WHERE [CustomerID] = {{ params.getID }};"
}]This form can be used to look up the company name and contact info for any company in this SQL table. However, you can view the URL used for the data lookup in a browser console, as shown below (using Google Chrome).
From there, you can use the same URL to make a call without actually loading the form, as such:
As you can see, the form in its current state can be used to view much more information with little security. There are a few steps we can take to improve upon this implementation.
Step 1) Make your URL complicated, so it is harder to guess
First, let us modify the route to prevent users from being able to easily guess the URL. There are a number of ways to accomplish this, but typically inserting a long, complicated string of characters to the URL will suffice. This ensures that the only way to know the Data Lookup route is to have access to the form. If you are using multiple routes, be sure that each route has its own unique string.
For example, we can add a string generated from https://www.random.org/strings/ to our route, so it looks as follows:
"routes": [{
"method": "get",
"endpoint": "/CustomerInfo/qjojH6XWqKy3a2eFpDkaDKLuTl2WleYV1jbY6pt7iZmGl8bfB8D25zZLE6c3/:getID",
"query": "SELECT TOP 1 * FROM TestData.dbo.CustomerInfo WHERE [CustomerID] = {{ params.getID }};"
}]Don't forget to adjust your Select component to use the new URL.
Step 2) Limit your SQL query to only retrieve wanted data
You may have also noticed that the SQL query pulls back all columns, even though we are only looking up the Customer Name and Customer Contact fields. To address this, we can adjust the SQL query in our route.
"routes": [{
"method": "get",
"endpoint": "/CustomerInfo/qjojH6XWqKy3a2eFpDkaDKLuTl2WleYV1jbY6pt7iZmGl8bfB8D25zZLE6c3/:getID",
"query": "SELECT TOP 1 [CustomerName], [CustomerContact] FROM TestData.dbo.CustomerInfo WHERE [CustomerID] = {{ params.getID }};"
}]The resulting URL displays as such:
Step 3) Optionally, require a password for lookup
In its current state, anyone with access to the form has access to view the Customer Name and Customer Contact for any customer, assuming they know the Customer ID. Depending on your particular form implementation, this may be desired. However, if you want to restrict access for users to only look up data that's relevant to them, you may want to include a secure code or password in the data lookup. In our case, we will use the "LookupPassword" column in the SQL data source.
Note: Using a password in this way includes the password in your URL. This prevents users from being able to look up data without the password, but the password can still be read from a network monitor and pulled from the URL.
First, configure your route to only return results that contain the password. If you are unsure if your SQL database is case-sensitive, you can force a case-sensitive collation to ensure that "PASSWORD1" does not work if the intended password is "password1".
"routes": [{
"method": "get",
"endpoint": "/CustomerInfo/qjojH6XWqKy3a2eFpDkaDKLuTl2WleYV1jbY6pt7iZmGl8bfB8D25zZLE6c3/:getID/:password",
"query": "SELECT TOP 1 [CustomerName], [CustomerContact] FROM CustomerInfo WHERE [CustomerID] = {{ params.getID }} AND [LookupPassword] = '{{ params.password }}' COLLATE SQL_Latin1_General_CP1_CS_AS;"
}]Next, update your form accordingly by adding a field for the user to enter the password...
...and changing the URL in the Select component to use it.
You should also configure your Select component to update when the password is changed, rather than the lookup ID. Otherwise, users would have to re-enter the lookup ID after putting in the password.
