/**
* array_walk_recursive wrapper to sanitizeHtml()
*
* array_walk needs to be working with the actual values of the array,
* so the parameter of funcname is specified as a reference (i.e. &)
*
* @param string &$value
*/
private function sanitizeByReference(&$value)
{
// Reverse htmlentities, we want usable html
$value = html_entity_decode(stripslashes($value), ENT_QUOTES, 'UTF-8');
// Get rid of font tags before handing off to htmLawed,
// see: http://www.bioinformatics.org/phplabware/forum/viewtopic.php?id=64
$value = preg_replace('/<font([^>]+)>/i', '', $value);
$value = str_ireplace('</font>', '', $value);
// Sanitize
$value = suxFunct::sanitizeHtml($value, 0);
}
/**
* Write something to the users_log table
*
* @param string $body_html
* @param int $users_id
* @param int $private
*/
function write($users_id, $body_html, $private = false)
{
// Any user
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
$private = $private ? true : false;
$clean['users_id'] = $users_id;
$clean['private'] = $private;
$clean['body_html'] = suxFunct::sanitizeHtml($body_html, -1);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Timestamp
$clean['ts'] = date('Y-m-d H:i:s');
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table, $clean);
$st = $this->db->prepare($query);
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if ($this->db_driver == 'pgsql') {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
$st->bindParam(':private', $clean['private'], PDO::PARAM_BOOL);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':ts', $clean['ts'], PDO::PARAM_STR);
$st->execute();
} else {
$st->execute($clean);
}
}
/**
* Saves an album to the database
*
* @param int $users_id users_id
* @param array $album required keys => (url, title, body) optional keys => (id, published_on, draft)
* @param int $trusted passed on to sanitizeHtml()
* @return int insert id
*/
function saveAlbum($users_id, array $album, $trusted = -1)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
if (!isset($album['title']) || !isset($album['body'])) {
throw new Exception('Invalid $album array');
}
// Album id
if (isset($album['id'])) {
if (!filter_var($album['id'], FILTER_VALIDATE_INT) || $album['id'] < 1) {
throw new Exception('Invalid album id');
} else {
$clean['id'] = $album['id'];
}
}
// Users id
$clean['users_id'] = $users_id;
// No HTML in title
$clean['title'] = strip_tags($album['title']);
// Sanitize HTML in body
$clean['body_html'] = suxFunct::sanitizeHtml($album['body'], $trusted);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Draft, boolean / tinyint
$clean['draft'] = false;
if (isset($album['draft']) && $album['draft']) {
$clean['draft'] = true;
}
// Publish date
if (isset($album['published_on'])) {
// ISO 8601 date format
// regex must match '2008-06-18 16:53:29' or '2008-06-18T16:53:29-04:00'
$regex = '/^(\\d{4})-(0[0-9]|1[0,1,2])-([0,1,2][0-9]|3[0,1]).+(\\d{2}):(\\d{2}):(\\d{2})/';
if (!preg_match($regex, $album['published_on'])) {
throw new Exception('Invalid date');
}
$clean['published_on'] = $album['published_on'];
} else {
$clean['published_on'] = date('Y-m-d H:i:s');
}
// We now have the $clean[] array
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if (isset($clean['id'])) {
// UPDATE
unset($clean['users_id']);
// Don't override the original submitter
$query = suxDB::prepareUpdateQuery($this->db_albums, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':id', $clean['id'], PDO::PARAM_INT);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
$st->execute();
} else {
$st->execute($clean);
}
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_albums, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
$st->execute();
} else {
$st->execute($clean);
}
if ($this->db_driver == 'pgsql') {
$clean['id'] = $this->db->lastInsertId("{$this->db_albums}_id_seq");
} else {
$clean['id'] = $this->db->lastInsertId();
}
}
return $clean['id'];
}
/**
* Saves a message to the database
*
* @param int $users_id users_id
* @param array $msg required keys => (title, body, [forum|blog|wiki|slideshow]) optional keys => (published_on)
* @param int $trusted passed on to sanitizeHtml()
* @return int insert id
*/
function save($users_id, array $msg, $trusted = -1)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
if (!isset($msg['title']) || !isset($msg['body'])) {
throw new Exception('Invalid $msg array');
}
// Message id
if (isset($msg['id'])) {
if (!filter_var($msg['id'], FILTER_VALIDATE_INT) || $msg['id'] < 1) {
throw new Exception('Invalid message id');
} else {
$clean['id'] = $msg['id'];
}
}
// Users id
$clean['users_id'] = $users_id;
// Parent_id
$clean['parent_id'] = @filter_var($msg['parent_id'], FILTER_VALIDATE_INT);
if ($clean['parent_id'] === false) {
$clean['parent_id'] = 0;
}
// No HTML in title
$clean['title'] = strip_tags($msg['title']);
// Sanitize HTML in body
$clean['body_html'] = suxFunct::sanitizeHtml($msg['body'], $trusted);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Image
if (isset($msg['image'])) {
$clean['image'] = filter_var($msg['image'], FILTER_SANITIZE_STRING);
}
// Publish date
if (isset($msg['published_on'])) {
// ISO 8601 date format
// regex must match '2008-06-18 16:53:29' or '2008-06-18T16:53:29-04:00'
$regex = '/^(\\d{4})-(0[0-9]|1[0,1,2])-([0,1,2][0-9]|3[0,1]).+(\\d{2}):(\\d{2}):(\\d{2})/';
if (!preg_match($regex, $msg['published_on'])) {
throw new Exception('Invalid date');
}
$clean['published_on'] = $msg['published_on'];
} else {
$clean['published_on'] = date('Y-m-d H:i:s');
}
// Draft, boolean / tinyint
$clean['draft'] = false;
if (isset($msg['draft']) && $msg['draft']) {
$clean['draft'] = true;
}
// Types of threaded messages
$clean['blog'] = false;
$clean['forum'] = false;
$clean['wiki'] = false;
$clean['slideshow'] = false;
if (isset($msg['blog']) && $msg['blog']) {
$clean['blog'] = true;
}
if (isset($msg['forum']) && $msg['forum']) {
$clean['forum'] = true;
}
if (isset($msg['wiki']) && $msg['wiki']) {
$clean['wiki'] = true;
}
if (isset($msg['slideshow']) && $msg['slideshow']) {
$clean['slideshow'] = true;
}
if (!$clean['forum'] && !$clean['blog'] && !$clean['wiki'] && !$clean['slideshow']) {
throw new Exception('No message type specified?');
}
// We now have the $clean[] array
// -------------------------------------------------------------------
// Go!
// -------------------------------------------------------------------
// Begin transaction
$tid = suxDB::requestTransaction();
$this->inTransaction = true;
if (isset($clean['id'])) {
// UPDATE
// Get $edit[] array in order to keep a history
$query = "SELECT title, image, body_html, body_plaintext FROM {$this->db_table} WHERE id = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['id']));
$edit = $st->fetch(PDO::FETCH_ASSOC);
if (!$edit) {
throw new Exception('No message to edit?');
}
$edit['messages_id'] = $clean['id'];
$edit['users_id'] = $clean['users_id'];
$edit['edited_on'] = date('Y-m-d H:i:s');
$query = suxDB::prepareInsertQuery($this->db_table_hist, $edit);
$st = $this->db->prepare($query);
$st->execute($edit);
unset($clean['users_id']);
// Don't override the original publisher
// Update the message
$query = suxDB::prepareUpdateQuery($this->db_table, $clean);
} else {
// INSERT
/*
The first message in a thread has thread_pos = 0.
For a new message N, if there are no messages in the thread with the same
parent as N, N's thread_pos is one greater than its parent's thread_pos.
For a new message N, if there are messages in the thread with the same
parent as N, N's thread_pos is one greater than the biggest thread_pos
of all the messages with the same parent as N, recursively.
After new message N's thread_pos is determined, all messages in the same
thread with a thread_pos value greater than or equal to N's have their
thread_pos value incremented by 1 (to make room for N).
*/
if ($clean['parent_id']) {
// Get thread_id, level, and thread_pos from parent
$st = $this->db->prepare("SELECT thread_id, level, thread_pos FROM {$this->db_table} WHERE id = ? ");
$st->execute(array($clean['parent_id']));
$parent = $st->fetch(PDO::FETCH_ASSOC);
// a reply's level is one greater than its parent's
$clean['level'] = $parent['level'] + 1;
// what is the biggest thread_pos in this thread among messages with the same parent, recursively?
$clean['thread_pos'] = $this->biggestThreadPos($parent['thread_id'], $clean['parent_id']);
if ($clean['thread_pos']) {
// this thread_pos goes after the biggest existing one
$clean['thread_pos']++;
} else {
// this is the first reply, so put it right after the parent
$clean['thread_pos'] = $parent['thread_pos'] + 1;
}
// increment the thread_pos of all messages in the thread that come after this one
$st = $this->db->prepare("UPDATE {$this->db_table} SET thread_pos = thread_pos + 1 WHERE thread_id = ? AND thread_pos >= ? ");
$st->execute(array($parent['thread_id'], $clean['thread_pos']));
// the new message should be saved with the parent's thread_id
$clean['thread_id'] = $parent['thread_id'];
} else {
// The message is not a reply, so it's the start of a new thread
$clean['level'] = 0;
$clean['thread_pos'] = 0;
$clean['thread_id'] = $this->db->query("SELECT MAX(thread_id) + 1 FROM {$this->db_table} ")->fetchColumn(0);
}
// Sanity check
if (!$clean['thread_id']) {
$clean['thread_id'] = 1;
}
// Insert the message
$query = suxDB::prepareInsertQuery($this->db_table, $clean);
}
$st = $this->db->prepare($query);
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if ($this->db_driver == 'pgsql') {
if (isset($clean['id'])) {
$st->bindParam(':id', $clean['id'], PDO::PARAM_INT);
} else {
$st->bindParam(':thread_id', $clean['thread_id'], PDO::PARAM_INT);
$st->bindParam(':level', $clean['level'], PDO::PARAM_INT);
$st->bindParam(':thread_pos', $clean['thread_pos'], PDO::PARAM_INT);
}
if (isset($clean['users_id'])) {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
}
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->bindParam(':parent_id', $clean['parent_id'], PDO::PARAM_INT);
if (isset($clean['image'])) {
$st->bindParam(':image', $clean['image'], PDO::PARAM_STR);
}
if (isset($clean['published_on'])) {
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
}
$st->bindParam(':forum', $clean['forum'], PDO::PARAM_BOOL);
$st->bindParam(':blog', $clean['blog'], PDO::PARAM_BOOL);
$st->bindParam(':wiki', $clean['wiki'], PDO::PARAM_BOOL);
$st->bindParam(':slideshow', $clean['slideshow'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
// MySQL InnoDB with transaction reports the last insert id as 0 after
// commit, the real ids are only reported before committing.
if (isset($clean['id'])) {
$insert_id = $clean['id'];
} elseif ($this->db_driver == 'pgsql') {
$insert_id = $this->db->lastInsertId("{$this->db_table}_id_seq");
} else {
$insert_id = $this->db->lastInsertId();
}
// Commit
suxDB::commitTransaction($tid);
$this->inTransaction = false;
return $insert_id;
}
