- Patch #465190 by Heine: add check_plain() call.

3 files changed, 115 insertions, 71 deletions

diff --git a/modules/profile/profile.admin.inc b/modules/profile/profile.admin.inc
index 5ef53ffcb..bf1627f4f 100644
--- a/modules/profile/profile.admin.inc
+++ b/modules/profile/profile.admin.inc
@@ -17,7 +17,7 @@ function profile_admin_overview() {
 
   $form = array();
   $categories = array();
-  while ($field = db_fetch_object($result)) {
+  foreach ($result as $field) {
     // Collect all category information
     $categories[] = $field->category;
 
@@ -74,7 +74,13 @@ function profile_admin_overview_submit($form, &$form_state) {
       $weight = $form_state['values'][$fid]['weight'];
       $category = $form_state['values'][$fid]['category'];
       if ($weight != $form[$fid]['weight']['#default_value'] || $category != $form[$fid]['category']['#default_value']) {
-        db_query("UPDATE {profile_field} SET weight = %d, category = '%s' WHERE fid = %d", $weight, $category, $fid);
+        db_update('profile_field')
+          ->fields(array(
+            'weight' => $weight,
+            'category' => $category,
+          ))
+          ->condition('fid', $fid)
+          ->execute();
       }
     }
   }
@@ -169,7 +175,7 @@ function profile_field_form(&$form_state, $arg = NULL) {
     if (is_numeric($arg)) {
       $fid = $arg;
 
-      $edit = db_fetch_array(db_query('SELECT * FROM {profile_field} WHERE fid = %d', $fid));
+      $edit = db_query('SELECT * FROM {profile_field} WHERE fid = :fid', array('fid' => $fid))->fetchAssoc();
 
       if (!$edit) {
         drupal_not_found();
@@ -305,19 +311,27 @@ function profile_field_form_validate($form, &$form_state) {
   if (strtolower($form_state['values']['category']) == 'account') {
     form_set_error('category', t('The specified category name is reserved for use by Drupal.'));
   }
-  $args1 = array($form_state['values']['title'], $form_state['values']['category']);
-  $args2 = array($form_state['values']['name']);
-  $query_suffix = '';
+  $query = db_select('profile_field');
+  $query->fields('profile_field', array('fid'));
 
   if (isset($form_state['values']['fid'])) {
-    $args1[] = $args2[] = $form_state['values']['fid'];
-    $query_suffix = ' AND fid != %d';
+    $query->condition('fid', $form_state['values']['fid']);
   }
-
-  if (db_result(db_query("SELECT fid FROM {profile_field} WHERE title = '%s' AND category = '%s'" . $query_suffix, $args1))) {
+  $query_name = clone $query;
+
+  $title = $query
+    ->condition('title', $form_state['values']['title'])
+    ->condition('category', $form_state['values']['category'])
+    ->execute()
+    ->fetchField();
+  if ($title) {
     form_set_error('title', t('The specified title is already in use.'));
   }
-  if (db_result(db_query("SELECT fid FROM {profile_field} WHERE name = '%s'" . $query_suffix, $args2))) {
+  $name = $query_name
+    ->condition('name', $form_state['values']['name'])
+    ->execute()
+    ->fetchField();
+  if ($name) {
     form_set_error('name', t('The specified name is already in use.'));
   }
   if ($form_state['values']['visibility'] == PROFILE_HIDDEN) {
@@ -341,14 +355,19 @@ function profile_field_form_submit($form, &$form_state) {
     $form_state['values']['page'] = '';
   }
   if (!isset($form_state['values']['fid'])) {
-    db_query("INSERT INTO {profile_field} (title, name, explanation, category, type, weight, required, register, visibility, autocomplete, options, page) VALUES ('%s', '%s', '%s', '%s', '%s', %d, %d, %d, %d, %d, '%s', '%s')", $form_state['values']['title'], $form_state['values']['name'], $form_state['values']['explanation'], $form_state['values']['category'], $form_state['values']['type'], $form_state['values']['weight'], $form_state['values']['required'], $form_state['values']['register'], $form_state['values']['visibility'], $form_state['values']['autocomplete'], $form_state['values']['options'], $form_state['values']['page']);
-
+    // Remove all elements that are not profile_field columns.
+    $values = array_intersect_key($form_state['values'], array_flip(array('type', 'category', 'title', 'name', 'explanation', 'visibility', 'page', 'weight', 'autocomplete', 'required', 'register', 'options')));
+    db_insert('profile_field')
+      ->fields($values)
+      ->execute();
     drupal_set_message(t('The field has been created.'));
     watchdog('profile', 'Profile field %field added under category %category.', array('%field' => $form_state['values']['title'], '%category' => $form_state['values']['category']), WATCHDOG_NOTICE, l(t('view'), 'admin/user/profile'));
   }
   else {
-    db_query("UPDATE {profile_field} SET title = '%s', name = '%s', explanation = '%s', category = '%s', weight = %d, required = %d, register = %d, visibility = %d, autocomplete = %d, options = '%s', page = '%s' WHERE fid = %d", $form_state['values']['title'], $form_state['values']['name'], $form_state['values']['explanation'], $form_state['values']['category'], $form_state['values']['weight'], $form_state['values']['required'], $form_state['values']['register'], $form_state['values']['visibility'], $form_state['values']['autocomplete'], $form_state['values']['options'], $form_state['values']['page'], $form_state['values']['fid']);
-
+    db_update('profile_field')
+      ->fields($form_state['values'])
+      ->condition('fid', $form_state['values']['fid'])
+      ->exeucte();
     drupal_set_message(t('The field has been updated.'));
   }
   cache_clear_all();
@@ -362,7 +381,7 @@ function profile_field_form_submit($form, &$form_state) {
  * Menu callback; deletes a field from all user profiles.
  */
 function profile_field_delete(&$form_state, $fid) {
-  $field = db_fetch_object(db_query("SELECT title FROM {profile_field} WHERE fid = %d", $fid));
+  $field = db_query("SELECT title FROM {profile_field} WHERE fid = :fid", array(':fid' => $fid))->fetchObject();
   if (!$field) {
     drupal_not_found();
     return;
@@ -380,8 +399,12 @@ function profile_field_delete(&$form_state, $fid) {
  * Process a field delete form submission.
  */
 function profile_field_delete_submit($form, &$form_state) {
-  db_query('DELETE FROM {profile_field} WHERE fid = %d', $form_state['values']['fid']);
-  db_query('DELETE FROM {profile_value} WHERE fid = %d', $form_state['values']['fid']);
+  db_delete('profile_field')
+    ->condition('fid', $form_state['values']['fid'])
+    ->execute();
+  db_delete('profile_value')
+    ->condition('fid', $form_state['values']['fid'])
+    ->execute();
 
   cache_clear_all();
 
@@ -398,7 +421,7 @@ function profile_field_delete_submit($form, &$form_state) {
 function profile_admin_settings_autocomplete($string) {
   $matches = array();
   $result = db_query_range("SELECT category FROM {profile_field} WHERE LOWER(category) LIKE LOWER(:category)", array(':category' => $string . '%'), 0, 10);
-  while ($data = db_fetch_object($result)) {
+  foreach ($result as $data) {
     $matches[$data->category] = check_plain($data->category);
   }
   drupal_json($matches);
diff --git a/modules/profile/profile.module b/modules/profile/profile.module
index f1b4d0c8a..e96fb33c2 100644
--- a/modules/profile/profile.module
+++ b/modules/profile/profile.module
@@ -140,8 +140,8 @@ function profile_block_list() {
 function profile_block_configure($delta = '') {
   // Compile a list of fields to show
   $fields = array();
-  $result = db_query('SELECT name, title, weight, visibility FROM {profile_field} WHERE visibility IN (%d, %d) ORDER BY weight', PROFILE_PUBLIC, PROFILE_PUBLIC_LISTINGS);
-  while ($record = db_fetch_object($result)) {
+  $result = db_query('SELECT name, title, weight, visibility FROM {profile_field} WHERE visibility IN (:visibility) ORDER BY weight', array(':visibility' => array(PROFILE_PUBLIC, PROFILE_PUBLIC_LISTINGS)));
+  foreach ($result as $record) {
     $fields[$record->name] = check_plain($record->title);
   }
   $fields['user_profile'] = t('Link to full user profile');
@@ -175,8 +175,8 @@ function profile_block_view($delta = '') {
       if ($use_fields = variable_get('profile_block_author_fields', array())) {
         // Compile a list of fields to show.
         $fields = array();
-        $result = db_query('SELECT name, title, type, visibility, weight FROM {profile_field} WHERE visibility IN (%d, %d) ORDER BY weight', PROFILE_PUBLIC, PROFILE_PUBLIC_LISTINGS);
-        while ($record = db_fetch_object($result)) {
+        $result = db_query('SELECT name, title, weight, visibility FROM {profile_field} WHERE visibility IN (:visibility) ORDER BY weight', array(':visibility' => array(PROFILE_PUBLIC, PROFILE_PUBLIC_LISTINGS)));
+        foreach ($result as $record) {
           // Ensure that field is displayed only if it is among the defined block fields and, if it is private, the user has appropriate permissions.
           if (isset($use_fields[$record->name]) && $use_fields[$record->name]) {
             $fields[] = $record;
@@ -258,7 +258,9 @@ function profile_user_cancel(&$edit, &$account, $method) {
   switch ($method) {
     case 'user_cancel_reassign':
     case 'user_cancel_delete':
-      db_delete('profile_value')->condition('uid', $account->uid)->execute();
+      db_delete('profile_value')
+        ->condition('uid', $account->uid)
+        ->execute();
       break;
   }
 }
@@ -277,12 +279,17 @@ function profile_user_load($users) {
 
 function profile_save_profile(&$edit, &$user, $category, $register = FALSE) {
   $result = _profile_get_fields($category, $register);
-  while ($field = db_fetch_object($result)) {
+  foreach ($result as $field) {
     if (_profile_field_serialize($field->type)) {
       $edit[$field->name] = serialize($edit[$field->name]);
     }
-    db_query("DELETE FROM {profile_value} WHERE fid = %d AND uid = %d", $field->fid, $user->uid);
-    db_query("INSERT INTO {profile_value} (fid, uid, value) VALUES (%d, %d, '%s')", $field->fid, $user->uid, $edit[$field->name]);
+    db_merge('profile_value')
+      ->key(array(
+        'fid' => $field->fid,
+        'uid' => $user->uid,
+      ))
+      ->fields(array('value' => $edit[$field->name]))
+      ->execute();
     // Mark field as handled (prevents saving to user->data).
     $edit[$field->name] = NULL;
   }
@@ -344,11 +351,11 @@ function profile_view_profile(&$user) {
     $result = db_query('SELECT * FROM {profile_field} WHERE visibility <> %d ORDER BY category, weight', PROFILE_HIDDEN);
   }
   else {
-    $result = db_query('SELECT * FROM {profile_field} WHERE visibility <> %d AND visibility <> %d ORDER BY category, weight', PROFILE_PRIVATE, PROFILE_HIDDEN);
+    $result = db_query('SELECT * FROM {profile_field} WHERE visibility <> :private AND visibility <> :hidden ORDER BY category, weight', array(':private' => PROFILE_PRIVATE, ':hidden' => PROFILE_HIDDEN));
   }
 
   $fields = array();
-  while ($field = db_fetch_object($result)) {
+  foreach ($result as $field) {
     if ($value = profile_view_field($user, $field)) {
       $title = ($field->type != 'checkbox') ? check_plain($field->title) : NULL;
 
@@ -389,7 +396,7 @@ function profile_form_profile($edit, $user, $category, $register = FALSE) {
   $result = _profile_get_fields($category, $register);
   $weight = 1;
   $fields = array();
-  while ($field = db_fetch_object($result)) {
+  foreach ($result as $field) {
     $category = $field->category;
     if (!isset($fields[$category])) {
       $fields[$category] = array('#type' => 'fieldset', '#title' => check_plain($category), '#weight' => $weight++);
@@ -473,7 +480,7 @@ function _profile_update_user_fields($fields, $account) {
 
 function profile_validate_profile($edit, $category) {
   $result = _profile_get_fields($category);
-  while ($field = db_fetch_object($result)) {
+  foreach ($result as $field) {
     if ($edit[$field->name]) {
       if ($field->type == 'url') {
         if (!valid_url($edit[$field->name], TRUE)) {
@@ -492,7 +499,7 @@ function profile_validate_profile($edit, $category) {
 function profile_categories() {
   $result = db_query("SELECT DISTINCT(category) FROM {profile_field}");
   $data = array();
-  while ($category = db_fetch_object($result)) {
+  foreach ($result as $category) {
     $data[] = array(
       'name' => $category->category,
       'title' => $category->category,
@@ -512,7 +519,10 @@ function profile_category_access($account, $category) {
     return TRUE;
   }
   else {
-    $category_visible = (bool) db_query_range('SELECT 1 FROM {profile_field} WHERE category = :category AND visibility <> :visibility', array(':category' => $category, ':visibility' => PROFILE_HIDDEN), 0, 1)->fetchField();
+    $category_visible = (bool) db_query_range('SELECT 1 FROM {profile_field} WHERE category = :category AND visibility <> :visibility', array(
+      ':category' => $category,
+      ':visibility' => PROFILE_HIDDEN
+    ), 0, 1)->fetchField();
     return user_edit_access($account) && $category_visible;
   }
 }
@@ -599,23 +609,21 @@ function _profile_field_serialize($type = NULL) {
 }
 
 function _profile_get_fields($category, $register = FALSE) {
-  $args = array();
-  $sql = 'SELECT * FROM {profile_field} WHERE ';
-  $filters = array();
+  $query = db_select('profile_field');
   if ($register) {
-    $filters[] = 'register = 1';
+    $query->condition('register', 1);
   }
   else {
-    // Use LOWER('%s') instead of PHP's strtolower() to avoid UTF-8 conversion issues.
-    $filters[] = "LOWER(category) = LOWER('%s')";
-    $args[] = $category;
+    // Use LOWER(:category) instead of PHP's strtolower() to avoid UTF-8 conversion issues.
+    $query->where('LOWER(category) = LOWER(:category)', array(':category' => $category));
   }
   if (!user_access('administer users')) {
-    $filters[] = 'visibility != %d';
-    $args[] = PROFILE_HIDDEN;
+    $query->condition('visibility', PROFILE_HIDDEN);
   }
-  $sql .= implode(' AND ', $filters);
-  $sql .= ' ORDER BY category, weight';
-  return db_query($sql, $args);
+  return $query
+    ->fields('profile_field')
+    ->orderBy('category', 'ASC')
+    ->orderBy('weight', 'ASC')
+    ->execute();
 }
 
diff --git a/modules/profile/profile.pages.inc b/modules/profile/profile.pages.inc
index 572d2365c..8f69d888b 100644
--- a/modules/profile/profile.pages.inc
+++ b/modules/profile/profile.pages.inc
@@ -13,7 +13,7 @@ function profile_browse() {
   // Ensure that the path is converted to 3 levels always.
   list(, $name, $value) = array_pad(explode('/', $_GET['q'], 3), 3, '');
 
-  $field = db_fetch_object(db_query("SELECT DISTINCT(fid), type, title, page, visibility FROM {profile_field} WHERE name = '%s'", $name));
+  $field = db_query("SELECT DISTINCT(fid), type, title, page, visibility FROM {profile_field} WHERE name = :name", array(':name' => $name))->fetchObject();
 
   if ($name && $field->fid) {
     // Only allow browsing of fields that have a page title set.
@@ -28,37 +28,45 @@ function profile_browse() {
     }
 
     // Compile a list of fields to show.
-    $fields = array();
-    $result = db_query('SELECT name, title, type, weight, page FROM {profile_field} WHERE fid <> %d AND visibility = %d ORDER BY weight', $field->fid, PROFILE_PUBLIC_LISTINGS);
-    while ($record = db_fetch_object($result)) {
-      $fields[] = $record;
-    }
+    $fields = db_query('SELECT name, title, type, weight, page FROM {profile_field} WHERE fid <> :fid AND visibility = :visibility ORDER BY weight', array(
+      ':fid' => $field->fid,
+      ':visibility' => PROFILE_PUBLIC_LISTINGS,
+    ))->fetchAll();
+
+    $query = db_select('users')->extend('PagerDefault');
+    $query->join('profile_value', 'v', 'u.uid = v.uid');
+    $query
+      ->fields('u', array('uid', 'access'))
+      ->condition('v.fid', $field->fid)
+      ->condition('u.access', 0, '<>')
+      ->condition('u.status', 0, '<>')
+      ->orderBy('u.access', 'DESC');
 
     // Determine what query to use:
     $arguments = array($field->fid);
     switch ($field->type) {
       case 'checkbox':
-        $query = 'v.value = 1';
+        $query->condition('v.value', 1);
         break;
       case 'textfield':
       case 'selection':
-        $query = "v.value = '%s'";
-        $arguments[] = $value;
+        $query->condition('v.value', $value);
         break;
       case 'list':
-        $query = "v.value LIKE '%%%s%%'";
-        $arguments[] = $value;
+        $query->condition('v.value', '%' . $value . '%', 'LIKE');
         break;
       default:
         drupal_not_found();
         return;
     }
 
-    // Extract the affected users:
-    $result = pager_query("SELECT u.uid, u.access FROM {users} u INNER JOIN {profile_value} v ON u.uid = v.uid WHERE v.fid = %d AND $query AND u.access <> 0 AND u.status <> 0 ORDER BY u.access DESC", 20, 0, NULL, $arguments)->fetchAllAssoc('uid');
+    $uids = $query
+      ->limit(20)
+      ->execute()
+      ->fetchCol();
 
     // Load the users.
-    $users = user_load_multiple(array_keys($result));
+    $users = user_load_multiple($uids);
 
     $content = '';
     foreach ($users as $account) {
@@ -83,15 +91,20 @@ function profile_browse() {
   }
   else {
     // Compile a list of fields to show.
-    $fields = array();
-    $result = db_query('SELECT name, title, type, weight, page, visibility FROM {profile_field} WHERE visibility = %d ORDER BY category, weight', PROFILE_PUBLIC_LISTINGS);
-    while ($record = db_fetch_object($result)) {
-      $fields[] = $record;
-    }
+    $fields = db_query('SELECT name, title, type, weight, page, visibility FROM {profile_field} WHERE visibility = :visibility ORDER BY category, weight', array(':visibility' => PROFILE_PUBLIC_LISTINGS))->fetchAll();
 
     // Extract the affected users:
-    $result = pager_query('SELECT uid, access FROM {users} WHERE uid > 0 AND status <> 0 AND access <> 0 ORDER BY access DESC', 20, 0, NULL)->fetchAllAssoc('uid');
-    $users = user_load_multiple(array_keys($result));
+    $query = db_select('users', 'u')->extend('PagerDefault');
+    $uids = $query
+      ->fields('u', array('uid', 'access'))
+      ->condition('u.uid', 0, '>')
+      ->condition('u.status', 0, '>')
+      ->condition('u.access', 0, '>')
+      ->orderBy('u.access', 'DESC')
+      ->limit(20)
+      ->execute()
+      ->fetchCol();
+    $users = user_load_multiple($uids);
     $content = '';
     foreach ($users as $account) {
       $profile = _profile_update_user_fields($fields, $account);
@@ -100,7 +113,7 @@ function profile_browse() {
     $output = theme('profile_wrapper', $content);
     $output .= theme('pager', NULL);
 
-    drupal_set_title(t('User list'), PASS_THROUGH);
+    drupal_set_title(t('User list'));
     return $output;
   }
 }
@@ -112,12 +125,12 @@ function profile_autocomplete($field, $string) {
   $matches = array();
   $autocomplete_field = (bool) db_query_range("SELECT 1 FROM {profile_field} WHERE fid = :fid AND autocomplete = 1", array(':fid' => $field), 0, 1)->fetchField();
   if ($autocomplete_field) {
-    $result = db_query_range("SELECT value FROM {profile_value} WHERE fid = :fid AND LOWER(value) LIKE LOWER(:value) GROUP BY value ORDER BY value ASC", array(
+    $values = db_query_range("SELECT value FROM {profile_value} WHERE fid = :fid AND LOWER(value) LIKE LOWER(:value) GROUP BY value ORDER BY value ASC", array(
       ':fid' => $field,
       ':value' => $string . '%',
-    ), 0, 10);
-    while ($data = db_fetch_object($result)) {
-      $matches[$data->value] = check_plain($data->value);
+    ), 0, 10)->fetchCol();
+    foreach ($values as $value) {
+      $matches[$value] = check_plain($value);
     }
   }