User Tools

Site Tools


Guide Section: Processing Object Data

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.

The basic form of a SELECT statement is as follows:

  • SELECT columnames FROM tablename

Where tablename will be a table found in the database and columnnames will be a comma separated list of columns found in the specified table. Additional SQL syntax, such as WHERE and HAVING clauses and table joining, 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 the SQL query, declare the new LIST object names in Outputs: each LIST correspond to a column name specified in the SQL query.

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.

READ can be used from the DATA PROCESSING branch or as an ACTION. In general, it is best to use as an ACTION as this gives the Author fine control over when the ACTION is executed. For example, it may be necessary to do a READ immediately after a WRITE has inserted new data into a table.

Essential Parameters:
Outputs:Comma separated list of names for new object references that will store data returned from a SQL query
Optional Parameters:
Text:Text of SQL query that begins with a Select statement (object references allowed)
File:File containing text of SQL query that begins with a SELECT statement (object references allowed)
Replace Nulls:Value to replace any null values returned by the SQL query of a READ.
Delimiter:Character to delimit any object references in Text: or File: ( default→%)
Encoding:Text encoding used in File: default→US-ASCII
Output Type:Sets the data type to be returned from the database. default→LIST, SCALAR
Diagnostics:A string here will create a new, explicit SCALAR with that name that contains the contents of a database error message. Setting this disables the effect of DATABASE ACTION→On Error:
Error Occurred:A string here will create a new explicit SCALAR with that name that has two possible status values: ERROR or NO ERROR. Setting this disables the effect of DATABASE ACTION→On Error:

SQL Injection Warning

READ does not check to see if the 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 allows much more than the intended data to be returned or the query may be malformed and broken.

Use a SEARCH 'Whitelist', example

SEARCH allows regular expressions to 'whitelist' 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]+$

search1 will validate most SQL parameters to 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 can 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

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 called inputbox1
  • Create a REPLACE called sql_safe_input_field
    • Set:
      • Value: inputbox1
      • Find Text: '
      • Replace Text: ' ' ←IMPORTANT: two single-quotes, NOT a double-quote!
      • Replacement Mode: ALL

Now 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.