User Tools

Site Tools


Table of Contents

READ

Guide Section: Processing Object Data
EASAP Tree:
DATA PROCESSING > DATABASE
EVENT PROCESSING or PROCESSES > DATA PROCESSING ACTIONS > DATABASE


READ retrieves data from its parent DATABASE.

READ uses the Structured Query Language (SQL) to define a SQL query in either Text: or File:

In either parameter the SQL query must start with a SELECT statement, for example:

  • SELECT column1, column2 FROM table1

Where column1 and column2 are fields in table1.

Additional SQL syntax, such as WHERE and HAVING clauses and SQL joins, may be included in the SQL query.

An Author must be proficient with SQL and must have a good understanding of their database content to use DATABASE and READ.

After formulating a SQL query, declare new LIST names in Outputs: each LIST corresponds to a column name specified in the SQL query in the same order.

The explicit objects created by READ as specified in Outputs: are often used to populate a CHOICE LIST or a TABULATED DATA on the user interface of an EASAP.

DATABASE ACTION > DATABASE > READ is nearly always preferrable to DATA PROCESSING > LIST > DATABASE > READ as, in the latter case, there is no way to prevent the EASAP from running the query each time a linked object value in the query changes (so to be clear, a change of something which lives in memory could trigger a network connection, network traffic, and an indefinite wait or block until the result is returned over the network; the EASAP may be very slow).

READ
Essential Parameters:
Outputs:Comma-separated list of names for new object references that will store data returned from a SQL query
Optional Parameters:
Text:Enter literal text and/or LIST and/or SCALAR references to create a SQL query that begins with a SELECT statement
File:Enter a filename which contains literal text and/or LIST and/or SCALAR references to create a SQL query that begins with a SELECT statement
Replace Nulls:Enter literal text or a SCALAR reference for value to return for all database null values in a results sets
Delimiter:Enter a character to delimit any LIST or SCALAR references in Text: or File:
Default: %
Encoding:text encoding used in File: default→US-ASCII
Output Type:Set to SCALAR if the query will return a single record
Default: LIST, SCALAR
Diagnostics:Enter a name for a new SCALAR to contain a database error message
A name here disables the effect of DATABASE ACTION > On Error:
Error Occurred:Enter a name for a new SCALAR which has two possible status values, 'ERROR' or 'NO ERROR'
A name here disables the effect of DATABASE ACTION > On Error:

SQL Injection Warning

READ does not check to see if a User has entered SQL specific characters or commands in the Text: and File: parameters. If a single-quote or ' - - ' or ' /* ' is entered the query can be extended in a way that either allows much more than the intended data to be returned or else the query may simply become malformed and broken.

Below we provide two solutions to this problem.


1. Use a SEARCH for an 'include list'

SEARCH allows regular expressions to an 'include list' for digits, characters and whitespace in each input field.

For example:

  • Create an INPUTBOX called inputbox1
  • Create a SEARCH called search_for_safe_tokens
  • Set Search Text: ^[\d\w\s]+$
    search_for_safe_tokens will validate most SQL parameters which do not contain SQL reserved tokens (single-quote for example)
  • Create a MAP called sql_safe_input_field
  • Set
    Value: search_for_safe_tokens
    Outputs: <space>, inputbox1
    Inputs: 0.0, 1.0

In general if the Author is able to use a regular expression to restrict the format of valid user input and ignore invalid input, SQL injection can be mitigated.

Use DATA VALIDATION with an INPUTBOX (or, if relevant, set Type: NUMBER) to ensure only valid expected data is allowed to be entered


2. REPLACE single-quotes with two single-quotes

A more advanced solution allows more possible valid inputs. We use REPLACE to allowing single-quotes in the input but we replace each single-quote with two consecutive single-quotes. The query containing a single-quote will still run but not return correct results however this protects from running arbitrary SQL code on the database.

  • Create an INPUTBOX named inputbox1
  • Create a REPLACE named sql_safe_input_field
  • Set
    Value: inputbox1
    Find Text: '
    Replace Text: ' ' (two single-quotes, NOT a double-quote!)
    Replacement Mode: ALL

We need to make sure to use sql_safe_input_field inside of single-quotes so text input that consists of SQL reserved tokens is turned into text within single-quotes.

  • Create a READ
  • Set
    Text: SELECT * FROM db_table WHERE some_field EQUALS 'sql_safe_input_field'
    Outputs: query_results

Any single-quotes entered into inputbox1 will be turned into two consecutive single-quotes (ie. importantly with no SQL reserved tokens between them) and the resulting string is placed with single-quotes for the SQL query.