/**
* 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.
*
* \param $field_type
* The field type (one of the \c ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
* \param $value
* The value to escape
*
* \return
* The escaped value
*
* \see escape_field_array
*/
private function escape_field($field_type, $value)
{
/* Escaping is not needed for NULL values. */
if (is_null($value)) {
return 'NULL';
}
/* The value is non-null. Perform very restrictive input sanitizing
* based on the field type. */
switch ($field_type) {
case ANEWT_DATABASE_SQL_FIELD_TYPE_BOOLEAN:
/* Integers: only accept 0 and 1 (no type juggling!) */
if (is_int($value)) {
if ($value === 0) {
$value = false;
} elseif ($value === 1) {
$value = true;
}
}
/* Strings: only accept literal "0" and "1" (no type juggling!) */
if (is_string($value)) {
if ($value === "0") {
$value = false;
} elseif ($value === "1") {
$value = true;
}
}
if (is_bool($value)) {
$value = $this->connection->escape_boolean($value);
break;
}
throw new AnewtDatabaseQueryException('Invalid boolean value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_INTEGER:
if (is_int($value)) {
$value = (string) $value;
break;
}
if (is_string($value) && preg_match('/^-?\\d+$/', $value)) {
break;
}
throw new AnewtDatabaseQueryException('Invalid integer value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_FLOAT:
/* FIXME: this does not accept .123 (without a leading zero) */
if (is_string($value) && preg_match('/^-?\\d+(\\.\\d*)?$/', $value)) {
/* Enough checks done by the regex, no need to do any
* formatting/escaping */
break;
} elseif (is_int($value) || is_float($value)) {
/* Locale-agnostic float formatting */
$value = number_format($value, 10, '.', '');
if (str_has_suffix($value, '.')) {
$value .= '0';
}
break;
}
throw new AnewtDatabaseQueryException('Invalid float value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_STRING:
/* Accept integers and objects with a render() method. */
if (is_int($value)) {
$value = (string) $value;
} elseif (is_object($value) && method_exists($value, 'render')) {
$value = to_string($value);
}
/* From this point on only strings are accepted. */
if (is_string($value)) {
$value = $this->connection->escape_string($value);
break;
}
throw new AnewtDatabaseQueryException('Invalid string value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_DATE:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql_date($value);
}
if (is_string($value) && preg_match('/^\\d{2,4}-\\d{2}-\\d{2}$/', $value)) {
$value = $this->connection->escape_date($value);
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
throw new AnewtDatabaseQueryException('Invalid date value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_TIME:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql_time($value);
}
if (is_string($value) && preg_match('/^\\d{2}:\\d{2}(:\\d{2})?$/', $value)) {
$value = $this->connection->escape_time($value);
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
throw new AnewtDatabaseQueryException('Invalid time value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_DATETIME:
case ANEWT_DATABASE_SQL_FIELD_TYPE_TIMESTAMP:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql($value);
}
if (is_string($value) && preg_match('/^\\d{2,4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$/', $value)) {
$value = $this->connection->escape_datetime($value);
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
throw new AnewtDatabaseQueryException('Invalid datetime or timestamp value: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_RAW:
/* No checking, no escaping... use at your own risk! */
break;
/* The column and table type are mostly for internal usage, it's
* a BAD idea to use user data for these fields! */
/* The column and table type are mostly for internal usage, it's
* a BAD idea to use user data for these fields! */
case ANEWT_DATABASE_SQL_FIELD_TYPE_COLUMN:
if (is_string($value) && preg_match('/^([a-z0-9_-]+\\.)*[a-z0-9_-]+$/i', $value)) {
$value = $this->connection->escape_column_name($value);
break;
}
throw new AnewtDatabaseQueryException('Invalid column name: "%s"', $value);
case ANEWT_DATABASE_SQL_FIELD_TYPE_TABLE:
if (is_string($value) && preg_match('/^([a-z0-9_-]+\\.)*[a-z0-9_-]+$/i', $value)) {
$value = $this->connection->escape_table_name($value);
break;
}
throw new AnewtDatabaseQueryException('Invalid table name: "%s"', $value);
default:
throw new AnewtDatabaseQueryException('Unsupported field type! Please file a bug.');
break;
}
assert('is_string($value)');
return $value;
}
/**
* Fills in the valus in the SQL template. This method will check all values
* for correctness to avoid nasty SQL injection vulnerabilities.
*
* \param $args
* Array with values to use for substitution.
*
* \return
* The query containing all values, quoted correctly.
*/
function fill($args = null)
{
/* We accept either:
* - no parameters
* - multiple scalar parameters
* - 1 array parameter with scalar elements
* - 1 associative array parameter
* - 1 container parameter
*/
$args = func_get_args();
if ($this->named_mode) {
if (count($args) != 1) {
trigger_error('associative array or Container expected', E_USER_ERROR);
}
if ($args[0] instanceof Container) {
$args = $args[0]->to_array();
} elseif (is_array($args[0])) {
$args = $args[0];
} else {
trigger_error('associative array or Container expected', E_USER_ERROR);
}
$numargs = count($this->named_placeholders);
} else {
if (count($args) == 1 && is_numeric_array($args[0])) {
$args = $args[0];
}
assert('is_numeric_array($args)');
if (count($args) != count($this->placeholders)) {
trigger_error(sprintf('Incorrect number of parameters to SQLTemplate::fill(): expected %d, got %d', count($this->placeholders), count($args)), E_USER_ERROR);
}
$numargs = count($args);
}
/* Note: don't use foreach() here, because it copies the values in
* memory and leaves the original values untouched! */
for ($i = 0; $i < $numargs; $i++) {
if ($this->named_mode) {
$fieldtype = $this->named_placeholders[$i]['type'];
$var = $this->named_placeholders[$i]['var'];
if (!isset($args[$var])) {
$var = str_replace('-', '_', $var);
// Container replaces '-' with '_'
if (!array_key_exists($var, $args)) {
trigger_error(sprintf('SQLTemplate::fill(): missing expected parameter "%s"', $this->named_placeholders[$i]['var']), E_USER_ERROR);
}
}
$value = $args[$var];
$argname = "`" . $var . "'";
} else {
$fieldtype = $this->placeholders[$i];
$value =& $args[$i];
$argname = $i + 1;
}
/* Handle NULL values here. Escaping is not needed for NULL values. */
if (is_null($value)) {
$value = 'NULL';
if ($this->named_mode) {
$arglist[$i] = $value;
}
continue;
}
/* The value is non-null. Perform very restrictive input sanitizing
* based on the field type. */
switch ($fieldtype) {
case ANEWT_DATABASE_TYPE_BOOLEAN:
/* Integers: only accept 0 and 1 (no type juggling!) */
if (is_int($value)) {
if ($value === 0) {
$value = false;
} elseif ($value === 1) {
$value = true;
}
}
/* Strings: only accept literal "0" and "1" (no type juggling!) */
if (is_string($value)) {
if ($value === "0") {
$value = false;
} elseif ($value === "1") {
$value = true;
}
}
if (is_bool($value)) {
$value = $this->db->backend->escape_boolean($value);
break;
}
trigger_error(sprintf('Invalid boolean value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_INTEGER:
if (is_int($value)) {
$value = (string) $value;
break;
}
if (is_string($value) && preg_match('/^-?\\d+$/', $value)) {
break;
}
trigger_error(sprintf('Invalid integer value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_FLOAT:
// FIXME: this does not accept .123 (without a leading zero)
if (is_string($value) && preg_match('/^-?\\d+(\\.\\d*)?$/', $value)) {
/* Enough checks done by the regex, no need to do any
* formatting/escaping */
break;
/* Locale-agnostic float formatting */
} elseif (is_int($value) || is_float($value)) {
$value = number_format($value, 10, '.', '');
if (str_has_suffix($value, '.')) {
$value .= '0';
}
break;
}
trigger_error(sprintf('Invalid float value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_STRING:
/* Accept integers and objects with a render() method. */
if (is_int($value)) {
$value = (string) $value;
} elseif (is_object($value) && method_exists($value, 'render')) {
$value = $value->render();
}
/* From this point on, only strings are accepted. */
if (is_string($value)) {
$value = $this->db->backend->escape_string($value);
break;
}
trigger_error(sprintf('Invalid string value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_DATE:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql_date($value);
}
if (is_string($value) && preg_match('/^\\d{2,4}-\\d{2}-\\d{2}$/', $value)) {
$value = $this->db->backend->escape_date($value);
break;
}
if (is_string($value) && strtoupper($value) == "NOW") {
$value = "NOW()";
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
trigger_error(sprintf('Invalid date value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_TIME:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql_time($value);
}
if (is_string($value) && preg_match('/^\\d{2}:\\d{2}(:\\d{2})?$/', $value)) {
$value = $this->db->backend->escape_time($value);
break;
}
if (is_string($value) && strtoupper($value) == "NOW") {
$value = "NOW()";
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
trigger_error(sprintf('Invalid time value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_DATETIME:
case ANEWT_DATABASE_TYPE_TIMESTAMP:
if ($value instanceof AnewtDateTimeAtom) {
$value = AnewtDateTime::sql($value);
}
if (is_string($value) && preg_match('/^\\d{2,4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$/', $value)) {
$value = $this->db->backend->escape_datetime($value);
break;
}
if (is_string($value) && strtoupper($value) == "NOW") {
$value = "NOW()";
break;
}
if (is_string($value) && strtoupper($value) == 'NOW') {
$value = 'NOW()';
break;
}
trigger_error(sprintf('Invalid datetime or timestamp value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_RAW:
/* No checking, no escaping... use at your own risk ;-) */
break;
/* The column and table type are mostly for internal usage, it's
* a BAD idea to use user data for these fields! */
/* The column and table type are mostly for internal usage, it's
* a BAD idea to use user data for these fields! */
case ANEWT_DATABASE_TYPE_COLUMN:
if (is_string($value) && preg_match('/^([a-z0-9_-]+\\.)*[a-z0-9_-]+$/i', $value)) {
$value = $this->db->backend->escape_column_name($value);
break;
}
trigger_error(sprintf('Invalid column value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
case ANEWT_DATABASE_TYPE_TABLE:
if (is_string($value) && preg_match('/^([a-z0-9_-]+\\.)*[a-z0-9_-]+$/i', $value)) {
$value = $this->db->backend->escape_table_name($value);
break;
}
trigger_error(sprintf('Invalid table value: "%s" on argument %s', $value, $argname), E_USER_ERROR);
default:
trigger_error('This is a bug! Fieldtype unknown', E_USER_ERROR);
break;
}
assert('is_string($value)');
if ($this->named_mode) {
$arglist[$i] = $value;
}
}
/* Now that all supplied values are validated and escaped properly, we
* can easily substitute them into the query template. The %s
* placeholders were already prepared during initial parsing. */
if ($this->named_mode) {
$query = vsprintf($this->sql_template, $arglist);
} else {
$query = vsprintf($this->sql_template, $args);
}
return $query;
}
