function make_one_cell($field, $value, &$row)
{
$classes = array();
if (method_exists($this, "check_" . $field)) {
if (!call_user_func(array($this, "check_" . $field), $value, $row)) {
$classes['error'] = 1;
}
}
if (isset($this->field_opts[$field]['truncate'])) {
$value = str_truncate($value, $this->field_opts[$field]['truncate']);
}
if (isset($this->field_opts[$field]['datetime']) and $value) {
$value = AnewtDateTime::sql($value);
}
if (isset($this->field_opts[$field]['time']) and $value) {
$value = AnewtDateTime::time($value);
}
if (isset($this->field_opts[$field]['num'])) {
$this->stat[$field]['sum'] += intval($value);
}
if (preg_match('/^[\\d\\+\\-\\.eE]+$/', $value) || isset($this->field_opts[$field]['flushright'])) {
$classes['number'] = 1;
}
$display_value = $value;
if (method_exists($this, "display_" . $field)) {
$display_value = call_user_func(array($this, "display_" . $field), $value, &$row);
}
$expand = array_get_default($this->field_opts[$field], 'expand', "0");
$dynamic = array_get_default($this->field_opts[$field], 'dynamic', FALSE);
if ($dynamic || $expand > 0 && is_string($value) && strlen($value) > 0 && strlen($value) > $expand) {
$display_value = ax_a(array(str_truncate($value, $expand, "...", true), ax_span($display_value, array('class' => 'expanded'))), array('class' => 'expand'));
}
$c =& ax_td($display_value);
foreach ($classes as $class => $val) {
$c->add_class($class);
}
return $c;
}
function get_data($id)
{
$q = "SELECT * FROM ssscrapecontrol.ssscrape_task WHERE id=?str?";
$db = DB::get_instance();
$data = $db->prepare_execute_fetch($q, $id);
if ($data) {
$data['periodicity'] = AnewtDateTime::time($data['periodicity']);
$data['latest_run'] = AnewtDateTime::sql($data['latest_run']);
$this->fill($data);
return true;
}
return false;
}
/**
* 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;
}
function valid_values_provider()
{
return array(array(null, null, null, null, null, null, null, null, null), array(true, null, null, null, null, null, null, null, null), array(false, null, null, null, null, null, null, null, null), array(null, 2, null, null, null, null, null, null, null), array(null, '3', null, null, null, null, null, null, null), array(null, null, 2.0, null, null, null, null, null, null), array(null, null, 1.234, null, null, null, null, null, null), array(null, null, 3, null, null, null, null, null, null), array(null, null, null, 'Test', null, null, null, null, null), array(null, null, null, 'Te\';st', null, null, null, null, null), array(null, null, null, "\t\n;--'", null, null, null, null, null), array(null, null, null, 2, null, null, null, null, null), array(null, null, null, new StringWrap(), null, null, null, null, null), array(null, null, null, null, '2006-06-06', null, null, null, null), array(null, null, null, null, AnewtDateTime::now(), null, null, null, null), array(null, null, null, null, null, '2006-06-06 06:06:06', null, null, null), array(null, null, null, null, null, AnewtDateTime::now(), null, null, null), array(null, null, null, null, null, AnewtDateTime::sql(AnewtDateTime::now()), null, null, null), array(null, null, null, null, null, null, '2006-06-06 06:06:06', null, null), array(null, null, null, null, null, null, AnewtDateTime::now(), null, null), array(null, null, null, null, null, null, AnewtDateTime::sql(AnewtDateTime::now()), null, null), array(null, null, null, null, null, null, null, '06:06:06', null), array(null, null, null, null, null, null, null, AnewtDateTime::now(), null), array(null, null, null, null, null, null, null, AnewtDateTime::sql_time(AnewtDateTime::now()), null), array(null, null, null, null, null, null, null, null, "'?int?'"), array(true, 3, 2.0, 'Test', '2006-06-06', '2006-06-06 06:06:06', '2006-06-06 06:06:06', '06:06:06', "'?raw?'"));
}
/**
* 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;
}
function get_data($id)
{
$q = "SELECT * FROM ssscrape.ssscrape_feed WHERE id=?str?";
$db = DB::get_instance();
$data = $db->prepare_execute_fetch($q, $id);
if ($data) {
$data['pub_date'] = AnewtDateTime::sql($data['pub_date']);
$data['mod_date'] = AnewtDateTime::sql($data['mod_date']);
$q = "SELECT * FROM ssscrape.ssscrape_feed_metadata WHERE feed_id=?str?";
$metadata = $db->prepare_execute_fetch($q, $id);
if ($metadata) {
foreach ($metadata as $name => $value) {
$data['m_' . $name] = $value;
}
}
$q = "SELECT * FROM ssscrapecontrol.ssscrape_task WHERE LOCATE(?str?, args) AND type in ('fetch', 'peilendfetch')";
$task_data = $db->prepare_execute_fetch($q, "'" . $data['url'] . "'");
if ($task_data) {
$task_data['periodicity'] = AnewtDateTime::time($task_data['periodicity']);
$task_data['latest_run'] = AnewtDateTime::sql($task_data['latest_run']);
foreach ($task_data as $name => $value) {
$data['t_' . $name] = $value;
}
}
$q = "SELECT * FROM ssscrapecontrol.ssscrape_task WHERE LOCATE(?str?, args) AND type='modelupdate'";
$task_data = $db->prepare_execute_fetch($q, "'" . $data['url'] . "'");
if ($task_data) {
if ($task_data['periodicity']) {
$task_data['periodicity'] = AnewtDateTime::time($task_data['periodicity']);
} else {
# In case periodicity cannot be read from the DB
$task_data['periodicity'] = "24:00:00";
}
$task_data['latest_run'] = AnewtDateTime::sql($task_data['latest_run']);
foreach ($task_data as $name => $value) {
$data['ct_' . $name] = $value;
}
}
$this->fill($data);
return true;
}
return false;
}
$pq->execute($values);
$values->set('date_var', null);
/* Test datetimes */
$values->set('datetime_var', '2006-06-06 06:06:06');
$pq->execute($values);
$values->set('datetime_var', AnewtDateTime::now());
$pq->execute($values);
$values->set('datetime_var', AnewtDateTime::sql(AnewtDateTime::now()));
$pq->execute($values);
$values->set('datetime_var', null);
/* Test timestamps */
$values->set('timestamp_var', '2006-06-06 06:06:06');
$pq->execute($values);
$values->set('timestamp_var', AnewtDateTime::now());
$pq->execute($values);
$values->set('timestamp_var', AnewtDateTime::sql(AnewtDateTime::now()));
$pq->execute($values);
$values->set('timestamp_var', null);
/* Test times */
$values->set('time_var', '06:06:06');
$pq->execute($values);
$values->set('time_var', AnewtDateTime::now());
$pq->execute($values);
$values->set('time_var', null);
/* Test raw */
$values->set('raw_var', '"?int?"');
$pq->execute($values);
$values->set('raw_var', null);
/* Test all at once */
$values->seed(array('bool_var' => true, 'int_var' => 3, 'float_var' => 2.0, 'string_var' => 'Test', 'date_var' => '2006-06-06', 'datetime_var' => '2006-06-06 06:06:06', 'timestamp_var' => '2006-06-06 06:06:06', 'time_var' => '06:06:06', 'raw_var' => '"?raw?"'));
$pq->execute($values);
