ODBC Data Sources¶
This chapter documents methods and behaviors that are specific to the ODBC data source in Lasso. Native support for ODBC data sources is included in Lasso. This feature allows Lasso to communicate with dozens of ODBC-compliant data sources including Sybase, DB2, Frontbase, OpenBase, Interbase, and Microsoft SQL Server. For more information on ODBC connectivity and availability for a particular data source, see the documentation for the data source or contact the data source provider.
Lasso accesses ODBC drivers that are set up as System DSNs. An ODBC Data Source Administrator utility or control panel should be used to configure the driver as a System DSN, after which the data source name can be entered into Lasso. See the Datasource Setup chapter for additional details.
Supported Features for ODBC Data Sources¶
The following chart details the features of this data source connector.
|Friendly Name:||Lasso Connector for ODBC|
|Module Name:||SQLConnector.dll, SQLConnector.dylib, or SQLConnector.so|
|Inline Host Attributes:|
ODBC Data Source Tips¶
The following is a list of guidelines to follow when writing Lasso code that interfaces with ODBC data sources.
Always specify a primary key field using the
-findAllactions. This will ensure that the
keyField_valuemethod will always return a value.
-keyValueto reference a particular record for updates or deletes.
Some data sources will truncate any data beyond the length they are set up to store. Verify that all fields have sufficient capacity for the values that need to be stored in them.
-returnFieldparameters to reduce the number of fields that are returned from a
-searchaction. Returning only the fields that need to be used for further processing or shown to the site visitor reduces the amount of data that needs to travel between Lasso and the data source.
-updateaction is performed on a database, the data from the added or updated record is available inside the capture block of the
inlinemethod. If the
-returnFieldparameter is used, then only those fields specified should be returned from an
-maxRecords=0can be used as an indication that no record should be returned.
SQL statements that are generated using visitor-defined data should be screened carefully for unwanted commands such as “DROP” or “GRANT”.
Always sanitize any inputs from site visitors that are incorporated into SQL statements. For example, any SQL strings that have visitor-defined data should be sanitized using the
string->encodeSqlmethod for MySQL-like data sources or the
string->encodeSql92method for SQL92-compliant data sources or ODBC data sources. Encoding the values in this manner ensures that quotes and other reserved characters are properly escaped within the SQL statement, thereby helping to prevent SQL injection attacks.
For example, the following SQL “SELECT” statement contains a SQL string in the LIKE clause and uses
string->encodeSql92to encode the value of the
web_request->param. This encoding causes all single quotes within the passed
companyparameter to be encoded with an additional single quote.
local(sql_statement) = "SELECT * FROM contacts.people WHERE company LIKE '" + string(web_request->param('company'))->encodeSql92 + "%';"
web_request->param('company')returns “McDonald’s” then the SQL statement generated by this code would appear as follows:
SELECT * FROM Contacts.People WHERE Company LIKE "McDonald's%";
Lasso Server uses connection pooling when connecting to data sources via ODBC, and the ODBC connections will remain open during the time that Lasso Server is running.
Using ODBC Data Sources¶
Data source operations outlined in the Database Interaction Fundamentals, Searching and Displaying Data, and Adding and Updating Records chapters are supported with ODBC data sources. Because ODBC is a standardized API for connecting to tabular data sources, there are no unique methods in Lasso that are specific to ODBC data sources or invoke special functions specific to any ODBC data source.