public function jsapiSignature($url)
{
$noncestr = generateUUID();
$timestamp = time();
$jsapiTicketData = $this->jsapiTicket();
$signatureData = array();
$signatureData["noncestr"] = $noncestr;
$signatureData["timestamp"] = $timestamp;
$signatureData["url"] = $url;
$signatureData["jsapi_ticket"] = $jsapiTicketData["ticket"];
ksort($signatureData);
$signatureStr = urldecode(http_build_query($signatureData));
$signature = sha1($signatureStr);
$signatureData["signature"] = $signature;
$signatureData["appId"] = $this->wxAppID;
return $signatureData;
}
function generateUUID($content)
{
return generateUUID(serialize($content));
}
public function getUUID()
{
if (substr($this->nodeid, 10) == 'urn:uuid:') {
return $this->nodeid;
} else {
return 'urn:uuid:' . generateUUID($this->nodeid);
}
}
$username = _post('yttusername');
$password = _post('yttpassword');
$email = _post('yttemail');
$role = (int) _post('yttrole');
// check input
if (empty($username) || empty($password) || empty($email) || !in_array($role, array(1, 2, 3))) {
jsonExit(array('error' => 1));
// data invalid
}
$result = $db->dq("SELECT * FROM {$db->prefix}users WHERE username = ?", array($username));
$row = $result->fetch_assoc();
if ($result && is_array($row) && count($row) > 0) {
jsonExit(array('error' => 2));
// username already exists
}
$uuid = generateUUID();
$db->dq("INSERT INTO {$db->prefix}users (uuid,username,password,email,d_created,role) VALUES(?,?,?,?,?,?)", array($uuid, $username, hashPassword($password, $uuid), $email, time(), $role));
$id = $db->last_insert_id($db->prefix . 'users');
if ($id > 0) {
jsonExit(array('error' => 0));
// everything is fine
} else {
jsonExit(array('error' => 3));
// error creating user
}
} elseif (isset($_GET['edituser'])) {
check_admin_access();
stop_gpc($_POST);
$username = _post('yttusername');
$password = _post('yttpassword');
$email = _post('yttemail');
function update_131_14($db, $dbtype)
{
$db->ex("BEGIN");
if ($dbtype == 'mysql') {
$db->ex("DROP TABLE {$db->prefix}tags");
$db->ex("CREATE TABLE {$db->prefix}tags (\r\n\t\t\t `id` INT UNSIGNED NOT NULL auto_increment,\r\n\t\t\t `name` VARCHAR(50) NOT NULL,\r\n\t\t\t PRIMARY KEY(`id`),\r\n\t\t\t UNIQUE KEY `name` (`name`)\r\n\t\t\t) CHARSET=utf8 ");
$db->ex("ALTER TABLE {$db->prefix}todolist CHANGE `tags` `tags` VARCHAR(600) NOT NULL default ''");
$db->ex("ALTER TABLE {$db->prefix}todolist ADD `tags_ids` VARCHAR(250) NOT NULL default ''");
$db->ex("ALTER TABLE {$db->prefix}todolist ADD `uuid` CHAR(36) NOT NULL default ''");
$db->ex("ALTER TABLE {$db->prefix}todolist ADD `d_edited` INT UNSIGNED NOT NULL default 0");
$db->ex("ALTER TABLE {$db->prefix}tag2task ADD `list_id` INT UNSIGNED NOT NULL");
$db->ex("ALTER TABLE {$db->prefix}tag2task ADD KEY(`list_id`)");
$db->ex("ALTER TABLE {$db->prefix}lists ADD `uuid` CHAR(36) NOT NULL default ''");
$db->ex("ALTER TABLE {$db->prefix}lists ADD `d_edited` INT UNSIGNED NOT NULL default 0");
} else {
# changes in tags table: fully new
$db->ex("DROP TABLE {$db->prefix}tags");
//index will be deleted too
$db->ex("CREATE TABLE {$db->prefix}tags (\r\n\t\t\t\t id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n\t\t\t\t name VARCHAR(50) NOT NULL COLLATE NOCASE\r\n\t\t\t\t) ");
$db->ex("CREATE UNIQUE INDEX tags_name ON {$db->prefix}tags (name COLLATE NOCASE)");
# changes in todolist table: uuid, d_edited, tags, tags_ids
$db->ex("CREATE TABLE todolist_new (\r\n\t\t\t\t id INTEGER PRIMARY KEY,\r\n\t\t\t\t uuid CHAR(36) NOT NULL default '',\r\n\t\t\t\t list_id INTEGER UNSIGNED NOT NULL default 0,\r\n\t\t\t\t d_created INTEGER UNSIGNED NOT NULL default 0,\r\n\t\t\t\t d_completed INTEGER UNSIGNED NOT NULL default 0,\r\n\t\t\t\t d_edited INTEGER UNSIGNED NOT NULL default 0,\r\n\t\t\t\t compl TINYINT UNSIGNED NOT NULL default 0,\r\n\t\t\t\t title VARCHAR(250) NOT NULL,\r\n\t\t\t\t note TEXT,\r\n\t\t\t\t prio TINYINT NOT NULL default 0,\r\n\t\t\t\t ow INTEGER NOT NULL default 0,\r\n\t\t\t\t tags VARCHAR(600) NOT NULL default '',\r\n\t\t\t\t tags_ids VARCHAR(250) NOT NULL default '',\r\n\t\t\t\t duedate DATE default NULL\r\n\t\t\t\t) ");
$db->ex("INSERT INTO todolist_new (id,list_id,d_created,d_completed,compl,title,note,prio,ow,tags,duedate)" . " SELECT id,list_id,d_created,d_completed,compl,title,note,prio,ow,tags,duedate FROM {$db->prefix}todolist");
$db->ex("DROP TABLE {$db->prefix}todolist");
$db->ex("ALTER TABLE todolist_new RENAME TO {$db->prefix}todolist");
$db->ex("CREATE INDEX todo_list_id ON {$db->prefix}todolist (list_id)");
#1st index of 2
# changes in tag2task table: new column and index, new names of indexes
$db->ex("ALTER TABLE {$db->prefix}tag2task ADD list_id INTEGER NOT NULL default 0");
$db->ex("DROP INDEX tag_id");
$db->ex("DROP INDEX task_id ");
$db->ex("CREATE INDEX tag2task_tag_id ON {$db->prefix}tag2task (tag_id)");
$db->ex("CREATE INDEX tag2task_task_id ON {$db->prefix}tag2task (task_id)");
$db->ex("CREATE INDEX tag2task_list_id ON {$db->prefix}tag2task (list_id)");
# changes in lists table: uuid, d_edited
$db->ex("ALTER TABLE {$db->prefix}lists ADD uuid CHAR(36) NOT NULL default ''");
$db->ex("ALTER TABLE {$db->prefix}lists ADD d_edited INTEGER UNSIGNED NOT NULL default 0");
}
# recreate tags
$db->ex("DELETE FROM {$db->prefix}tag2task");
$q = $db->dq("SELECT id,list_id,tags FROM {$db->prefix}todolist WHERE tags != ''");
$ar = array();
while ($r = $q->fetch_assoc()) {
$ar[] = $r;
}
foreach ($ar as $r) {
$aTags = v14_prepareTags($r['tags']);
if ($aTags) {
v14_addTaskTags($r['id'], $aTags['ids'], $r['list_id']);
$db->ex("UPDATE {$db->prefix}todolist SET tags=?,tags_ids=? WHERE id=" . $r['id'], array(implode(',', $aTags['tags']), implode(',', $aTags['ids'])));
}
}
# fix bug with empty lists.d_created
$db->ex("UPDATE {$db->prefix}lists SET d_created=?", time());
# init d_edited
$db->ex("UPDATE {$db->prefix}todolist SET d_edited=d_created");
$db->ex("UPDATE {$db->prefix}todolist SET d_edited=d_completed WHERE d_completed > d_edited");
$db->ex("UPDATE {$db->prefix}lists SET d_edited=d_created");
# add UUID
$q = $db->dq("SELECT id FROM {$db->prefix}todolist");
$ar = array();
while ($r = $q->fetch_assoc()) {
$ar[] = $r;
}
foreach ($ar as $r) {
$db->ex("UPDATE {$db->prefix}todolist SET uuid=? WHERE id=" . $r['id'], array(generateUUID()));
}
$q = $db->dq("SELECT id FROM {$db->prefix}lists");
$ar = array();
while ($r = $q->fetch_assoc()) {
$ar[] = $r;
}
foreach ($ar as $r) {
$db->ex("UPDATE {$db->prefix}lists SET uuid=? WHERE id=" . $r['id'], array(generateUUID()));
}
# create unique indexes for UUID
if ($dbtype == 'mysql') {
$db->ex("ALTER TABLE {$db->prefix}lists ADD UNIQUE KEY (`uuid`)");
$db->ex("ALTER TABLE {$db->prefix}todolist ADD UNIQUE KEY (`uuid`)");
} else {
$db->ex("CREATE UNIQUE INDEX lists_uuid ON {$db->prefix}lists (uuid)");
$db->ex("CREATE UNIQUE INDEX todo_uuid ON {$db->prefix}todolist (uuid)");
}
$db->ex("COMMIT");
}
$grades = $count;
}
$step = ($qmax - $qmin) / $grades;
foreach ($at as $i => $tag) {
$t['cloud'][] = array('tag' => htmlarray($tag['name']), 'id' => (int) $tag['id'], 'w' => tag_size($qmin, $ac[$i], $step));
}
$t['total'] = $count;
jsonExit($t);
} elseif (isset($_GET['addList'])) {
check_write_access();
stop_gpc($_POST);
$t = array();
$t['total'] = 0;
$name = str_replace(array('"', "'", '<', '>', '&'), array('', '', '', '', ''), trim(_post('name')));
$ow = 1 + (int) $db->sq("SELECT MAX(ow) FROM {$db->prefix}lists");
$db->dq("INSERT INTO {$db->prefix}lists (uuid,name,ow,d_created,d_edited) VALUES (?,?,?,?,?)", array(generateUUID(), $name, $ow, time(), time()));
$id = $db->last_insert_id();
$t['total'] = 1;
$r = $db->sqa("SELECT * FROM {$db->prefix}lists WHERE id={$id}");
$t['list'][] = prepareList($r);
jsonExit($t);
} elseif (isset($_GET['renameList'])) {
check_write_access();
stop_gpc($_POST);
$t = array();
$t['total'] = 0;
$id = (int) _post('list');
$name = str_replace(array('"', "'", '<', '>', '&'), array('', '', '', '', ''), trim(_post('name')));
$db->dq("UPDATE {$db->prefix}lists SET name=?,d_edited=? WHERE id={$id}", array($name, time()));
$t['total'] = $db->affected();
$r = $db->sqa("SELECT * FROM {$db->prefix}lists WHERE id={$id}");
function update_14_15($db, $dbtype)
{
$db->ex("BEGIN");
if ($dbtype == 'mysql') {
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}users (\r\n\t\t\t id int(10) unsigned NOT NULL auto_increment,\r\n\t\t\t uuid varchar(36) NOT NULL,\r\n\t\t\t username varchar(50) NOT NULL,\r\n\t\t\t password varchar(32) NOT NULL,\r\n\t\t\t email varchar(100) NOT NULL,\r\n\t\t\t d_created int(10) unsigned NOT NULL,\r\n\t\t\t role enum('1','2','3') NOT NULL default '3',\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) CHARSET=utf8 ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}notifications (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t creator_user_id int(11) NOT NULL,\r\n\t\t\t text varchar(255) NOT NULL,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t shown tinyint(1) NOT NULL default '0',\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}notification_listeners (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t type set('task','list','global') character set utf8 NOT NULL,\r\n\t\t\t value int(11) default NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}comments (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t task_id int(11) NOT NULL,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t comment varchar(255) NOT NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}time_tracker (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t task_id int(11) NOT NULL,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t minutes int(11) NOT NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
} else {
if ($dbtype == 'postgres') {
$db->ex("CREATE TABLE {$db->prefix}users (\r\n\t\t\t\t\tid integer NOT NULL,\r\n\t\t\t\t\tuuid character varying(36),\r\n\t\t\t\t\tusername character varying(50),\r\n\t\t\t\t\t\"password\" character varying(32),\r\n\t\t\t\t\temail character varying(100),\r\n\t\t\t\t\td_created integer,\r\n\t\t\t\t\t\"role\" integer\r\n\t\t\t\t);\r\n\t\t\t\tCREATE SEQUENCE {$db->prefix}users_id_seq\r\n\t\t\t\t\tINCREMENT BY 1\r\n\t\t\t\t\tNO MAXVALUE\r\n\t\t\t\t\tNO MINVALUE\r\n\t\t\t\t\tCACHE 1;\r\n\t\t\t\tALTER SEQUENCE {$db->prefix}users_id_seq OWNED BY {$db->prefix}users.id;\r\n\t\t\t\tSELECT pg_catalog.setval('{$db->prefix}users_id_seq', 1, true);\r\n\t\t\t\tALTER TABLE {$db->prefix}users ALTER COLUMN id SET DEFAULT nextval('{$db->prefix}users_id_seq'::regclass);\r\n\t\t\t\tALTER TABLE ONLY {$db->prefix}users\r\n \t\t\t\tADD CONSTRAINT {$db->prefix}users_pkey PRIMARY KEY (id);\r\n\t\t\t\t");
// Using || to concatenate in YTT is not recommeneded because there are
// database drivers for YTT that do not support the syntax, however
// they do support CONCAT(item1, item2) which we can replicate in
// PostgreSQL. PostgreSQL requires the function to be defined for each
// different argument variation the function can handle.
$db->ex('CREATE OR REPLACE FUNCTION "concat"(anynonarray, anynonarray) RETURNS text AS \'SELECT CAST($1 AS text) || CAST($2 AS text);\' LANGUAGE \'sql\'');
$db->ex('CREATE OR REPLACE FUNCTION "concat"(text, anynonarray) RETURNS text AS \'SELECT $1 || CAST($2 AS text);\' LANGUAGE \'sql\'');
$db->ex('CREATE OR REPLACE FUNCTION "concat"(anynonarray, text) RETURNS text AS \'SELECT CAST($1 AS text) || $2;\' LANGUAGE \'sql\'');
$db->ex('CREATE OR REPLACE FUNCTION "concat"(text, text) RETURNS text AS \'SELECT $1 || $2;\' LANGUAGE \'sql\'');
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}notifications (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t creator_user_id int(11) NOT NULL,\r\n\t\t\t text varchar(255) NOT NULL,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t shown tinyint(1) NOT NULL default '0',\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}notification_listeners (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t type set('task','list','global') character set utf8 NOT NULL,\r\n\t\t\t value int(11) default NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}comments (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t task_id int(11) NOT NULL,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t comment varchar(255) NOT NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
$db->ex("CREATE TABLE IF NOT EXISTS {$db->prefix}time_tracker (\r\n\t\t\t id int(11) NOT NULL auto_increment,\r\n\t\t\t created timestamp NOT NULL default CURRENT_TIMESTAMP,\r\n\t\t\t task_id int(11) NOT NULL,\r\n\t\t\t user_id int(11) NOT NULL,\r\n\t\t\t minutes int(11) NOT NULL,\r\n\t\t\t PRIMARY KEY (id)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ");
} else {
$db->ex('CREATE TABLE ' . $db->prefix . 'users ("id" INTEGER PRIMARY KEY NOT NULL , "uuid" VARCHAR, "username" VARCHAR, "password" VARCHAR, "email" VARCHAR, "d_created" INTEGER, "role" INTEGER)');
$db->ex('CREATE TABLE "' . $db->prefix . 'notifications" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "user_id" INTEGER, "creator_user_id" INTEGER, "text" VARCHAR, "created" DATETIME DEFAULT CURRENT_TIMESTAMP, "shown" INTEGER)');
$db->ex('CREATE TABLE "' . $db->prefix . 'notification_listeners" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "user_id" INTEGER, "type" VARCHAR, "value" INTEGER)');
$db->ex('CREATE TABLE "' . $db->prefix . 'comments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "task_id" INTEGER, "user_id" INTEGER, "created" DATETIME DEFAULT CURRENT_TIMESTAMP, "comment" TEXT)');
$db->ex('CREATE TABLE "' . $db->prefix . 'time_tracker" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "created" DATETIME, "task_id" INTEGER, "user_id" INTEGER, "minutes" INTEGER)');
}
}
// create default user - for multi user support
$uuid = generateUUID();
$db->ex("INSERT INTO {$db->prefix}users (id, uuid, username, password, email, d_created, role) VALUES (1, '" . $uuid . "', 'admin', '" . hashPassword('admin', $uuid) . "', '*****@*****.**', " . time() . ", '1')");
$db->ex("COMMIT");
}
function addTask($db, $listId, $title, $tag, $note = null, $priority = null, $duedate = null, $tags = null)
{
$t = array();
$t['total'] = 0;
$title = trim($title);
if ($title == '') {
return $t;
}
if ($note) {
$note = str_replace("\r\n", "\n", trim($note));
} else {
$note = "";
}
$duedate = parse_duedate(trim($duedate));
$prio = 0;
if ($tags) {
$tags = trim($tags);
} else {
$tags = '';
}
if (Config::get('smartsyntax') != 0) {
$a = parse_smartsyntax($title);
if ($a === false) {
jsonExit($t);
}
$title = $a['title'];
$prio = $a['prio'];
$tags = ($tags ? $tags . "," : "") . $a['tags'];
}
if ($priority) {
$prio = (int) $priority;
}
if ($prio < -1) {
$prio = -1;
} elseif ($prio > 2) {
$prio = 2;
}
if (Config::get('autotag')) {
$tags .= ',' . _post('tag');
}
$ow = 1 + (int) $db->sq("SELECT MAX(ow) FROM {$db->prefix}todolist WHERE list_id={$listId} AND compl=0");
$db->ex("BEGIN");
$db->dq("INSERT INTO {$db->prefix}todolist (uuid,list_id,title,d_created,d_edited,ow,prio,note,duedate) VALUES(?,?,?,?,?,?,?,?,?)", array(generateUUID(), $listId, $title, time(), time(), $ow, $prio, $note, $duedate));
$id = $db->last_insert_id();
if ($tags != '') {
$aTags = prepareTags($tags);
if ($aTags) {
addTaskTags($id, $aTags['ids'], $listId);
$db->ex("UPDATE {$db->prefix}todolist SET tags=?,tags_ids=? WHERE id={$id}", array(implode(',', $aTags['tags']), implode(',', $aTags['ids'])));
}
}
$db->ex("COMMIT");
$r = $db->sqa("SELECT * FROM {$db->prefix}todolist WHERE id={$id}");
$t['list'][] = prepareTaskRow($r, loadLists($db, ''));
$t['total'] = 1;
return $t;
}
<?php require_once '../config.inc.php'; require_once DIR_INCLUDE . '/common.inc.php'; // make sure the playerUUID isn't cached sendXMLHeaders(); $playerIP = getIP(); $playerHost = getHost($playerIP); // // TODO: Check ip etc. within last X minutes/seconds to prevent abuse. // If a player UUID was generated in this time, automatically return the relevant // existing ID. // // new player $playerUUID = generateUUID(); $qryPlayer = 'INSERT INTO players ( id, uuid, joined, ip, host ) VALUES( \'\', \'' . dbEscape($playerUUID) . '\', NOW(), \'' . dbEscape($playerIP) . '\', \'' . dbEscape($playerHost) . '\' )'; $resPlayer = mysql_query($qryPlayer);
