Guide Section: Processing Object Data | EASAP Tree: DATA PROCESSING Branch / EVENT PROCESSING Branch / PROCESSES Branch

READ read's data out of the database specified by its Parent DATABASE.

READ uses the Structured Query Language (SQL) to define a SQL query in either the Text: or File: parameters. 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, create a list of new object names in the Outputs: parameter that corresponds to the list of column names specified in the SQL query.

The explicit objects created by READ as specified in the Outputs: parameter 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.

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:	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 act as delimiter for object references in Text: or File: parameters. 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 the On Error: parameter under DATABASE ACTION.
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 the On Error: parameter under DATABASE ACTION.

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.

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 its 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
  • Set Outputs: <space>, inputbox1
  • Set 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 insure only valid expected data is allowed to be entered

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
  • Set Find Text: '
  • Set Replace Text: ' ' ←IMPORTANT: two single-quotes, NOT a double-quote!
  • Set 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'
  • Set 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.