SQL template class with mandatory type checking.
SQL template class with mandatory type checking.
This class implements the type checking logic for SQL queries. It is used internally by AnewtDatabasePreparedQuery, but can also be used directly to construct complex SQL queries with e.g. a variable number of placeholders.
SQL templates can be used in two modes: positional and named mode. The amount of input checking performed is the same, but the way the placeholders are specified and filled differs. Additionally, both modes support array values.
In positional mode, placeholders look like ?int?, and when filling the template the values are provided as a list of values to be used for those placeholders, either as multiple parameters (if AnewtDatabaseSQLTemplate::fill() is used) or as an array (if AnewtDatabaseSQLTemplate::fillv() is used).
Example:
$connection->prepare_execute( 'INSERT INTO person (name, age) VALUES (?str?, ?int?)', 'John Doe', 12);In named mode, placeholders look like ?int:name?, and when filling the template the values are provided as an associative array or as an AnewtContainer instance, and the placeholder name is used to obtain the value from the associative array or AnewtContainer. Named mode allows for cleaner code in some cases, and also allows you to use the same value at different places in your query.
Named mode templates can only be filled using a single values parameter, which means AnewtDatabaseSQLTemplate::fillv() must be used; AnewtDatabaseSQLTemplate::fill() cannot. This also applies for the query methods on AnewtDatabaseConnection, where only the ...v() variants can be used, e.g. use AnewtDatabaseConnection::prepare_executev_fetch_all(), not AnewtDatabaseConnection::prepare_execute_fetch_all().
Example:
$person = array( 'name' => 'John Doe', 'age' => 12); $connection->prepare_executev( 'INSERT INTO person (name, age) VALUES (?str:name?, ?int:age?)', $person);In both named and positional modes array placeholders are supported. This means that you can use ?int[]? (positional) or ?int[]:ids? (named) to specify that you will not substitute a a single, but multiple values,. When the AnewtDatabaseSQLTemplate is filled, type-checking will be done on all values, and the result will be a comma-separated list of escaped values. This is most useful for IN (...) SQL queries.
Example:
$ids = array(1, 2, 3); $connection->prepare_execute_fetch_all( 'SELECT * FROM table WHERE id IN * (?int[]?);', $ids);static query_type_for_sql($sql) [static] ¶Find out the type of an SQL query.
The return value is one of the ANEWT_DATABASE_SQL_QUERY_TYPE_* constants, e.g. ANEWT_DATABASE_SQL_QUERY_TYPE_SELECT or ANEWT_DATABASE_SQL_QUERY_TYPE_INSERT.
$sqlThe SQL query
The type of the query.
static field_type_for_string($field_type) [static] ¶Convert a column type string into the associated constant.
This function returns one of the ANEWT_DATABASE_SQL_FIELD_TYPE_* constants, and throws an exception if the passes string is not a valid identifier.
Example: The string int results in the ANEWT_DATABASE_SQL_FIELD_TYPE_INTEGER constant.
$field_typeA string indicating a database field type, e.g. int.
Associated ANEWT_DATABASE_SQL_FIELD_TYPE_* constant.
fill($values=null) ¶Fill the SQL template using the values passed as multiple parameters.
See AnewtDatabaseSQLTemplate::fillv() for a detailed description.
$valuesQuoted query
fillv($values=null) ¶Fill the SQL template using the values passed as a single parameter.
This method will check all values for correctness to avoid nasty SQL injection vulnerabilities.
$valuesArray with values to use for substitution. For positional placeholders this should be a numeric array. For named placeholders an associative array or AnewtContainer instance should be passed.
The query containing all values, quoted correctly.
__construct($sql, $connection) [private] ¶Construct a new AnewtDatabaseSQLTemplate instance.
Do not create instances directly; use AnewtDatabaseConnection::sql_template() instead.
$sqlThe SQL template string
$connectionAn AnewtDatabaseConnection instance
AnewtDatabaseConnection::prepare escape_field($field_type, $value) [private] ¶Escape a field for embedding in an SQL query.
This method does rigid sanity checking and throws errors when the supplied value is not suitable for the specified field type.
$field_typeThe field type (one of the ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
$valueThe value to escape
The escaped value
escape_field_array($field_type, $values) [private] ¶Escape multiple values that have the same field type.
This method concatenates all values using a comma as separator. This method is used for array placeholders and can be used to build queries like these: ... WHERE value IN (?str[]?).
$field_typeThe field type (one of the ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
$valuesThe values to escape
The escaped values, in a comma-separated string.
$connectionThe associated AnewtDatabaseConnection.
$sqlThe SQL query with sprintf-style format specifiers, after the placeholders have been parsed.
$placeholdersThe placeholders in this template.
$named_modeIndicates whether this SQL template uses positional or named placeholders.