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.
Warning |
---|
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. |
...
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
Info |
---|
GlobalForms SSLThe 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.
Code Block | ||||
---|---|---|---|---|
| ||||
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:
...
And it currently uses the following route for the Data Lookup
Code Block | ||||
---|---|---|---|---|
| ||||
"routes": [{
"method": "get",
"endpoint": "/CustomerInfo/:getID",
"query": "SELECT TOP 1 * FROM TestData.dbo.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.
...
For example, we can add a string generated from https://www.random.org/strings/ to our route, so it looks as follows:
Code Block | ||||
---|---|---|---|---|
| ||||
"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.
Code Block | ||||
---|---|---|---|---|
| ||||
"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
...
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".
Code Block | ||||
---|---|---|---|---|
| ||||
"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...
...
...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.
...
Examples
View file | ||
---|---|---|
|
...
...
View file | ||
---|---|---|
|
...
Related articles
Filter by label (Content by label) | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
...
|
Page Properties | ||
---|---|---|
| ||
|