Using a Drupal variable in SQL query

Question

I'm trying to stuff a variable into a SQL query to return a value to a page.

$sql = 'SELECT account FROM users WHERE uid = arg(1)';

Where arg(1) = the user currently being viewed. I am outputting arg(1) at the top of the page, so I know it's there, but Drupal doesn't seem to want to take it. I've tried escaping several different ways. Below is the full code

  function accountselect_getclientaccount() {
      global $user;
      $sql = 'SELECT account FROM users WHERE uid = arg(1)';
      $result = db_result(db_query($sql));
    return $result;
  }

Answer 1

You could try:

$uid = arg(1);
$result = db_result(db_query("SELECT account FROM {users} WHERE uid = %d", $uid));

Answer 2

To avoid sql-injection, you should use placeholders (see db_query for more info):

$result = db_query("SELECT * FROM {users} WHERE uid = %d", arg(1));

Also note that db_result is meant for single-column, single-result queries. You probably want to use db_fetch_object. Additionally, there isn't a column in the users table called account.

Answer 3

function accountselect_getclientaccount() {
  return (arg(0) == 'user') ? db_result(db_query('SELECT account FROM {users} WHERE uid = %d', arg(1))) : FALSE;
  }

I don't know why you're using the global $user. Maybe you should be using $user->uid instead of arg(1)? This would save you checking arg(1) is actually a user ID.

This might be better:

function accountselect_getclientaccount($account) {
  return db_result(db_query('SELECT account FROM {users} WHERE uid = %d', $account->uid));
  }

Also: see the user hook. It might be best practice to return the 'account' col on the load operation (if you're not doing that already)

http://api.drupal.org/api/function/hook_user/6