Parameterized Queries

Parameterized queries are now possible with the new ADO.NET SQL interface. This provides the possibility to define queries with parameters which can be substituted later by concrete values (integers, reals or strings). This is especially useful when a query is built using end user data, such as user names and passwords, inserted via graphical pages (forms). When correctly applied, parameterized queries can prevent SQL injection attacks.

Parameters are defined for each DB connection object and concrete values are substituted into queries just before executing the function from the SQLExec family.

The parameters exist in DB connection objects as long as the objects exist. The function SQLParamsClearAll can be used to remove all parameters from a specified DB connection object. There is currently no way to iterate through all existing parameters in a DB connection object nor to remove a single selected parameter from the object’s collection.

Each database provider (ODBC, OleDb, SQL Server) implements parameterized queries in different ways. As SQL queries can be very complicated, it is difficult to provide a generic converter for unifying the parameterization syntax. So to use parameterized queries, you should have knowledge of how they work on a particular provider and structure your Cicode accordingly.

See the examples in SQLParamsSetAsInt and SQLParamsSetAsString.

Published June 2018