By timlie on

Hi,

I am searching for an SQL query for the following:

I store chosen user roles in a variable called account_roles.

Now I want a SELECT query that joins 3 tables, one of the tables is the user_roles table. In this select query one of the ON statements is account_role != user_role

I don't know how I can achieve this because account_roles could be an array (let's say authenticated and a custom role is stored in it).
How do I use this array in an SQL query??

Thanx a lot!

⋅ Categories: Drupal 6.x

Comments

matt_harrold’s picture

matt_harrold commented

http://www.w3schools.com/sql/sql_in.asp

Have a look at the SQL "IN" operator. You can use this to restrict roles to an array with a little string manipulation.

timlie’s picture

timlie commented

Thanx!

What I try to do is the following:

I am writing some custom code for the account_reminder module. I'd like to have a feature where I can choose which roles should get this reminder.

At this point I am struggeling with the query.

Account reminder has the following query:

$result = db_query("SELECT u.*, COALESCE(ar.msg_cnt, 0) AS msg_cnt, COALESCE(ar.msg_block, 0) AS msg_block, COALESCE(ar.last_reminder, %d) AS last_reminder FROM {users} u LEFT JOIN {account_reminder} ar ON u.uid = ar.uid WHERE u.uid <> 0 AND u.status = 1", time());

I am trying to nest an other join (users_roles table) with the where clause, users_roles.rid IN $roles, but I don't get it to work.

Someone an idea to point me in the right direction?

Thanx!

timlie’s picture

timlie commented

Could you explain a little bit more on "with a little string manipulation". I managed to make the query with the 3 tables but I am still struggling over the SQL "IN" operator.

Thanx!

matt_harrold’s picture

matt_harrold commented

The syntax look like:

SELECT name, city FROM contacts WHERE city NOT IN ('London','New York');

Assuming the array of roles to exclude is in the variable $user_roles ...

  // 1. create a string that looks like: 'administrator','manager','beefeater' from the array

  $roles_to_exclude="'".implode("','",$user_roles)."'";

  // 2. use NOT and IN to exclude those roles
  $sql_string="SELECT ... WHERE  ... AND account_role NOT IN ({$roles_to_exclude});";

  // 3. run the sql string as you normally would.
timlie’s picture

timlie commented

Thanx a lot!

Now it works!!!

heine’s picture

heine commented

This is often wrong. While in this case user_roles doesn't present an SQL injection hazard, never add 'SQL data' directly in you SQL queries. Instead, build the right amount of placeholders and pass all arguments in one array to db_query:


$excluded_roles = array('foo', 'bar', 'baz');

// Get a string with sufficient placeholders for the schema type 'varchar'.
// In our case this gets us the string: '%s', '%s', '%s'

$placeholders = db_placeholders($excluded_roles, 'varchar');

$sql = "SELECT ... WHERE whatever = %d AND something NOT IN ({$placeholders}) AND somethingelse = %d";

// Stuff everything in one array, order is the placeholder order.
$args = array_merge(array($whatever_value), $excluded_roles, array($somethingelse_value));

// We use the all values in one array form of db_query
$result = db_query($sql, $args);
matt_harrold’s picture

matt_harrold commented

That's a much more "Drupal Way" of covering all bases (security wise).

My tiny head barely negotiates SQL and PHP. I suppose most of my custom code would need cleaning prior to public release (I always assume I'm the only one running the code).

Thanks for the sample code guru Heine! It seems db_query with arguments is a powerful function.

matt_harrold’s picture

matt_harrold commented

I was just trying to help, but my dopey brain doesn't have the Drupal skillz of a Heine.

I suggest you try and rework your code to conform to that Drupalish db_query magic.

At that point, I reckon you'd not only have a solution, but a massively over engineered behemoth capable of beating down script kiddies and North Koreans.

Good luck.

liam morland’s picture

liam morland
Primary language English
Location Ontario, CA 🇨🇦
commented

An IN clause can be added to a query using SelectQuery::condition.

Pierre.Vriens’s picture

Pierre.Vriens commented

Another approach, now that we're 6 years later: maybe Forena reports can address this issue without writing your own custom module for it? It even comes with samples that are similar to this request ...

You don't need eyes to see, you need vision ...