- Excel Desktop Client new !
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:
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.
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.
|Outputs:||Comma separated list of names for new object references that will store data returned from a SQL query|
|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:|
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:
search1 will validate most SQL parameters to not contain SQL reserved tokens (single-quote for example).
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
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.
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.
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.