public ulong Nav_GetNearestParent(Title title) { string query = @"/* Nav_GetNearestParent */ SELECT page_id FROM pages WHERE page_namespace={0} AND STRCMP(SUBSTRING('{1}', 1, CHAR_LENGTH(page_title) + 1), CONCAT(page_title, '/'))=0 ORDER BY CHAR_LENGTH(page_title) DESC LIMIT 1"; return(Catalog.NewQuery(string.Format(query, (int)title.Namespace, DataCommand.MakeSqlSafe(title.AsUnprefixedDbPath()))).ReadAsULong() ?? Head.Pages_HomePageId); }
public IList <BanBE> Bans_GetByRequest(uint userid, IList <string> ips) { StringBuilder sb = new StringBuilder(); foreach (string ip in ips) { if (sb.Length > 0) { sb.Append(", "); } sb.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(ip)); } if (sb.Length == 0) { sb.Append("NULL"); } //Note: expiration not applied in query to allow for query caching string where = string.Format(@" where b.ban_id in ( select b.ban_id from bans b left join banips bi on b.ban_id = bi.banip_ban_id where bi.banip_ipaddress in ({0}) union select b.ban_id from bans b left join banusers bu on b.ban_id = bu.banuser_ban_id where bu.banuser_user_id = {1})", sb.ToString(), userid); return(Bans_GetBansInternal(where)); }
public Dictionary <Title, ResourceBE> Resources_GetFileResourcesByTitlesWithMangling(IList <Title> fileTitles) { //A specialized query that retrieves file resources from titles. This is only used from DekiXmlParser.ConvertFileLinks Dictionary <Title, ResourceBE> ret = new Dictionary <Title, ResourceBE>(); if (ArrayUtil.IsNullOrEmpty(fileTitles)) { return(ret); } string query = @" /* Resources_GetFileResourcesByTitlesWithMangling */ select resources.*, resourcecontents.*, pages.page_id, pages.page_namespace, pages.page_title, pages.page_display_name from pages join resources on resrev_parent_page_id = page_id left join resourcecontents on resources.resrev_content_id = resourcecontents.rescontent_id where res_type = 2 AND res_deleted = 0 AND( {0} ) "; StringBuilder whereQuery = new StringBuilder(); for (int i = 0; i < fileTitles.Count; i++) { if (i > 0) { whereQuery.Append("\n OR "); } whereQuery.AppendFormat("(pages.page_namespace={0} AND pages.page_title='{1}' AND REPLACE(resources.resrev_name, ' ', '_') = REPLACE('{2}', ' ', '_'))", (uint)fileTitles[i].Namespace, DataCommand.MakeSqlSafe(fileTitles[i].AsUnprefixedDbPath()), DataCommand.MakeSqlSafe(fileTitles[i].Filename)); } query = string.Format(query, whereQuery); Catalog.NewQuery(query).Execute(delegate(IDataReader dr) { while (dr.Read()) { Title title = DbUtils.TitleFromDataReader(dr, "page_namespace", "page_title", "page_display_name", "resrev_name"); ResourceBE r = Resources_Populate(dr) as ResourceBE; if (r != null) { ret[title] = r; } } }); return(ret); }
public IList <GroupBE> Groups_GetByQuery(string groupNameFilter, uint?serviceIdFilter, SortDirection sortDir, GroupsSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount) { List <GroupBE> result = new List <GroupBE>(); StringBuilder query = new StringBuilder(); if (groupNameFilter != null) { groupNameFilter = "%" + DataCommand.MakeSqlSafe(groupNameFilter) + "%"; } string sortFieldString = null; GROUPS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString); if ((sortFieldString ?? string.Empty).StartsWith("roles.")) { query.Append(@" left join roles on groups.group_role_id = roles.role_id"); } if ((sortFieldString ?? string.Empty).StartsWith("services.")) { query.AppendFormat(@" left join services on groups.group_service_id = services.service_id"); } if (!string.IsNullOrEmpty(groupNameFilter) || serviceIdFilter != null) { query.Append(" where (1=1)"); if (serviceIdFilter != null) { query.AppendFormat(" AND group_service_id = {0}", serviceIdFilter.Value); } if (!string.IsNullOrEmpty(groupNameFilter)) { query.AppendFormat(" AND group_name like '{0}'", groupNameFilter); } } if (!string.IsNullOrEmpty(sortFieldString)) { query.AppendFormat(" order by {0} ", sortFieldString); if (sortDir != SortDirection.UNDEFINED) { query.Append(sortDir.ToString()); } } return(Groups_GetInternal(query.ToString(), "Groups_GetByQuery", true, limit, offset, out totalCount, out queryCount)); }
public void Links_UpdateLinksForPage(PageBE page, IList <ulong> outboundLinks, IList <string> brokenLinks) { StringBuilder query = new StringBuilder(@" /* Links_UpdateLinksForPage */ DELETE FROM links WHERE l_from = ?PAGEID; DELETE FROM brokenlinks WHERE bl_from = ?PAGEID; "); // build page's outbound links query if (!ArrayUtil.IsNullOrEmpty(outboundLinks)) { query.Append(@" INSERT IGNORE INTO links (l_from, l_to) VALUES "); for (int i = 0; i < outboundLinks.Count; i++) { query.AppendFormat("{0}(?PAGEID, {1})", i > 0 ? "," : "", outboundLinks[i]); } query.Append(";"); } // build page's broken links query if (!ArrayUtil.IsNullOrEmpty(brokenLinks)) { query.Append(@" INSERT IGNORE INTO brokenlinks (bl_from, bl_to) VALUES "); for (int i = 0; i < brokenLinks.Count; i++) { query.AppendFormat("{0}(?PAGEID, '{1}')", i > 0 ? "," : "", DataCommand.MakeSqlSafe(brokenLinks[i])); } query.Append(";"); } // build query to update any broken links that point to this page query.Append(@" INSERT IGNORE INTO links (l_from, l_to) SELECT brokenlinks.bl_from, ?PAGEID FROM brokenlinks WHERE bl_to = ?TITLE; DELETE FROM brokenlinks WHERE bl_to = ?TITLE;" ); Catalog.NewQuery(query.ToString()) .With("PAGEID", page.ID) .With("TITLE", page.Title.AsPrefixedDbPath()) .Execute(); }
public IList <GroupBE> Groups_GetByNames(IList <string> groupNames) { if (ArrayUtil.IsNullOrEmpty(groupNames)) { return(new List <GroupBE>()); } var groupNamesStr = new StringBuilder(); for (int i = 0; i < groupNames.Count; i++) { if (i > 0) { groupNamesStr.Append(","); } groupNamesStr.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(groupNames[i])); } return(Groups_GetInternal(string.Format("where groups.group_name in ({0})", groupNamesStr), "Groups_GetByNames")); }
public void Config_WriteInstanceSettings(IList <KeyValuePair <string, string> > keyValues) { StringBuilder query = new StringBuilder(); query.Append(@" /* Config_WriteInstanceSettings */ DELETE FROM `config`; INSERT INTO `config` (`config_key`, `config_value`) VALUES "); bool first = true; foreach (KeyValuePair <string, string> setting in keyValues) { string key = DataCommand.MakeSqlSafe(setting.Key.TrimEnd(new char[] { '/' })); string val = DataCommand.MakeSqlSafe(setting.Value); query.AppendFormat("{0}('{1}', '{2}' )", first ? string.Empty : ",", key, val); first = false; } query.Append(";"); Catalog.NewQuery(query.ToString()).Execute(); }
public static void InsertDWIPBlock(IPBlockBE ipBlock) { uint banID = 0; MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into bans (ban_by_user_id, ban_reason, ban_revokemask, ban_last_edit) VALUES ('{0}', '{1}', '{2}', '{3}'); SELECT LAST_INSERT_ID() as banid;", ipBlock.ByUserID, DataCommand.MakeSqlSafe(ipBlock.Reason), 9223372036854779902, ipBlock.Timestamp)).Execute(delegate(IDataReader dr) { while (dr.Read()) { banID = DbUtils.Convert.To <uint>(dr["banid"], 0); } }); if (0 == ipBlock.UserID) { MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into banips (banip_ipaddress, banip_ban_id) VALUES ('{0}', '{1}')", DataCommand.MakeSqlSafe(ipBlock.Address), banID)).Execute(); } else { MediaWikiConverterContext.Current.DWCatalog.NewQuery(String.Format("INSERT into banusers (banuser_user_id, banuser_ban_id) VALUES ('{0}', '{1}')", ipBlock.UserID, banID)).Execute(); } }
//--- Methods --- public ulong SearchAnalytics_LogQuery(SearchQuery query, string parsedQuery, uint userId, uint resultCount, ulong? previousQueryId) { var sorted = query.GetOrderedNormalizedTermString(); var hash = query.GetOrderedTermsHash(); var queryId = Catalog.NewQuery(@"/* SearchAnalytics_LogQuery */ INSERT INTO query_log (raw, sorted_terms, sorted_terms_hash, parsed, created, user_id, ref_query_id, result_count) VALUES (?QUERY, ?SORTED, ?HASH, ?PARSED, ?CREATED, ?USERID, ?REFID, ?RESULTCOUNT); SELECT LAST_INSERT_ID();") .With("QUERY", query.Raw) .With("SORTED", sorted) .With("HASH", hash) .With("PARSED", parsedQuery) .With("USERID", userId) .With("CREATED", DateTime.UtcNow) .With("REFID", previousQueryId) .With("RESULTCOUNT", resultCount) .ReadAsULong().Value; var terms = query.GetNormalizedTerms(); if(terms.Any()) { var quotedTerms = terms.Select(x => "'" + DataCommand.MakeSqlSafe(x) + "'").ToArray(); Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */ INSERT IGNORE INTO query_terms (query_term) values {0};", string.Join(",", quotedTerms.Select(x => "(" + x + ")").ToArray()))).Execute(); var termIds = new List<uint>(); Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */ SELECT query_term_id from query_terms where query_term IN({0})", string.Join(",", quotedTerms))).Execute(r => { while(r.Read()) { termIds.Add(r.Read<uint>(0)); } }); Catalog.NewQuery(string.Format(@"/* SearchAnalytics_LogQuery */ INSERT IGNORE INTO query_term_map (query_term_id,query_id) values {0};", string.Join(",", termIds.Select(x => "(" + x + "," + queryId + ")").ToArray()))).Execute(); } return queryId; }
public IEnumerable <UserBE> Users_GetByQuery(string usernamefilter, string realnamefilter, string usernameemailfilter, string rolefilter, bool?activatedfilter, uint?groupId, uint?serviceIdFilter, bool?seatFilter, SortDirection sortDir, UsersSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount) { List <UserBE> users = new List <UserBE>(); uint totalCountTemp = 0; uint queryCountTemp = 0; StringBuilder joinQuery = new StringBuilder(); string sortFieldString; USERS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString); if (!string.IsNullOrEmpty(rolefilter) || (sortFieldString ?? string.Empty).StartsWith("roles.")) { joinQuery.Append(@" left join roles on users.user_role_id = roles.role_id"); } if ((sortFieldString ?? string.Empty).StartsWith("services.")) { joinQuery.AppendFormat(@" left join services on users.user_service_id = services.service_id"); } if (groupId != null) { joinQuery.AppendFormat(@" join groups on groups.group_id = {0} join user_groups on user_groups.group_id = groups.group_id", groupId.Value); } StringBuilder whereQuery = new StringBuilder(" where 1=1"); if (groupId != null) { whereQuery.AppendFormat(" AND users.user_id = user_groups.user_id"); } if (!string.IsNullOrEmpty(usernamefilter) && !string.IsNullOrEmpty(realnamefilter)) { whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_real_name like '{1}%')", DataCommand.MakeSqlSafe(usernamefilter), DataCommand.MakeSqlSafe(realnamefilter)); } else if (!string.IsNullOrEmpty(usernamefilter)) { whereQuery.AppendFormat(" AND user_name like '{0}%'", DataCommand.MakeSqlSafe(usernamefilter)); } else if (!string.IsNullOrEmpty(realnamefilter)) { whereQuery.AppendFormat(" AND user_real_name like '{0}%'", DataCommand.MakeSqlSafe(realnamefilter)); } if (!string.IsNullOrEmpty(usernameemailfilter)) { whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_email like '{0}%')", DataCommand.MakeSqlSafe(usernameemailfilter)); } if (activatedfilter != null) { whereQuery.AppendFormat(" AND user_active = {0}", activatedfilter.Value ? "1" : "0"); } if (!string.IsNullOrEmpty(rolefilter)) { whereQuery.AppendFormat(" AND role_name = '{0}'", DataCommand.MakeSqlSafe(rolefilter)); } if (serviceIdFilter != null) { whereQuery.AppendFormat(" AND user_service_id = {0}", serviceIdFilter.Value); } if (seatFilter != null) { whereQuery.AppendFormat(" AND user_seat = {0}", seatFilter.Value ? 1 : 0); } StringBuilder sortLimitQuery = new StringBuilder(); if (!string.IsNullOrEmpty(sortFieldString)) { sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString); if (sortDir != SortDirection.UNDEFINED) { sortLimitQuery.Append(sortDir.ToString()); } } if (limit != null || offset != null) { sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0); } string query = string.Format(@" /* Users_GetByQuery */ select * from users {0} {1} {2}; select count(*) as totalcount from users {0} {3}; select count(*) as querycount from users {0} {1};", joinQuery, whereQuery, sortLimitQuery, groupId == null ? string.Empty : "where users.user_id = user_groups.user_id"); Catalog.NewQuery(query) .Execute(delegate(IDataReader dr) { while (dr.Read()) { UserBE u = Users_Populate(dr); users.Add(u); } if (dr.NextResult() && dr.Read()) { totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0); } if (dr.NextResult() && dr.Read()) { queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0); } }); totalCount = totalCountTemp; queryCount = queryCountTemp; return(users); }
public override User GetUser(string user) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT {1}usergroup.title FROM {1}user LEFT JOIN {1}usergroup ON {1}user.usergroupid={1}usergroup.usergroupid WHERE {1}user.username='******' AND {1}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), _tablePrefix)); string title = cmd.Read(); if (title == null) { return(null); } return(new User(user, string.Empty, new Group[] { new Group(title) })); }
public override bool CheckUserPassword(string user, string password) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT count(*) FROM {2}user LEFT JOIN {2}usergroup ON {2}user.usergroupid={2}usergroup.usergroupid WHERE {2}user.username='******' AND {2}user.password=MD5(CONCAT(MD5('{1}'), salt)) AND {2}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), DataCommand.MakeSqlSafe(password), _tablePrefix)); long count = cmd.ReadAsLong() ?? 0; return(count > 0); }
public override Group GetGroup(string group) { string title = _catalog.NewQuery(string.Format("SELECT title FROM {1}usergroup WHERE {1}usergroup.title='{0}' AND {1}usergroup.forumpermissions!=0", DataCommand.MakeSqlSafe(group), _tablePrefix)).Read(); if (title != null) { return(new Group(title)); } return(null); }
public IList <PageBE> Pages_GetPopular(string language, uint?offset, uint?limit) { string limitAndOffset = (limit == null && offset == null) ? string.Empty : string.Format("limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0); string query = "INNER JOIN page_viewcount as pv ON p.page_id = pv.page_id WHERE p.page_namespace = 0 AND p.page_is_redirect = 0"; //Add language filter to query if (!string.IsNullOrEmpty(language)) { query += string.Format(" AND (p.page_language = '' OR p.page_language = '{0}') ", DataCommand.MakeSqlSafe(language)); } return(Pages_GetInternal(query, "ORDER BY pv.page_counter DESC", limitAndOffset, "Pages_GetPopular")); }
//--- Methods --- public override bool CheckUserPassword(string user, string password) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT count(*) FROM {2}users WHERE name='{0}' AND pass=md5('{1}') LIMIT 1", DataCommand.MakeSqlSafe(user), DataCommand.MakeSqlSafe(password), _prefix)); long count = cmd.ReadAsLong() ?? 0; if (count > 0) { return(true); } return(false); }
public Dictionary <Title, ulong> Pages_GetIdsByTitles(IList <Title> pageTitle) { Dictionary <Title, ulong> ids = new Dictionary <Title, ulong>(); if (0 < pageTitle.Count) { // generate query to retrieve all pages matching the criteria // TODO (brigette): this might break for very long queries. We need to test on a page with many links StringBuilder query = new StringBuilder("SELECT page_id, page_namespace, page_title, page_display_name FROM pages WHERE "); for (int i = 0; i < pageTitle.Count; i++) { if (0 < i) { query.Append(" OR "); } query.AppendFormat(" (page_namespace={0} AND page_title='{1}')", (uint)pageTitle[i].Namespace, DataCommand.MakeSqlSafe(pageTitle[i].AsUnprefixedDbPath())); } query.Append(" GROUP BY page_namespace, page_title"); // execute the query and read the results into a lookup table Catalog.NewQuery(query.ToString()).Execute(delegate(IDataReader dr) { while (dr.Read()) { Title info = DbUtils.TitleFromDataReader(dr, "page_namespace", "page_title", "page_display_name"); ids[info] = dr.Read <ulong>("page_id"); } }); } return(ids); }
public override bool CheckUserPassword(string user, string password) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT COUNT(*) FROM openIDToUser LEFT JOIN users ON openIDToUser.userID = users.userID WHERE ((users.internal = 'Y' AND openIDToUser.openID = CONCAT('{0}', '{2}') AND users.password = MD5(CONCAT('{1}', 'WL758ek0', salt))) OR (users.internal = 'N' AND openIDToUser.openID = '{0}' AND users.password = MD5('{1}'))) AND users.active = 'Y' LIMIT 1", DataCommand.MakeSqlSafe(user.ToLowerInvariant()), DataCommand.MakeSqlSafe(password), DataCommand.MakeSqlSafe(_domain))); long count = cmd.ReadAsLong() ?? 0; if (count > 0) { return(true); } return(false); }
public override bool CheckUserPassword(string user, string password) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT user_pass FROM {1}users WHERE user_login='******' AND user_status=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix)); XDoc result = cmd.ReadAsXDoc("users", "user"); if (result["user"].IsEmpty) { return(false); } string hashed = result["user/user_pass"].AsText; return(CheckPassword(password, hashed)); }
public IList <ServiceBE> Services_GetByQuery(string serviceType, SortDirection sortDir, ServicesSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount) { IList <ServiceBE> services = null; uint totalCountTemp = 0; uint queryCountTemp = 0; StringBuilder whereQuery = new StringBuilder(" where 1=1"); if (!string.IsNullOrEmpty(serviceType)) { whereQuery.AppendFormat(" AND service_type = '{0}'", DataCommand.MakeSqlSafe(serviceType)); } StringBuilder sortLimitQuery = new StringBuilder(); string sortFieldString = null; //Sort by id if no sort specified. if (sortField == ServicesSortField.UNDEFINED) { sortField = ServicesSortField.ID; } if (SERVICES_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString)) { sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString); if (sortDir != SortDirection.UNDEFINED) { sortLimitQuery.Append(sortDir.ToString()); } } if (limit != null || offset != null) { sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0); } string query = string.Format(@" /* Services_GetByQuery */ select * from services {0} {1}; select service_config.* from service_config join ( select service_id from services {0} {1} ) s on service_config.service_id = s.service_id; select service_prefs.* from service_prefs join ( select service_id from services {0} {1} ) s on service_prefs.service_id = s.service_id; select count(*) as totalcount from services; select count(*) as querycount from services {0}; ", whereQuery, sortLimitQuery); Catalog.NewQuery(query) .Execute(delegate(IDataReader dr) { services = Services_Populate(dr); if (dr.NextResult() && dr.Read()) { totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0); } if (dr.NextResult() && dr.Read()) { queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0); } }); totalCount = totalCountTemp; queryCount = queryCountTemp; return(services == null ? new List <ServiceBE>() : services); }
public uint Services_Insert(ServiceBE service) { StringBuilder query = null; if (service.Id == 0) { //new service query = new StringBuilder(@" /* Services_Insert */ insert into services (service_type, service_sid, service_uri, service_description, service_local, service_enabled, service_last_edit, service_last_status) values (?TYPE, ?SID, ?URI, ?DESC, ?LOCAL, ?ENABLED, ?TIMESTAMP, ?LASTSTATUS); "); query.AppendLine("select LAST_INSERT_ID() into @service_id;"); query.AppendLine("select LAST_INSERT_ID() as service_id;"); } else { //update existing service query = new StringBuilder(@" /* Services_Insert (with id) */ insert into services (service_id, service_type, service_sid, service_uri, service_description, service_local, service_enabled, service_last_edit, service_last_status) values (?ID, ?TYPE, ?SID, ?URI, ?DESC, ?LOCAL, ?ENABLED, ?TIMESTAMP, ?LASTSTATUS); "); query.AppendLine(string.Format("select {0} into @service_id;", service.Id)); query.AppendLine(string.Format("select {0} as service_id;", service.Id)); } if (service.Preferences != null && service.Preferences.Count > 0) { query.Append("insert into service_prefs (service_id, pref_name, pref_value) values "); for (int i = 0; i < service.Preferences.AllKeys.Length; i++) { string key = DataCommand.MakeSqlSafe(service.Preferences.AllKeys[i]); string val = DataCommand.MakeSqlSafe(service.Preferences[key]); query.AppendFormat("{0}(@service_id, '{1}', '{2}')\n", i > 0 ? "," : string.Empty, key, val); } query.AppendLine(";"); } if (service.Config != null && service.Config.Count > 0) { query.Append("insert into service_config (service_id, config_name, config_value) values "); for (int i = 0; i < service.Config.AllKeys.Length; i++) { string key = DataCommand.MakeSqlSafe(service.Config.AllKeys[i]); string val = DataCommand.MakeSqlSafe(service.Config[key]); query.AppendFormat("{0}(@service_id, '{1}', '{2}')\n", i > 0 ? "," : string.Empty, key, val); } query.AppendLine(";"); } uint serviceId = 0; try { serviceId = Catalog.NewQuery(query.ToString()) .With("ID", service.Id) .With("TYPE", service.Type.ToString()) .With("SID", service.SID) .With("URI", service.Uri) .With("DESC", service.Description) .With("LOCAL", service.ServiceLocal) .With("ENABLED", service.ServiceEnabled) .With("TIMESTAMP", service.ServiceLastEdit) .With("LASTSTATUS", service.ServiceLastStatus) .ReadAsUInt() ?? 0; } catch (MySqlException e) { // catch Duplicate Key (1062) if (e.Number == 1062) { serviceId = service.Id; } else { throw; } } return(serviceId); }
public override User GetUser(string user) { string name = "", email = ""; DataCommand cmd = _catalog.NewQuery(string.Format("SELECT user_login, user_email FROM {1}users WHERE user_login='******' AND user_status=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix)); XDoc result = cmd.ReadAsXDoc("users", "user"); if (result["user"].IsEmpty) { return(null); } name = result["user/user_login"].AsText; email = result["user/user_email"].AsText; // TODO, group should be determined by deserializing the PHP value in wp_usermeta meta_key='wp_capabilities' return(new User(name, email, new Group[] { _defaultGroup })); }
private static string BuildNameFilterWhereClause(IList <string> names, string tableAlias) { /* * AND ( * name in (1,2,3) * OR name like %4% * OR name like %5% * ) */ StringBuilder nameSpecificQuery = new StringBuilder(); StringBuilder nameSubstringQuery = new StringBuilder(); if (!ArrayUtil.IsNullOrEmpty(names)) { for (int i = 0; i < names.Count; i++) { if (!names[i].Contains('*')) { if (nameSpecificQuery.Length == 0) { nameSpecificQuery.AppendFormat("{0}.resrev_name in (", tableAlias); } else { nameSpecificQuery.Append(i > 0 ? "," : string.Empty); } nameSpecificQuery.AppendFormat("'{0}'", DataCommand.MakeSqlSafe(names[i])); } else { //Change the name into a mysql substring expression string nameExpression = DataCommand.MakeSqlSafe(names[i]); if (nameExpression.StartsWith("*", StringComparison.InvariantCultureIgnoreCase)) { nameExpression = nameExpression.TrimStart('*').Insert(0, "%"); } if (nameExpression.EndsWith("*", StringComparison.InvariantCultureIgnoreCase)) { nameExpression = nameExpression.TrimEnd('*') + '%'; } if (nameSubstringQuery.Length > 0) { nameSubstringQuery.Append(" OR "); } nameSubstringQuery.AppendFormat(" {0}.resrev_name like \"{1}\"", tableAlias, nameExpression); } } if (nameSpecificQuery.Length > 0) { nameSpecificQuery.Append(")"); } } string ret = string.Empty; if (nameSpecificQuery.Length > 0 || nameSubstringQuery.Length > 0) { ret = string.Format(" AND ({0} {1} {2})", nameSpecificQuery, (nameSpecificQuery.Length > 0 && nameSubstringQuery.Length > 0) ? "OR" : string.Empty, nameSubstringQuery); } return(ret); }
public IList <TagBE> Tags_GetByQuery(string partialName, TagType type, DateTime from, DateTime to) { // retrieve the tags associated with a specified page id List <TagBE> tags = new List <TagBE>(); bool hasWhere = false; StringBuilder query = new StringBuilder(); query.Append(@" /* Tags_GetByQuery */ SELECT `tag_id`, `tag_name`, `tag_type` FROM tags "); if (!string.IsNullOrEmpty(partialName)) { query.AppendFormat("WHERE tag_name LIKE '{0}%' ", DataCommand.MakeSqlSafe(partialName)); hasWhere = true; } if (type != TagType.ALL) { if (hasWhere) { query.Append("AND "); } else { query.Append("WHERE "); } query.AppendFormat(" tag_type={0} ", (int)type); hasWhere = true; } if ((type == TagType.DATE) && (from != DateTime.MinValue)) { if (hasWhere) { query.Append("AND "); } else { query.Append("WHERE "); } query.AppendFormat("tag_name >= '{0}' ", from.ToString("yyyy-MM-dd")); hasWhere = true; } if ((type == TagType.DATE) && (to != DateTime.MaxValue)) { if (hasWhere) { query.Append("AND "); } else { query.Append("WHERE "); } query.AppendFormat("tag_name <= '{0}' ", to.ToString("yyyy-MM-dd")); } Catalog.NewQuery(query.ToString()) .Execute(delegate(IDataReader dr) { while (dr.Read()) { TagBE t = Tags_Populate(dr); tags.Add(t); } }); return(tags); }
public uint Bans_Insert(BanBE ban) { //build banusers insert query StringBuilder userIdInsertQuery = new StringBuilder(); if (ban.BanUserIds != null && ban.BanUserIds.Count > 0) { userIdInsertQuery.Append("insert into banusers (banuser_user_id, banuser_ban_id) values "); for (int i = 0; i < ban.BanUserIds.Count; i++) { userIdInsertQuery.AppendFormat("{0}({1}, @banid)", i > 0 ? "," : string.Empty, ban.BanUserIds[i]); } userIdInsertQuery.Append(";"); } //build banips insert query StringBuilder addressesInsertQuery = new StringBuilder(); if (ban.BanAddresses != null && ban.BanAddresses.Count > 0) { addressesInsertQuery.Append("insert into banips (banip_ipaddress, banip_ban_id) values "); for (int i = 0; i < ban.BanAddresses.Count; i++) { addressesInsertQuery.AppendFormat("{0}('{1}', @banid)", i > 0 ? "," : string.Empty, DataCommand.MakeSqlSafe(ban.BanAddresses[i])); } addressesInsertQuery.Append(";"); } string query = string.Format(@" /* Bans_Insert */ insert into bans (ban_by_user_id, ban_expires, ban_reason, ban_revokemask, ban_last_edit) values(?BAN_BY_USER_ID, ?BAN_EXPIRES, ?BAN_REASON, ?BAN_REVOKEMASK, ?BAN_LAST_EDIT); select LAST_INSERT_ID(); select LAST_INSERT_ID() into @banid; {0} {1}", userIdInsertQuery, addressesInsertQuery); return(uint.Parse(Catalog.NewQuery(query) .With("BAN_BY_USER_ID", ban.ByUserId) .With("BAN_EXPIRES", ban.Expires) .With("BAN_REASON", ban.Reason) .With("BAN_REVOKEMASK", ban.RevokeMask) .With("BAN_LAST_EDIT", ban.LastEdit) .Read())); }
//--- Class Methods --- public XDoc RecentChanges_GetPageRecentChanges(PageBE page, DateTime since, bool recurse, bool createOnly, uint?limit) { string query = @"/* RecentChanges_GetPageRecentChanges */ SELECT rc_id, rc_comment, rc_cur_id, rc_last_oldid, rc_this_oldid, rc_namespace, rc_timestamp, rc_title, rc_type, rc_moved_to_ns, rc_moved_to_title, user_name AS rc_user_name, user_real_name as rc_full_name, (page_id IS NOT NULL) AS rc_page_exists, IF(page_id IS NULL, 0, page_revision) AS rc_revision, cmnt_id, cmnt_number, cmnt_content, cmnt_content_mimetype, (cmnt_delete_date IS NOT NULL) as cmnt_deleted, old_is_hidden FROM recentchanges LEFT JOIN old ON rc_this_oldid=old_id LEFT JOIN users ON rc_user=user_id LEFT JOIN comments ON ((rc_type=40 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_poster_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_create_date) OR (rc_type=41 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_last_edit_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_last_edit) OR (rc_type=42 AND cmnt_page_id=rc_cur_id AND rc_user=cmnt_deleter_user_id AND STR_TO_DATE(rc_timestamp,'%Y%m%e%H%i%s')=cmnt_delete_date)) JOIN ( ( SELECT page_id, page_namespace, page_title, page_revision FROM pages WHERE page_id = {0} ) UNION ( SELECT page_id, page_namespace, page_title, page_revision FROM pages where ({4}) AND (page_is_redirect=0 OR page_is_redirect IS NULL) ) )p ON rc_cur_id = p.page_id {1} {2} ORDER BY rc_timestamp DESC, rc_id DESC LIMIT {3}"; query = string.Format(query, page.ID, Nav_GetTimestampQuery(since), Nav_GetChangeTypeLimitQuery(createOnly), limit ?? UInt32.MaxValue, recurse ? string.Format("(page_title like '{1}%') AND (page_namespace={0} AND (('{1}' = '' AND page_title != '') OR (LEFT(page_title, CHAR_LENGTH('{1}') + 1) = CONCAT('{1}', '/') AND SUBSTRING(page_title, CHAR_LENGTH('{1}') + 2, 1) != '/')))", (int)page.Title.Namespace, DataCommand.MakeSqlSafe(page.Title.AsUnprefixedDbPath())) : string.Format("page_id={0}", page.ID)); return(Catalog.NewQuery(query).ReadAsXDoc("table", "change")); }
public override User GetUser(string user) { string name = "", email = ""; DataCommand cmd = _catalog.NewQuery(string.Format("SELECT username, email, usertype FROM {1}users WHERE username='******' AND block=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix)); XDoc result = cmd.ReadAsXDoc("users", "user"); if (result["user"].IsEmpty) { return(null); } name = result["user/username"].AsText; email = result["user/email"].AsText; Group group = new Group(result["user/usertype"].AsText); return(new User(name, email, new Group[] { group })); }
public override Group GetGroup(string group) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT name AS role_name FROM {1}role WHERE name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix)); XDoc role = cmd.ReadAsXDoc("roles", "role"); if (role["role/role_name"].IsEmpty) { return(null); } return(new Group(role["role/role_name"].AsText)); }
public override Group GetGroup(string group) { DataCommand cmd; // Compatability with 1.0 version switch (_version.Substring(0, 3)) { case "1.0": cmd = _catalog.NewQuery(string.Format("SELECT name FROM {1}usertypes where name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix)); break; default: cmd = _catalog.NewQuery(string.Format("SELECT name FROM {1}core_acl_aro_groups where name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(group), _prefix)); break; } XDoc groupXDoc = cmd.ReadAsXDoc("groups", "group"); if (groupXDoc["group/name"].IsEmpty) { return(null); } return(new Group(groupXDoc["group/name"].AsText)); }
public override User GetUser(string user) { string name = "", email = ""; Group group = new Group("anonymous user"); DataCommand cmd = _catalog.NewQuery(string.Format("SELECT u.name, u.mail AS email, r.name AS group_name FROM {1}users u LEFT OUTER JOIN {1}users_roles ur ON u.uid=ur.uid LEFT OUTER JOIN {1}role r ON ur.rid=r.rid WHERE u.name='{0}' LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix)); XDoc result = cmd.ReadAsXDoc("users", "user"); if (result["user"].IsEmpty) { return(null); } name = result["user/name"].AsText; email = result["user/email"].AsText; string groupName = result["user/group_name"].AsText; if (!string.IsNullOrEmpty(groupName)) { group = new Group(groupName); } return(new User(name, email, new Group[] { group })); }
public override bool CheckUserPassword(string user, string password) { DataCommand cmd = _catalog.NewQuery(string.Format("SELECT password FROM {1}users WHERE username='******' AND block=0 LIMIT 1", DataCommand.MakeSqlSafe(user), _prefix)); string remotePassword = cmd.Read(); if (remotePassword == null) { return(false); } if (remotePassword == password) { return(true); } if (remotePassword.Contains(":")) { // Extract Salt password string[] split = remotePassword.Split(':'); // Encode in MD5 byte[] textBytes = Encoding.UTF8.GetBytes(password + split[1]); byte[] hash = MD5.Create().ComputeHash(textBytes); // Create hash to Hex string StringBuilder s = new StringBuilder(); foreach (byte a in hash) { s.Append(a.ToString("x2").ToLower()); } if (String.Compare(split[0], s.ToString(), true) == 0) { return(true); } } return(false); }