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.

...

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

...

Starting Example

For our example, we will be using the SQL table shown below.Image Removed

...

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

...

The example form in its current state looks like this:Image Removed

...

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).Image Removed

...

From there, you can use the same URL to make a call without actually loading the form, as such:Image Removed

...

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.

...

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.Image Removed

...

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:Image Removed

...

Step 3) Optionally, require a password for lookup

...

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 TestData.dbo.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...Image Removed

...

...and changing the URL in the Select component to use it.Image Removed

...

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.Image Removed

...

Examples

...

...

Related articles

...