From 8405f0d324fd42bec2f775986e69e5d8cf548ebf Mon Sep 17 00:00:00 2001 From: Meik Sievertsen Date: Sat, 12 Aug 2006 13:14:39 +0000 Subject: sql_in_set changes git-svn-id: file:///svn/phpbb/trunk@6271 89ea8834-ac86-4346-8a33-228a782c2dd0 --- phpBB/includes/functions_user.php | 110 +++++++++++++++++++------------------- 1 file changed, 55 insertions(+), 55 deletions(-) (limited to 'phpBB/includes/functions_user.php') diff --git a/phpBB/includes/functions_user.php b/phpBB/includes/functions_user.php index bc1c41b087..c03e92d0b0 100644 --- a/phpBB/includes/functions_user.php +++ b/phpBB/includes/functions_user.php @@ -34,7 +34,7 @@ function user_get_id_name(&$user_id_ary, &$username_ary) $$which_ary = array($$which_ary); } - $sql_in = ($which_ary == 'user_id_ary') ? array_map('intval', $$which_ary) : preg_replace('#^\s*(.*)\s*$#e', "\"'\" . \$db->sql_escape('\\1') . \"'\"", $$which_ary); + $sql_in = ($which_ary == 'user_id_ary') ? array_map('intval', $$which_ary) : $$which_ary; unset($$which_ary); $user_id_ary = $username_ary = array(); @@ -42,8 +42,8 @@ function user_get_id_name(&$user_id_ary, &$username_ary) // Grab the user id/username records $sql_where = ($which_ary == 'user_id_ary') ? 'user_id' : 'username'; $sql = 'SELECT user_id, username - FROM ' . USERS_TABLE . " - WHERE $sql_where IN (" . implode(', ', $sql_in) . ')'; + FROM ' . USERS_TABLE . ' + WHERE ' . $db->sql_in_set($sql_where, $sql_in); $result = $db->sql_query($sql); if (!($row = $db->sql_fetchrow($result))) @@ -307,7 +307,7 @@ function user_delete($mode, $user_id, $post_username = false) { $sql = 'SELECT topic_id, topic_replies, topic_replies_real FROM ' . TOPICS_TABLE . ' - WHERE topic_id IN (' . implode(', ', array_keys($topic_id_ary)) . ')'; + WHERE ' . $db->sql_in_set('topic_id', array_keys($topic_id_ary)); $result = $db->sql_query($sql); $del_topic_ary = array(); @@ -323,7 +323,7 @@ function user_delete($mode, $user_id, $post_username = false) if (sizeof($del_topic_ary)) { $sql = 'DELETE FROM ' . TOPICS_TABLE . ' - WHERE topic_id IN (' . implode(', ', $del_topic_ary) . ')'; + WHERE ' . $db->sql_in_set('topic_id', $del_topic_ary); $db->sql_query($sql); } } @@ -528,24 +528,23 @@ function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reas $username = trim($username); if ($username != '') { - $sql_usernames[] = "'" . $db->sql_escape($username) . "'"; + $sql_usernames[] = strtolower($username); } } - $sql_usernames = implode(', ', $sql_usernames); // Make sure we have been given someone to ban - if (empty($sql_usernames)) + if (!sizeof($sql_usernames)) { trigger_error($user->lang['NO_USER_SPECIFIED']); } $sql = 'SELECT user_id FROM ' . USERS_TABLE . ' - WHERE username IN (' . $sql_usernames . ')'; + WHERE ' . $db->sql_in_set('LOWER(username)', $sql_usernames); if (sizeof($founder)) { - $sql .= ' AND user_id NOT IN (' . implode(', ', array_keys($founder)) . ')'; + $sql .= ' AND ' . $db->sql_in_set('user_id', array_keys($founder), true); } $result = $db->sql_query($sql); @@ -762,17 +761,11 @@ function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reas switch ($mode) { case 'user': - $sql_where = (in_array('*', $banlist_ary)) ? '' : 'WHERE session_user_id IN (' . implode(', ', $banlist_ary) . ')'; + $sql_where = (in_array('*', $banlist_ary)) ? '' : 'WHERE ' . $db->sql_in_set('session_user_id', $banlist_ary); break; case 'ip': - $banlist_ary_sql = array(); - - foreach ($banlist_ary as $ban_entry) - { - $banlist_ary_sql[] = "'" . $db->sql_escape($ban_entry) . "'"; - } - $sql_where = 'WHERE session_ip IN (' . implode(', ', $banlist_ary_sql) . ')'; + $sql_where = 'WHERE ' . $db->sql_in_set('session_ip', $banlist_ary); break; case 'email': @@ -780,12 +773,12 @@ function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reas foreach ($banlist_ary as $ban_entry) { - $banlist_ary_sql[] = "'" . $db->sql_escape(str_replace('*', '%', $ban_entry)) . "'"; + $banlist_ary_sql[] = (string) str_replace('*', '%', $ban_entry); } $sql = 'SELECT user_id FROM ' . USERS_TABLE . ' - WHERE user_email IN (' . implode(', ', $banlist_ary_sql) . ')'; + WHERE ' . $db->sql_in_set('user_email', $banlist_ary_sql); $result = $db->sql_query($sql); $sql_in = array(); @@ -798,7 +791,7 @@ function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reas } while ($row = $db->sql_fetchrow($result)); - $sql_where = 'WHERE session_user_id IN (' . implode(', ', $sql_in) . ")"; + $sql_where = 'WHERE ' . $db->sql_in_set('session_user_id', $sql_in); } $db->sql_freeresult($result); break; @@ -812,7 +805,7 @@ function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reas if ($mode == 'user') { - $sql = 'DELETE FROM ' . SESSIONS_KEYS_TABLE . ' ' . ((in_array('*', $banlist_ary)) ? '' : 'WHERE user_id IN (' . implode(', ', $banlist_ary) . ')'); + $sql = 'DELETE FROM ' . SESSIONS_KEYS_TABLE . ' ' . ((in_array('*', $banlist_ary)) ? '' : 'WHERE ' . $db->sql_in_set('user_id', $banlist_ary)); $db->sql_query($sql); } } @@ -847,30 +840,30 @@ function user_unban($mode, $ban) $ban = array($ban); } - $unban_sql = implode(', ', array_map('intval', $ban)); + $unban_sql = array_map('intval', $ban); - if ($unban_sql) + if (sizeof($unban_sql)) { // Grab details of bans for logging information later switch ($mode) { case 'user': $sql = 'SELECT u.username AS unban_info - FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . " b - WHERE b.ban_id IN ($unban_sql) - AND u.user_id = b.ban_userid"; + FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . ' b + WHERE ' . $db->sql_in_set('b.ban_id', $unban_sql) . ' + AND u.user_id = b.ban_userid'; break; case 'email': $sql = 'SELECT ban_email AS unban_info - FROM ' . BANLIST_TABLE . " - WHERE ban_id IN ($unban_sql)"; + FROM ' . BANLIST_TABLE . ' + WHERE ' . $db->sql_in_set('ban_id', $unban_sql); break; case 'ip': $sql = 'SELECT ban_ip AS unban_info - FROM ' . BANLIST_TABLE . " - WHERE ban_id IN ($unban_sql)"; + FROM ' . BANLIST_TABLE . ' + WHERE ' . $db->sql_in_set('ban_id', $unban_sql); break; } $result = $db->sql_query($sql); @@ -882,8 +875,8 @@ function user_unban($mode, $ban) } $db->sql_freeresult($result); - $sql = 'DELETE FROM ' . BANLIST_TABLE . " - WHERE ban_id IN ($unban_sql)"; + $sql = 'DELETE FROM ' . BANLIST_TABLE . ' + WHERE ' . $db->sql_in_set('ban_id', $unban_sql); $db->sql_query($sql); add_log('admin', 'LOG_UNBAN_' . strtoupper($mode), $l_unban_list); @@ -1580,7 +1573,7 @@ function group_user_add($group_id, $user_id_ary = false, $username_ary = false, // Remove users who are already members of this group $sql = 'SELECT user_id, group_leader FROM ' . USER_GROUP_TABLE . ' - WHERE user_id IN (' . implode(', ', $user_id_ary) . ") + WHERE ' . $db->sql_in_set('user_id', $user_id_ary) . " AND group_id = $group_id"; $result = $db->sql_query($sql); @@ -1636,7 +1629,7 @@ function group_user_add($group_id, $user_id_ary = false, $username_ary = false, { $sql = 'UPDATE ' . USER_GROUP_TABLE . ' SET group_leader = 1 - WHERE user_id IN (' . implode(', ', $update_id_ary) . ") + WHERE ' . $db->sql_in_set('user_id', $update_id_ary) . " AND group_id = $group_id"; $db->sql_query($sql); } @@ -1682,7 +1675,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, $sql = 'SELECT * FROM ' . GROUPS_TABLE . ' - WHERE group_name IN (' . implode(', ', preg_replace('#^(.*)$#', "'\\1'", $group_order)) . ')'; + WHERE ' . $db->sql_in_set('group_name', $group_order); $result = $db->sql_query($sql); $group_order_id = $special_group_data = array(); @@ -1711,7 +1704,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, // Get users default groups - we only need to reset default group membership if the group from which the user gets removed is set as default $sql = 'SELECT user_id, group_id FROM ' . USERS_TABLE . ' - WHERE user_id IN (' . implode(', ', $user_id_ary) . ")"; + WHERE ' . $db->sql_in_set('user_id', $user_id_ary); $result = $db->sql_query($sql); $default_groups = array(); @@ -1724,7 +1717,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, // What special group memberships exist for these users? $sql = 'SELECT g.group_id, g.group_name, ug.user_id FROM ' . USER_GROUP_TABLE . ' ug, ' . GROUPS_TABLE . ' g - WHERE ug.user_id IN (' . implode(', ', $user_id_ary) . ") + WHERE ' . $db->sql_in_set('ug.user_id', $user_id_ary) . " AND g.group_id = ug.group_id AND g.group_id <> $group_id AND g.group_type = " . GROUP_SPECIAL . ' @@ -1760,7 +1753,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, // Ok, get the original avatar data from users having an uploaded one (we need to remove these from the filesystem) $sql = 'SELECT user_id, user_avatar FROM ' . USERS_TABLE . ' - WHERE user_id IN (' . implode(', ', $sql_where_ary[$gid]) . ') + WHERE ' . $db->sql_in_set('user_id', $sql_where_ary[$gid]) . ' AND user_avatar_type = ' . AVATAR_UPLOAD; $result = $db->sql_query($sql); @@ -1772,7 +1765,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, } $sql = 'UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $special_group_data[$gid]) . ' - WHERE user_id IN (' . implode(', ', $sql_where_ary[$gid]) . ')'; + WHERE ' . $db->sql_in_set('user_id', $sql_where_ary[$gid]); $db->sql_query($sql); } } @@ -1780,7 +1773,7 @@ function group_user_del($group_id, $user_id_ary = false, $username_ary = false, $sql = 'DELETE FROM ' . USER_GROUP_TABLE . " WHERE group_id = $group_id - AND user_id IN (" . implode(', ', $user_id_ary) . ')'; + AND " . $db->sql_in_set('user_id', $user_id_ary); $db->sql_query($sql); // Clear permissions cache of relevant users @@ -1825,7 +1818,7 @@ function group_user_attributes($action, $group_id, $user_id_ary = false, $userna $sql = 'UPDATE ' . USER_GROUP_TABLE . ' SET group_leader = ' . (($action == 'promote') ? 1 : 0) . " WHERE group_id = $group_id - AND user_id IN (" . implode(', ', $user_id_ary) . ')'; + AND " . $db->sql_in_set('user_id', $user_id_ary); $db->sql_query($sql); $log = ($action == 'promote') ? 'LOG_GROUP_PROMOTED' : 'LOG_GROUP_DEMOTED'; @@ -1838,7 +1831,7 @@ function group_user_attributes($action, $group_id, $user_id_ary = false, $userna WHERE ug.group_id = ' . $group_id . ' AND ug.user_pending = 1 AND ug.user_id = u.user_id - AND ug.user_id IN (' . implode(', ', $user_id_ary) . ')'; + AND ' . $db->sql_in_set('ug.user_id', $user_id_ary); $result = $db->sql_query($sql); $user_id_ary = $email_users = array(); @@ -1857,7 +1850,7 @@ function group_user_attributes($action, $group_id, $user_id_ary = false, $userna $sql = 'UPDATE ' . USER_GROUP_TABLE . " SET user_pending = 0 WHERE group_id = $group_id - AND user_id IN (" . implode(', ', $user_id_ary) . ')'; + AND " . $db->sql_in_set('user_id', $user_id_ary); $db->sql_query($sql); // Send approved email to users... @@ -1963,7 +1956,7 @@ function group_set_user_default($group_id, $user_id_ary, $group_attributes = fal // Ok, get the original avatar data from users having an uploaded one (we need to remove these from the filesystem) $sql = 'SELECT user_id, user_avatar FROM ' . USERS_TABLE . ' - WHERE user_id IN (' . implode(', ', $user_id_ary) . ') + WHERE ' . $db->sql_in_set('user_id', $user_id_ary) . ' AND user_avatar_type = ' . AVATAR_UPLOAD; $result = $db->sql_query($sql); @@ -1975,7 +1968,7 @@ function group_set_user_default($group_id, $user_id_ary, $group_attributes = fal } $sql = 'UPDATE ' . USERS_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . ' - WHERE user_id IN (' . implode(', ', $user_id_ary) . ')'; + WHERE ' . $db->sql_in_set('user_id', $user_id_ary); $db->sql_query($sql); } @@ -2016,22 +2009,29 @@ function group_memberships($group_id_ary = false, $user_id_ary = false, $return_ return true; } + if ($user_id_ary) + { + $user_id_ary = (!is_array($user_id_ary)) ? array($user_id_ary) : $user_id_ary; + } + + if ($group_id_ary) + { + $group_id_ary = (!is_array($group_id_ary)) ? array($group_id_ary) : $group_id_ary; + } + $sql = 'SELECT ug.*, u.username, u.user_email FROM ' . USER_GROUP_TABLE . ' ug, ' . USERS_TABLE . ' u WHERE ug.user_id = u.user_id AND '; - if ($group_id_ary && $user_id_ary) - { - $sql .= " ug.group_id " . ((is_array($group_id_ary)) ? ' IN (' . implode(', ', $group_id_ary) . ')' : " = $group_id_ary") . " - AND ug.user_id " . ((is_array($user_id_ary)) ? ' IN (' . implode(', ', $user_id_ary) . ')' : " = $user_id_ary"); - } - else if ($group_id_ary) + if ($group_id_ary) { - $sql .= " ug.group_id " . ((is_array($group_id_ary)) ? ' IN (' . implode(', ', $group_id_ary) . ')' : " = $group_id_ary"); + $sql .= ' ' . $db->sql_in_set('ug.group_id', $group_id_ary); } - else if ($user_id_ary) + + if ($user_id_ary) { - $sql .= " ug.user_id " . ((is_array($user_id_ary)) ? ' IN (' . implode(', ', $user_id_ary) . ')' : " = $user_id_ary"); + $sql .= ($group_id_ary) ? ' AND ' : ' '; + $sql .= $db->sql_in_set('ug.user_id', $user_id_ary); } $result = ($return_bool) ? $db->sql_query_limit($sql, 1) : $db->sql_query($sql); -- cgit v1.2.1