A lookup route is a web address that returns data from your database. Anyone who can see the address in a form can call it directly. Use these steps to make sure lookups return only what your forms need, to only the people who should see it.
1. Use HTTPS
Serve GlobalForms over HTTPS so lookup data is encrypted in transit. See Configuring HTTPS.
2. Use a least-privilege database account
Give the Resquel database account read access only to the tables and columns that lookups need. Never use sa, root or an owner account.
3. Return only the columns you need
Select specific columns instead of SELECT *:
{
"method": "get",
"endpoint": "/CustomerInfo/:id",
"query": "SELECT TOP 1 CustomerName, CustomerContact FROM CustomerInfo WHERE CustomerID = {{ params.id }};"
}
4. Require a token
Add a tokens list to the route so callers without the token are refused. See Setting Up SQL Lookups (Resquel). A token in a form's Select URL is visible to that form's users, so combine tokens with the other steps.
5. Require a second value for sensitive lookups
To stop users from looking up records that aren't theirs, require a value only the right person knows, such as an access code stored with the record:
{
"method": "get",
"endpoint": "/CustomerInfo/:id/:code",
"query": "SELECT TOP 1 CustomerName, CustomerContact FROM CustomerInfo WHERE CustomerID = {{ params.id }} AND LookupCode = {{ params.code }} COLLATE SQL_Latin1_General_CP1_CS_AS;"
}
-
Do not add quotes around the placeholders.
-
The
COLLATE … CS_ASclause makes the comparison case-sensitive. -
In the form, add a field for the code, include it in the Select's Data Source URL, and set the Select's Refresh On to the code field.
Values in the address can appear in browser history and network logs. Use this for low-risk access codes, not for passwords used elsewhere.
6. Secure the form itself
If the lookup data is sensitive, require users to sign in to use the form. See User Permissions.