Options
string
The connection string to the database (e.g.
mysql://user:pass@host:port/dbname).string
required
The SQL statement to be executed. Values from the arguments are inserted
through the placeholders
:1 to :10. Do not put placeholders in quotes.Placeholders in the SQL statement
The placeholders:1 to :10 refer to the arguments argument1 to argument10. What matters is the number, not the order in which the placeholders appear in the statement.
The values are passed to the database as parameters and are treated there as values only, never as part of the statement. This prevents SQL injection and makes manual escaping or quoting unnecessary.
This leads to three rules:
- Placeholders always appear without quotes:
WHERE customer = :1is correct,WHERE customer = ':1'is not. - Placeholders stand for values only. Table and column names must be part of the statement itself.
- Additional characters such as wildcards are appended to the placeholder instead of being written inside it.
Searching with LIKE
The percent signs belong in the statement as separate text and are joined to the placeholder:WHERE customer LIKE '%:1%' does not work.
Date and numeric values
Arguments are always passed as text. Use the formatyyyy-mm-dd for dates:
>= for the start and < for the end. Otherwise entries from the last day are missing for columns that include a time. In PostgreSQL you can make the conversion explicit with :2::date, in MySQL with CAST(:2 AS DATE).
Arguments
string
required
Value for the first parameter in the SQL statement.
string
Value for the second parameter in the SQL statement.
string
Value for the third parameter in the SQL statement.
string
Value for the fourth parameter in the SQL statement.
string
Value for the fifth parameter in the SQL statement.
string
Value for the sixth parameter in the SQL statement.
string
Value for the seventh parameter in the SQL statement.
string
Value for the eighth parameter in the SQL statement.
string
Value for the ninth parameter in the SQL statement.
string
Value for the tenth parameter in the SQL statement.
Example
The following query finds the contracts of a customer that expire within a given period. The customer name is matched as a partial term, and the period is limited by two date values. Optionquery:
Troubleshooting
The query returns no data although it works directly on the database
The query returns no data although it works directly on the database
Check whether the placeholders are enclosed in quotes.
':1' is executed as
literal text and is not replaced by the argument. Use :1 instead and join
wildcards with CONCAT.The query fails as soon as an optional argument stays empty
The query fails as soon as an optional argument stays empty
Only the placeholders with a filled argument are replaced. Use only the
numbers for which values are actually passed.
A date filter does not work as expected
A date filter does not work as expected
Pass date values in the format
yyyy-mm-dd. For columns that include a
time, limit the period with >= and < so that entries from the last day
are included.