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')
GO

We 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.

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".

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.

GlobalForms 10

Securing your SQL Server Data Lookups