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.
Sinds 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"