def createIcon(self, icon): """ Creates icon for user based on icon sent on create form TODO: This should be on MySQLUserStore """ # FIXME: Move user icon into filesystem for better performance, similar to project icon self.icon = None if isinstance(icon, unicode) or not icon.filename: return content_type = icon.type with admin_transaction() as cursor: try: cursor.execute("INSERT INTO user_icon VALUES(null, '" + safe_string( icon.value) + "', '" + safe_string(content_type) + "')") # Resolve last inserted icon id cursor.execute("SELECT last_insert_id() FROM user_icon") row = cursor.fetchone() if row: if row[0] != 0: # If nonzero is returned, row was successfully added self.icon = row[0] except: conf.log.exception("Exception. Failed creating icon.") raise
def get_user_tasks(self, username): """ Method for querying users tasks in a specific project context Gives url to ticket and it's summary """ env_url = conf.getEnvironmentUrl(self.env_name) + "/ticket/" # Base query query = ( "SELECT concat('%(env_url)s', tc.id) AS url, tc.summary, tc.description, tc.priority, tc.time, " "`enum`.`value` FROM `%(project)s`.ticket AS tc " "INNER JOIN `%(project)s`.`enum` ON `enum`.`name` = tc.priority AND `enum`.`type` = 'priority' " "WHERE tc.owner = '%(user)s' AND tc.status <> 'closed'" % { 'project': self.env_name, 'env_url': safe_string(env_url), 'user': safe_string(username) }) # Retrieve data rows = [] with admin_query() as cursor: try: cursor.execute(query) rows = cursor.fetchall() except: conf.log.exception( "Exception. Project.get_user_tasks query failed. '''%s'''" % query) return rows
def get_projects_with_rights(self, username, action): """ :returns: a list of projects where user have right for "action". .. note:: Permissions coming via LDAP groups are not included in the results """ user = get_userstore().getUser(username) # Get subjects subjects = set([username]) subjects.update(get_special_users(username)) # Surround string elements with ' and join them with comma actions_str = ','.join("'%s'" % safe_string(p) for p in [action, 'TRAC_ADMIN']) subjects_str = ','.join(["'{0}'".format(safe_string(subject)) for subject in subjects]) organizations_str = ','.join(["{0}".format(safe_int(org_key)) for org_key in user.organization_keys]) query = ("SELECT DISTINCT projects.* FROM projects " "INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key " "INNER JOIN group_permission ON group_permission.group_key = group.group_id " "INNER JOIN action ON group_permission.permission_key = action.action_id " "LEFT JOIN user_group ON user_group.group_key = group.group_id " "LEFT JOIN user ON user.user_id = user_group.user_key " "LEFT JOIN organization_group ON organization_group.group_key = group.group_id " "WHERE (user.username IN(%s) " "OR organization_group.organization_key IN(%s)) " "AND action.action_string IN(%s) " "ORDER BY projects.project_name" % (subjects_str, organizations_str, actions_str)) return self.queryProjectObjects(query)
def _list_users(self, req): """ Process the ajax request for fetching users from database. Require at least two characters in the name string before allowing any values. Disallow % character and allow _ only as a normal character, that is part of the username. """ name = req.args.get('q', '') auth = safe_string(req.args.get('auth', '').lower()) perm = safe_string(req.args.get('perm', '').upper()) limit = safe_int(req.args.get('limit', '30')) status = safe_string(req.args.get('status', '').lower()) raw_fields = safe_string(req.args.get('field', 'username').lower()) fields = [field for field in raw_fields.split(',') if field.strip() in self.db_fields.keys()] # If no fields or % in query => not allowed if not fields or '%' in name: return send_json(req, '', status=403) # Allow underscore in names/query name = safe_string(name).replace('_', '\_') states = [stat.lower() for stat in status.split(',') if stat] # Do the query rows = self._query_users(req, query=name, fields=fields, states=states, auth=auth, perm=perm, limit=limit) # Construct response in JSON list format # Serialize datetime objects into iso format: 2012-05-15T09:43:14 return send_json(req, rows)
def get_all_users(self, limit=0, count=50, initial=None): """ List all users If no parameters given, lists first 50 users. If limit given, lists first 50 users from the limit. If initial given, lists only users having that initial. """ query = "SELECT username, givenName, lastName, mail, mobile FROM user " query += "WHERE username NOT IN ('authenticated', 'anonymous') " if initial: query += "AND (username LIKE '" + safe_string(initial[0].upper()) + "%' " query += "OR username LIKE '" + safe_string(initial[0].lower()) + "%') " query += "ORDER BY username LIMIT %d,%d" % (safe_int(limit), safe_int(count)) users = [] with admin_query() as cursor: try: cursor.execute(query) for user in cursor: s = {'username': user[0], 'first': user[1], 'last': user[2], 'email': user[3], 'mobile': user[4]} users.append(s) except: conf.log.exception("Exception. Users.get_all_users query failed '''%s'''." % query) raise return users
def get_user_tasks(self, username): """ Method for querying users tasks in a specific project context Gives url to ticket and it's summary """ env_url = conf.getEnvironmentUrl(self.env_name) + "/ticket/" # Base query query = ("SELECT concat('%(env_url)s', tc.id) AS url, tc.summary, tc.description, tc.priority, tc.time, " "`enum`.`value` FROM `%(project)s`.ticket AS tc " "INNER JOIN `%(project)s`.`enum` ON `enum`.`name` = tc.priority AND `enum`.`type` = 'priority' " "WHERE tc.owner = '%(user)s' AND tc.status <> 'closed'" % {'project': self.env_name, 'env_url': safe_string(env_url), 'user': safe_string(username)}) # Retrieve data rows = [] with admin_query() as cursor: try: cursor.execute(query) rows = cursor.fetchall() except: conf.log.exception("Exception. Project.get_user_tasks query failed. '''%s'''" % query) return rows
def get_projects_with_params(self, username, perm, namelike=None, categories=None): """ Returns a list of projects where user have right for "action". """ categories = categories or [] user = get_userstore().getUser(username) user_organization = user.organization_keys # Get subjects subjects = set([username]) subjects.update(get_special_users(username)) # Construct comma separated lists for queries actions_str = ','.join("'%s'" % safe_string(p) for p in [perm, 'TRAC_ADMIN']) subjects_str = ','.join("'%s'" % safe_string(subject) for subject in subjects) categories_str = ','.join("'%s'" % safe_string(cat) for cat in categories) query = ("SELECT DISTINCT projects.* FROM projects " "INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key " "INNER JOIN group_permission ON group_permission.group_key = group.group_id " "INNER JOIN action ON group_permission.permission_key = action.action_id " "LEFT JOIN user_group ON user_group.group_key = group.group_id " "LEFT JOIN user ON user.user_id = user_group.user_key ") if categories: query += ("INNER JOIN project_categories ON projects.project_id = project_categories.project_key " "INNER JOIN categories ON categories.category_id = project_categories.category_key ") query += ("LEFT JOIN organization_group ON organization_group.group_key = group.group_id " "WHERE (user.username IN (%s) " % subjects_str) if not user_organization: query += "OR organization_group.organization_key = NULL )" else: # List user organizations as safe int, separated with comma: (1,5,3,65) orc = lambda org_key: str(safe_int(org_key)) query += "OR organization_group.organization_key IN (%s) ) " % ','.join(map(orc, user_organization)) query += "AND action.action_string IN(" + actions_str + ") " if categories: query += "AND categories.category_name IN(" + categories_str + ") " if namelike: conditions = [] search_strs = namelike.split(' ') for search in search_strs: if not search == '': search = safe_string(search) conditions.append("projects.project_name LIKE '%" + search + "%'") where = " OR ".join(conditions) query += "AND (" + where + ") " query += "ORDER BY projects.project_name" return self.queryProjectObjects(query)
def updateUserPreferences(self, user): with admin_transaction() as cursor: try: for item, value in user.preferences.iteritems(): query = "INSERT INTO user_preference (user_key, item, value) VALUES (" query += (str(safe_int(user.id)) + ", '" + safe_string(item) + "', '" + safe_string(str(value)) + "') ") query += "ON DUPLICATE KEY UPDATE value = '" + safe_string(str(value)) + "'" cursor.execute(query) except: conf.log.exception("Exception. update of user preferences failed '''%s'''." % query) raise return True
def get_user_task_sums(self, username): """ Method for querying user task sums (total tickets and closed tickets) """ # Build query query = ("SELECT tc.status, COUNT(*) " "FROM `{0}`.ticket AS tc " "WHERE tc.owner = %s " "GROUP BY tc.status").format(safe_string(self.env_name)) # Retrieve data rows = [] with admin_query() as cursor: try: cursor.execute(query, username) rows = cursor.fetchall() except: conf.log.exception( "Exception. Project.get_user_task_sums query failed. '''%s'''" % query) # go through tasks total = 0 closed = 0 for row in rows: if row[0] == 'closed': closed = row[1] total += row[1] return total, closed
def _parse_args(self, args, content): """ Parse args from incoming args or from content. Depending on which is set. If called as a macro, the content holds the arguments. Otherwise it's the args param that holds the arguments. In macro case, a tuple is returned, first element is list of arguments, second is a dict. We only support the dict format. """ env_name = "" count = 0 title = "Latest announcements" if args is None: args = parse_args(content) if len(args) > 1: args = args[1] if args is not None and "project" in args: env_name = args["project"] env_name = env_name.strip("\" '") env_name = env_name.encode("utf-8") env_name = safe_string(env_name) if args is not None and "count" in args: count = args["count"] count = safe_int(count) if args is not None and "title" in args: title = args["title"] title = title.strip('"') self.log.debug("Returning args: {0}".format((env_name, count, title))) return env_name, count, title
def _parse_args(self, args, content): """ Parse args from incoming args or from content. Depending on which is set. If called as a macro, the content holds the arguments. Otherwise it's the args param that holds the arguments. In macro case, a tuple is returned, first element is list of arguments, second is a dict. We only support the dict format. """ env_name = '' count = 0 title = 'Latest announcements' if args is None: args = parse_args(content) if len(args) > 1: args = args[1] if args is not None and 'project' in args: env_name = args['project'] env_name = env_name.strip('" \'') env_name = env_name.encode('utf-8') env_name = safe_string(env_name) if args is not None and 'count' in args: count = args['count'] count = safe_int(count) if args is not None and 'title' in args: title = args['title'] title = title.strip('"') self.log.debug("Returning args: {0}".format((env_name, count, title))) return env_name, count, title
def update_featured_projects(self, projects): """ update featured projects """ with admin_transaction() as cursor: try: # First cleanup selected projects query = "DELETE FROM project_selected" cursor.execute(query) # Then update new projects if len(projects) > 0: query = "INSERT INTO project_selected (project_id,value) VALUES " line = "((SELECT projects.project_id FROM projects WHERE environment_name = '%s'), %d)" lines = [] for project, value in projects: lines.append(line % (safe_string(project), safe_int(value))) query += ",".join(lines) cursor.execute(query) except: conf.log.exception("Update featured project transaction failed %s" % query) raise
def from_operational(self, project_identifier, forum_id): # Alternative way to do this would be to open the connection straight into the project database... query = """ SELECT id, name, author, moderators, subscribers, subject, description FROM %(project_identifier)s.forum WHERE id = %(forum_id)s """ % {'project_identifier': safe_string(project_identifier), 'forum_id': safe_int(forum_id)} dibo = None with admin_query() as cursor: try: cursor.execute(query) row = cursor.fetchone() if not row: return None dibo = {'forum_key':row[0], 'discussion_name':row[1], 'author':row[2], 'moderators':row[3], 'subscribers':row[4], 'subject':row[5], 'description':row[6] } except: conf.log.exception("Failed reading a record from discussion dimension. %s" % str(dibo)) pd = ProjectDimension() project = pd.from_operational(project_identifier) dibo['project_key'] = project['project_key'] dibo['project_identifier'] = project['identifier'] dibo['project_name'] = project['project_name'] return dibo
def get_participated_public_projects(self, username): """ Get public projects username has participated in """ store = get_userstore() user = store.getUser(username) if not user: return [] anon = store.getUser('anonymous') order = " ORDER BY projects.project_name" # We need projects where _both_ anonymous and the specified user exist query = """ SELECT projects.environment_name AS name, projects.description AS description, projects.created AS date, '%(user_name)s' AS author, projects.project_name, projects.icon_name FROM projects INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key INNER JOIN user_group ON user_group.group_key = group.group_id INNER JOIN `user` ON user_group.user_key = user.user_id WHERE user_group.user_key = %(user_id)d AND EXISTS (SELECT * FROM projects P INNER JOIN `group` ON `group`.trac_environment_key = P.trac_environment_key INNER JOIN user_group ON user_group.group_key = group.group_id WHERE user_group.user_key = %(anon_id)d AND projects.project_id = P.project_id) """ % {"user_name": safe_string(user.getDisplayName().encode('utf-8')), "user_id": safe_int(user.id), "anon_id": safe_int(anon.id)} query += order return self.__queryProjectsWithDescr(query)
def get_user_task_sums(self, username): """ Method for querying user task sums (total tickets and closed tickets) """ # Build query query = ("SELECT tc.status, COUNT(*) " "FROM `{0}`.ticket AS tc " "WHERE tc.owner = %s " "GROUP BY tc.status").format(safe_string(self.env_name)) # Retrieve data rows = [] with admin_query() as cursor: try: cursor.execute(query, username) rows = cursor.fetchall() except: conf.log.exception("Exception. Project.get_user_task_sums query failed. '''%s'''" % query) # go through tasks total = 0 closed = 0 for row in rows: if row[0] == 'closed': closed = row[1] total += row[1] return total, closed
def from_analytical(self, project_identifier, forum_id): """ Return user from analytical database """ query = """ SELECT forum_key, discussion_name, author, moderators, subscribers, subject, description, project_key, project_identifier, project_name FROM discussion_dim WHERE project_identifier = '%s' AND forum_key = %d AND VALID_TO IS NULL""" % (safe_string(project_identifier), safe_int(forum_id)) row = [] with analytical_query() as cursor: try: cursor.execute(query) row = cursor.fetchone() except: conf.log.exception( "Failed reading discussion forum from analytical db. {project: %s, forum: %d}" % (project_identifier, forum_id)) if not row: return None project = {'forum_key': row[0], 'discussion_name': MySQLdb.escape_string(row[1]), 'author': MySQLdb.escape_string(row[2]), 'moderators': MySQLdb.escape_string(row[3]), 'subscribers': MySQLdb.escape_string(row[4]), 'subject': MySQLdb.escape_string(row[5]), 'description': MySQLdb.escape_string(row[6]), 'project_key': row[7], 'project_identifier': MySQLdb.escape_string(row[8]), 'project_name': MySQLdb.escape_string(row[9]) } return project
def _get_posts(self, user, projects): #project_name, subject, body, createtime """ Get posts """ posts = [] query = None if self.env.project_identifier == "home": return posts with trac_db_query(self.env) as cursor: for prj in projects: query = ("SELECT id, forum, 0, time, subject, body FROM `%(dbname)s`.`topic` " "WHERE author = '%(user)s' " "UNION ALL " "SELECT m.id, m.forum, m.topic, m.time, t.subject, m.body FROM " "`%(dbname)s`.`message` m, `%(dbname)s`.`topic` t " "WHERE m.author = '%(user)s' AND m.topic = t.id" % {'dbname': safe_string(prj.env_name), 'user': safe_string(user)}) try: cursor.execute(query) for row in cursor: posts.append({ 'project': prj, 'id': row[0], 'forum_id': row[1], 'topic_id': row[2], 'time': to_datetime(row[3]), 'subject': unicode((row[4] != '') and row[4] or '<no subject>'), 'body': unicode(row[5]), 'type': (row[2] == 0) and 'NEWTOPIC' or 'POST' }) except: self.log.exception( "MyProjectsModule._get_posts query failed for project %s with query: '''%s'''" % (prj.env_name, query)) import operator posts.sort(key = operator.itemgetter('time'), reverse = True) return posts
def undo(self): # Remove only if command was executed successfully if self.success: with admin_transaction() as cursor: try: # Remove database cursor.execute("DROP DATABASE `{0}`".format(safe_string(self.dbname))) except Exception: conf.log.exception('Failed to drop database: {0}'.format(self.dbname)) return False return True
def _do_org_update(self): """ Updates organizations from project.ini to database Organizations are in format:: org.<type>.<identifier> = <backend>, <organization> Where: - type: Organization type: auths |Â email - identifier: Identifier for organization. Id for auths and mail domain for email - backend: Authentication backend - organization: Organization name """ orgman = self.env[OrganizationManager] organizations = orgman.get_organizations() if not orgman.use_organizations: printout('Organizations are disabled. Nothing to do.') return if not organizations: printout( 'No organizations were found from configuration. Nothing to do.' ) return # Surround organizations with ' organizations_sql = [] for organization in sorted(organizations, key=lambda org: org['position']): organizations_sql.append("('{0}', {1})".format( safe_string(organization['name']), safe_int(organization['position']))) query = """ INSERT IGNORE INTO organization (organization_name, sorting) VALUES {0} ON DUPLICATE KEY UPDATE sorting=VALUES(sorting) """.format(','.join(organizations_sql)) with admin_transaction() as cursor: try: cursor.execute(query) except: logging.exception( "Organization update failed (conf -> db) with query %s" % query) printout('Failed to update organization info. Check the logs') return printout('Updated organization info into db successfully')
def update_last_login(username): """ Updates user's last login timestamp to user table """ if not username: return query = "UPDATE user SET last_login=NOW() WHERE user.username='******'" % safe_string(username) with admin_transaction() as cursor: try: cursor.execute(query) except: conf.log.exception("Query failed: %s" % query) raise
def _expire_cookie(self, req): """ Instruct the user agent to drop the auth cookie by setting the "expires" property to a date in the past. """ if req.incookie.has_key(COOKIE_AUTH): cache = UserCache.instance() sql_safe_cookie_value = safe_string(req.incookie[COOKIE_AUTH].value) cache.clearUserCookieName(sql_safe_cookie_value) # Remove cached cookie self.cookie.remove(sql_safe_cookie_value) # Create cookie by setting expiration to past self._set_outcookie(req, cookie='', expires=(datetime.utcnow() - timedelta(10000)))
def get_newest_participated_projects(self, username, projectcount): """ Get those projects that user with 'username' has participated ordered by newest first, limited by projectcount """ user = get_userstore().getUser(username) query = "SELECT projects.*, '" + safe_string( user.getDisplayName().encode('utf-8')) + "' FROM projects " query += "INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key " query += "INNER JOIN user_group ON user_group.group_key = group.group_id " query += "INNER JOIN user ON user_group.user_key = user.user_id " query += "WHERE user_group.user_key = %d " % safe_int(user.id) query += "ORDER BY projects.created DESC LIMIT %d" % safe_int(projectcount) return self.__queryProjects(query)
def do(self): # Create db (schema) with admin_transaction() as cursor: # Create database try: query = """ CREATE DATABASE `{0}` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin """.format(safe_string(self.dbname)) cursor.execute(query) except: conf.log.exception("Error occurred on creating database! %s" % query) return False self.success = True return True
def get_authored_projects(self, user): """ Get projects that are created by given user :param User user: User who is author of the project :returns: List of project objects """ query = """ SELECT projects.* FROM projects INNER JOIN user ON projects.author = user.user_id WHERE user.username = '******' ORDER BY project_name """.format(safe_string(user.username)) return self.queryProjectObjects(query)
def _do_org_update(self): """ Updates organizations from project.ini to database Organizations are in format:: org.<type>.<identifier> = <backend>, <organization> Where: - type: Organization type: auths |Â email - identifier: Identifier for organization. Id for auths and mail domain for email - backend: Authentication backend - organization: Organization name """ orgman = self.env[OrganizationManager] organizations = orgman.get_organizations() if not orgman.use_organizations: printout('Organizations are disabled. Nothing to do.') return if not organizations: printout('No organizations were found from configuration. Nothing to do.') return # Surround organizations with ' organizations_sql = [] for organization in sorted(organizations, key=lambda org: org['position']): organizations_sql.append("('{0}', {1})".format(safe_string(organization['name']), safe_int(organization['position']))) query = """ INSERT IGNORE INTO organization (organization_name, sorting) VALUES {0} ON DUPLICATE KEY UPDATE sorting=VALUES(sorting) """.format(','.join(organizations_sql)) with admin_transaction() as cursor: try: cursor.execute(query) except: logging.exception("Organization update failed (conf -> db) with query %s" % query) printout('Failed to update organization info. Check the logs') return printout('Updated organization info into db successfully')
def get_archived_project(self, project_archive_id): """ Get project from archive :param int project_archive_id: Id of the project archive :returns: The archived project if found (one), otherwise None """ query = """ SELECT * FROM project_archive WHERE project_archive_id = {0} LIMIT 1 """.format(safe_string(str(project_archive_id))) projects = self.query_archived_projects(query) if len(projects) == 1: return projects[0] return None
def get_recipients(self, resid): """ List all admin emails in to field """ cc = [] to = [] # Wrap and escape admins in quotes admins = CQDEPermissionStore(env=self.env).get_users_with_permissions(['TRAC_ADMIN']) recipients = ", ".join(["'%s'" % safe_string(admin) for admin in admins]) # Query for admin email addresses with admin_transaction() as cursor: query = "SELECT mail FROM user WHERE username IN ({0})".format(recipients) try: cursor.execute(query) to = [row[0] for row in cursor] except: self.env.log.exception("Exception. MembershipApi.get_recipients query failed. '''%s'''" % query) return to, cc
def get_nonbrowsable_context_ids(self): """ Return a list of those contexts that are not seen as categories by user but as a metadata related with project. Like Natural language, License, or Development status. """ cntx_in = ', '.join(["'%s'" % safe_string(ctx) for ctx in conf.non_browsable_contexts]) query = "SELECT context_id FROM contexts WHERE context_name IN (%s)" % cntx_in cntxts = [] with admin_query() as cursor: try: cursor.execute(query) for row in cursor: cntxts.append(row[0]) except: conf.log.exception("Getting non-browsable context id's failed with query '%s'" % query) return cntxts
def get_archived_project(self, project_archive_id): """ Get project from archive :param int project_archive_id: Id of the project archive :returns: The archived project if found (one), otherwise None """ query = """ SELECT * FROM project_archive WHERE project_archive_id = {0} LIMIT 1 """.format( safe_string(str(project_archive_id)) ) projects = self.query_archived_projects(query) if len(projects) == 1: return projects[0] return None
def search_project(self, keywords, category_ids, sub_page=1, limit=5): """ Search for projects with fulltext and categories """ limit = safe_int(limit) limit_attr = {'limit_start': (safe_int(sub_page) - 1) * limit, 'limit': limit} select = "SELECT DISTINCT p.project_id,p.project_name,p.environment_name,p.description,"\ "p.author,p.created,p.updated,p.published,p.parent_id,p.icon_name FROM "\ "(SELECT DISTINCT * , CONCAT(project_name COLLATE utf8_general_ci,environment_name, description) as f FROM projects) AS p " wheres = [] joins = [] if category_ids: # Given categories search_cat_list = category_ids joins += ["INNER JOIN project_categories AS pc ON pc.project_key = p.project_id "] # Join id:s into in clause wrapped in quotes id_list = ', '.join([str(safe_int(id)) for id in search_cat_list]) wheres += [" pc.category_key IN (%s)" % id_list] if keywords: # Escape SQL query. Escape also the special % and _ characters wheres += ["f LIKE '%{0}%'".format(safe_string(kw.replace('%', '\%').replace('_', '\_'))) for kw in keywords.split() if kw] if len(wheres) > 0: # Merge where statements where_str = " WHERE " + ' AND '.join(wheres) else: where_str = "" # Merge join statements join_str = " ".join(joins) attr_str = " ORDER BY IFNULL(p.published, p.created) DESC LIMIT %(limit_start)d, %(limit)d" % limit_attr query = select + join_str + where_str + attr_str return self.__queryProjectsWithDescr(query)
def get_emails(self, usernames): if not usernames: return [] usernames_wrapped = [] for username in usernames: usernames_wrapped.append("'" + safe_string(username) + "'") usernames_str = ','.join(usernames_wrapped) mails = [] query = "SELECT DISTINCT mail FROM user WHERE username IN (" + usernames_str + ")" with admin_query() as cursor: try: cursor.execute(query) for row in cursor: if '@' in row[0]: mails.append(row[0]) except: conf.log.exception("Exception. Query failed when getting emails '''%s'''" % query) raise return mails
def _get_name_for_cookie(self, req, cookie, send_cookie = True): """ Fetch cookie->username from database based on cookie value """ cache = UserCache.instance() sql_safe_cookie_value = safe_string(cookie.value) active = self.cookie.get(sql_safe_cookie_value) name = cache.getUserCookieName(sql_safe_cookie_value) if name: if not active: if send_cookie: self._set_outcookie(req, sql_safe_cookie_value) self.cookie.add(sql_safe_cookie_value) return name row = None with admin_query() as cursor: try: cursor.execute("SELECT name FROM auth_cookie WHERE cookie=%s", (sql_safe_cookie_value,)) row = cursor.fetchone() except Exception: self.log.exception("Failed to get the name for the cookie") if not row: # The cookie is invalid (or has been purged from the database), so # tell the user agent to drop it as it is invalid if send_cookie: self._expire_cookie(req) return None if not active: if send_cookie: self._set_outcookie(req, sql_safe_cookie_value) self.cookie.add(sql_safe_cookie_value) cache.setUserCookieName(sql_safe_cookie_value, row[0]) return row[0]
def get_messages_with_query(cls, user_id, query, limit=30): """ Retrieve messages based on query that is matched with: :param int user_id: Id of the user whose messages are wanted to be retrieved :param str query: Query value matching with fields: - username - given name - last name - message content :param int limit: Maximum number of messages to fetch :returns: List of message objects matching with the query """ messages = [] # Query: match with username and message sql = ''' SELECT *, CONCAT_WS(' ', m.content, m.env, sender.givenName, sender.lastName, sender.username, receiver.username ) COLLATE utf8_general_ci AS query FROM message AS m LEFT JOIN message_group_recipient AS mgr ON m.message_group_id = mgr.message_group_id LEFT JOIN user AS sender ON sender.user_id = mgr.user_id LEFT JOIN user AS receiver ON receiver.user_id = mgr.user_id WHERE mgr.user_id = %s HAVING query LIKE '%%{0}%%' ORDER BY m.created DESC LIMIT %s '''.format(safe_string(query)) with admin_query(cursors.DictCursor) as cursor: cursor.execute(sql, (user_id, limit)) messages = [cls.to_object(row) for row in cursor.fetchall()] return messages
def from_analytical(self, project_identifier, forum_id): """ Return user from analytical database """ query = """ SELECT forum_key, discussion_name, author, moderators, subscribers, subject, description, project_key, project_identifier, project_name FROM discussion_dim WHERE project_identifier = '%s' AND forum_key = %d AND VALID_TO IS NULL""" % (safe_string(project_identifier), safe_int(forum_id)) row = [] with analytical_query() as cursor: try: cursor.execute(query) row = cursor.fetchone() except: conf.log.exception( "Failed reading discussion forum from analytical db. {project: %s, forum: %d}" % (project_identifier, forum_id)) if not row: return None project = { 'forum_key': row[0], 'discussion_name': MySQLdb.escape_string(row[1]), 'author': MySQLdb.escape_string(row[2]), 'moderators': MySQLdb.escape_string(row[3]), 'subscribers': MySQLdb.escape_string(row[4]), 'subject': MySQLdb.escape_string(row[5]), 'description': MySQLdb.escape_string(row[6]), 'project_key': row[7], 'project_identifier': MySQLdb.escape_string(row[8]), 'project_name': MySQLdb.escape_string(row[9]) } return project
def from_operational(self, project_identifier, forum_id): # Alternative way to do this would be to open the connection straight into the project database... query = """ SELECT id, name, author, moderators, subscribers, subject, description FROM %(project_identifier)s.forum WHERE id = %(forum_id)s """ % { 'project_identifier': safe_string(project_identifier), 'forum_id': safe_int(forum_id) } dibo = None with admin_query() as cursor: try: cursor.execute(query) row = cursor.fetchone() if not row: return None dibo = { 'forum_key': row[0], 'discussion_name': row[1], 'author': row[2], 'moderators': row[3], 'subscribers': row[4], 'subject': row[5], 'description': row[6] } except: conf.log.exception( "Failed reading a record from discussion dimension. %s" % str(dibo)) pd = ProjectDimension() project = pd.from_operational(project_identifier) dibo['project_key'] = project['project_key'] dibo['project_identifier'] = project['identifier'] dibo['project_name'] = project['project_name'] return dibo
def get_category_by_name_filter(self, name_filter, limit=None, context_ids=None): """ Returns a list of categories matching filter. If context_ids are provided, search is restricted to the corresponding contexts. :returns: list of matched Categories """ default_limit = 100 if name_filter: name_filter = name_filter.lower() and_context_ids = '' if context_ids and len(context_ids) < 20: and_context_ids = ' AND context_id IN ({0}) '.format( ','.join([str(safe_int(id)) for id in context_ids]) ) limit = safe_int(limit) or default_limit query = """ SELECT * FROM categories WHERE LOWER(category_name) LIKE '%{0}%' {1} LIMIT {2}""".format(safe_string(name_filter), and_context_ids, limit) categories = [] conf.log.debug("Querying categories with query: %s", query) try: with admin_query() as cursor: cursor.execute(query) for row in cursor: categories.append(Category.from_sql_row(row)) except Exception: conf.log.exception( "Exception. Query failed when searching category by name_filter('%s') from database." % str(name_filter)) return categories
def get_recipients(self, resid): """ List all admin emails in to field """ cc = [] to = [] # Wrap and escape admins in quotes admins = CQDEPermissionStore(env=self.env).get_users_with_permissions( ['TRAC_ADMIN']) recipients = ", ".join( ["'%s'" % safe_string(admin) for admin in admins]) # Query for admin email addresses with admin_transaction() as cursor: query = "SELECT mail FROM user WHERE username IN ({0})".format( recipients) try: cursor.execute(query) to = [row[0] for row in cursor] except: self.env.log.exception( "Exception. MembershipApi.get_recipients query failed. '''%s'''" % query) return to, cc
def __init__(self, env_name): conf = Configuration.instance() self.log = conf.log self.news_forum_name = conf.news_forum_name self.env_name = db.safe_string(env_name)
def __init__(self, project): Command.__init__(self) self.dbname = safe_string(project.env_name) self.name = "CreateTracDatabase"
def get_projects_with_params(self, username, perm, namelike=None, categories=None): """ :returns: a list of archived projects where user have right for permission (action). """ categories = categories or [] user = get_userstore().getUser(username) user_organization = user.organization_keys # Get subjects subjects = set([username]) subjects.update(get_special_users(username)) # Construct comma separated lists for queries actions_str = ','.join("'%s'" % safe_string(p) for p in [perm, 'TRAC_ADMIN']) subjects_str = ','.join("'%s'" % safe_string(subject) for subject in subjects) categories_str = ','.join("'%s'" % safe_string(cat) for cat in categories) query = """ SELECT DISTINCT pa.* FROM project_archive AS pa INNER JOIN `group` ON group.trac_environment_key = ( SELECT environment_id FROM trac_environment WHERE identifier = pa.environment_name ) INNER JOIN group_permission ON group_permission.group_key = group.group_id INNER JOIN action ON group_permission.permission_key = action.action_id LEFT JOIN user_group ON user_group.group_key = group.group_id LEFT JOIN user ON user.user_id = user_group.user_key """ # NOTE! When project is archived/removed, the category information is also removed if categories: query += ( "INNER JOIN project_categories ON pa.orig_project_id = project_categories.project_key " "INNER JOIN categories ON categories.category_id = project_categories.category_key " ) query += ( "LEFT JOIN organization_group ON organization_group.group_key = group.group_id " "WHERE (user.username IN (%s) " % subjects_str) if not user_organization: query += "OR organization_group.organization_key = NULL )" else: # List user organizations as safe int, separated with comma: (1,5,3,65) orc = lambda org_key: str(safe_int(org_key)) query += "OR organization_group.organization_key IN (%s) ) " % ','.join( map(orc, user_organization)) query += "AND action.action_string IN(" + actions_str + ") " if categories: query += "AND categories.category_name IN(" + categories_str + ") " if namelike: conditions = [] search_strs = namelike.split(' ') for search in search_strs: if not search == '': search = safe_string(search) conditions.append("pa.project_name LIKE '%" + search + "%'") where = " OR ".join(conditions) query += "AND (" + where + ") " query += "ORDER BY pa.project_name" return self.query_archived_projects(query)
def _query_users(self, req, query, fields, states=None, auth=None, perm=None, limit=None): """ Implements the full search based on given parameters. Maximum number of results is 30 :param str query: Search query matching into fields :param list fields: Name of fields where to search. .. NOTE:: Fields are combined for search, in provided order :param list states: Optional list of state names to include in search :param str auth: Optional name of authentication backend/organization to limit down the results :returns: List of results (each result presented as dictionary """ rows = [] home_perm = PermissionCache(HomeProject().get_env(), username=req.authname) default_limit = 30 limit = int(limit or default_limit) # Search terms shorter than 2 - except for the persons with USER_AUTHOR perms if len(query) < 2 and 'USER_AUTHOR' not in home_perm: return [] # Validate limit if limit > default_limit and 'USER_AUTHOR' not in home_perm: limit = default_limit # NOTE1: Fields are validated and escaped when reading from the request # Construct where: join fields so that we can search from all of them # NOTE2: Fields type other that varchar cannot be collated char_fields = [self.db_fields[field] for field in fields if field not in self.nonchar_fields] where = ["WHERE CONCAT_WS(' ', {0}) COLLATE utf8_general_ci LIKE '%{1}%'" .format(','.join(char_fields), query.lower())] # Do not list special users where.append('u.username NOT IN ("anonymous", "authenticated")') # Limit down the results based on authentication method if auth: where.append("LOWER(auth.method) = '{0}'".format(auth)) # Limit down the results based on status key if states: # Handle special status: expired if 'expired' in states: where.append("(u.expires IS NOT NULL AND u.expires <= NOW())") states.remove('expired') # If also other type of states defined if states: where.append("LOWER(us.status_label) IN ({0})".format(','.join(["'%s'" % safe_string(state) for state in states]))) # Limit down per permissions # NOTE: This does not really check the Trac resource permission (it would be far too slow), just check the author_id # FIXME: Do proper check and cache permissions results do proper check? if perm and perm != 'USER_VIEW': # Limit results down everybody else but user admins if 'USER_ADMIN' not in home_perm: userstore = get_userstore() user = userstore.getUser(req.authname) where.append('(u.author_id = {0} OR u.user_id = {0} OR FIND_IN_SET({0}, u.deputies) > 0)'.format(safe_int(user.id))) # Check if user is not home admin and remove private fields from query if 'USER_ADMIN' not in home_perm: for pfield in [pfield for pfield in self.sens_fields if pfield in fields]: fields.remove(pfield) # Construct SQL queries select_fields = ['{0} AS {1}'.format(self.db_fields[field], field) for field in fields] select_sql = ','.join(select_fields) where_sql = ' AND '.join(where) # SQL query for fetching username - in-casesentive sql = """ SELECT {0} FROM user AS u LEFT JOIN authentication AS auth ON auth.id = u.authentication_key LEFT JOIN user_status AS us ON us.user_status_id = u.user_status_key {1} ORDER BY u.givenName, u.lastname, u.username LIMIT {2} """.format(select_sql, where_sql, limit) with admin_query(cursors.DictCursor) as cursor: try: cursor.execute(sql) rows = cursor.fetchall() except Exception: self.log.exception("Failed to get users with query %s" % sql) return rows