AnewtDatabaseSQLTemplate Class Reference

SQL template class with mandatory type checking. More...

List of all members.

Public Member Functions
	fill ($values=null)
	Fill the SQL template using the values passed as multiple parameters.
	fillv ($values=null)
	Fill the SQL template using the values passed as a single parameter.
Static Public Member Functions
static	query_type_for_sql ($sql)
	Find out the type of an SQL query.
static	field_type_for_string ($field_type)
	Convert a column type string into the associated constant.
Private Member Functions
	__construct ($sql, $connection)
	Construct a new AnewtDatabaseSQLTemplate instance.
	escape_field ($field_type, $value)
	Escape a field for embedding in an SQL query.
	escape_field_array ($field_type, $values)
	Escape multiple values that have the same field type.
Private Attributes
	$connection
	The associated AnewtDatabaseConnection.
	$sql
	The SQL query with sprintf-style format specifiers, after the placeholders have been parsed.
	$placeholders
	The placeholders in this template.
	$named_mode = false
	Indicates whether this SQL template uses positional or named placeholders.

---

Detailed Description

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.

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.

Positional mode

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);

Named mode

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);

Array placeholders

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);

See also:

AnewtDatabaseConnection::prepare()

Definition at line 91 of file sql-template.lib.php.

---

Constructor & Destructor Documentation

AnewtDatabaseSQLTemplate::__construct	(	$	sql,
		$	connection
	)			[private]

Construct a new AnewtDatabaseSQLTemplate instance.

Do not create instances directly; use AnewtDatabaseConnection::sql_template() instead.

Parameters:

	$sql	The SQL template string
	$connection	An AnewtDatabaseConnection instance

See also:

AnewtDatabaseConnection::prepare

AnewtDatabaseConnection::sql_template

Definition at line 258 of file sql-template.lib.php.

---

Member Function Documentation

static AnewtDatabaseSQLTemplate::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.

Parameters:

$sql

The SQL query

Returns:

The type of the query.

Definition at line 108 of file sql-template.lib.php.

static AnewtDatabaseSQLTemplate::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.

Parameters:

$field_type

A string indicating a database field type, e.g. int.

Returns:

Associated ANEWT_DATABASE_SQL_FIELD_TYPE_* constant.

Definition at line 154 of file sql-template.lib.php.

AnewtDatabaseSQLTemplate::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.

Parameters:

	$field_type	The field type (one of the ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
	$value	The value to escape

Returns:

The escaped value

See also:

escape_field_array

Definition at line 360 of file sql-template.lib.php.

AnewtDatabaseSQLTemplate::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[]?).

Parameters:

	$field_type	The field type (one of the ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
	$values	The values to escape

Returns:

The escaped values, in a comma-separated string.

See also:

escape_field

Definition at line 575 of file sql-template.lib.php.

AnewtDatabaseSQLTemplate::fill

(

$

values = null

)

Fill the SQL template using the values passed as multiple parameters.

See AnewtDatabaseSQLTemplate::fillv() for a detailed description.

Parameters:

$values

Returns:

Quoted query

See also:

AnewtDatabaseSQLTemplate::fillv

Definition at line 594 of file sql-template.lib.php.

AnewtDatabaseSQLTemplate::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.

Parameters:

$values

Array 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.

Returns:

The query containing all values, quoted correctly.

See also:

AnewtDatabaseSQLTemplate::fill

Definition at line 616 of file sql-template.lib.php.

---

The documentation for this class was generated from the following file:

• sql-template.lib.php