Database Request Quoting

The methods Connection class of the database component support a quoting mechanism to build your SQL Request.

This quoting mechanism is similar to the Subst$ function, and it will allow you:

to build database-independent requests.
to avoid SQL injection.

I strongly suggest to use these methods for security reasons, and never build your requests by simple string concatenation!

The substitution methods takes a format string, and a list of arguments.

The &1, &2... patterns will be replaced by respectively the first, second... argument.

The patterns can be in any order in the format string, and can be repeated.

If you want to specify more that nine arguments, you have to enclose the argument index between { and }: &{10}, &{11}, and so on.

By default, the argument is supposed to be a SQL value, and the value is quoted according to its datatype into the result string.

Since 3.18

If a substitution pattern in enclosed with square brackets, then the argument is supposed to be a table name, and the Connection.Quote method is used to correctly quote the table name into the result string.

If a substitution pattern in enclosed with quotes or backquotes, then the argument is supposed to be a column name, and the Connection.Quote method is used to correctly quote the column name into the result string.

Example

' Result with a SQLite database Print DB.Subst("SELECT ** FROM [&1] WHERE '&2' = &3 ORDER BY '&4'", "Table", "Column1", "a string", "Column2")

SELECT ** FROM "Table" WHERE "Column1" = 'a string' ORDER BY "Column2"

' Result with a SQLite database Print DB.Subst("SELECT ** FROM [&1] WHERE '&2' = &3 ORDER BY '&4'", "Schema.Table", "Column1", "a string", "Column2")

SELECT ** FROM "Schema"."Table" WHERE "Column1" = E'a string' ORDER BY "Column2"