Run SQL statement

The Run SQL statement policy item can be used to run an SQL statement on a chosen database. This could be a query, or an insert, update, deletion etc. of information to the database.

The most common use for the Run SQL statement policy item is to perform a SQL query to a database, in order to compare the value of selected event data against a set of values in the database. This works by configuring a suitable SQL query (based on the event data values) to send to the database. If the records returned by the database contains a value, then it is considered a match has been found, and the evaluation of the Run SQL statement item will return true (Yes). If the recordset is empty, then the submitted event data value does not exist in the database, and so the Run SQL statement item will return false (No).

The example below shows how a possible SQL query could be constructed.

Using the event data that provides the requesting clients IP address {{Session.ClientIP}}, a SQL query could be made to see if this value is stored in the allowed_ip field of the ip_check table of the database. The result set returned from the database can then be used to determine the correct path of continuation for policy evaluation.

i.e. If there is a recordset returned with a corresponding value, then it is true that the clients IP address is in the ip_check table. If the recordset returned is empty then the client IP is not in the table.

Complex SQL queries can be constructed from more then one piece of event data to provide greater definition of what type of records should be returned for the Run SQL statement item.

Since databases can store vast amounts of information, the possibilities for setting up database tables with configured values such as Banned URLs, Allowed IP addresses, allowed user names, specific strings or numbers, are almost unlimited.

Note

To use the Run SQL statement policy item:

1. Select or create a policy to open a policy editor.
2. In the Items section of the policy editor toolbox, select the Run SQL statement item and drag it on to the worksheet. This will open the Run SQL statement properties.

   

   Close | Close all images

3. In the DSN (Data source name) field click the option button so you can select the registered System DSN that will be used to connect to the ODBC source for this policy item. This will require you to have the ODBC driver/connectivity configured in the operating system of the WinGate server that will provide connectivity to the appropriate database.
4. In the SQL window, enter a suitable SQL statement you wish to send to the database.
5. Select the appropriate event data that will be placed into the SQL statement. You can insert event data symbols as required in the appropriate places in the statement/query by positioning the cursor, and then clicking the Insert symbol button at the right of the SQL window, to open the Symbol Browser. As with any time that you select and insert event data, it will be automatically formatted for correct parsing of the value wherever it will be placed.
6. Once you have completed constructing the SQL statement/query, click OK on the Run SQL statement properties to return to the worksheet.
7. The Run SQL statement policy item will now blink, allowing you to give it a meaningful name so you can identify it easily in the policy evaluation flow.