install fails with postgresql 8.1

This is related in file includes/bootstrap.inc in this function:

/**
 * Perform an access check for a given mask and rule type. Rules are usually
 * created via admin/user/rules page.
 *
 * If any allow rule matches, access is allowed. Otherwise, if any deny rule
 * matches, access is denied.  If no rule matches, access is allowed.
 *
 * @param $type string
 *   Type of access to check: Allowed values are:
 *     - 'host': host name or IP address
 *     - 'mail': e-mail address
 *     - 'user': username
 * @param $mask string
 *   String or mask to test: '_' matches any character, '%' matches any
 *   number of characters.
 * @return bool
 *   TRUE if access is denied, FALSE if access is allowed.
 */
function drupal_is_denied($type, $mask) {
  // Because this function is called for every page request, both cached
  // and non-cached pages, we tried to optimize it as much as possible.
  // We deny access if the only matching records in the {access} table have
  // status 0. If any have status 1, or if there are no matching records,
  // we allow access. So, select matching records in decreasing order of
  // 'status', returning NOT(status) for the first. If any have status 1,
  // they come first, and we return NOT(status) = 0 (allowed). Otherwise,
  // if we have some with status 0, we return 1 (denied). If no matching
  // records, we get no return from db_result, so we return (bool)NULL = 0
  // (allowed).
  // The use of ORDER BY / LIMIT is more efficient than "MAX(status) = 0"
  // in PostgreSQL <= 8.0.
  return (bool) db_result(db_query_range("SELECT NOT(status) FROM {access} WHERE type = '%s' AND LOWER('%s') LIKE LOWER(mask) ORDER BY status DESC", $type, $mask, 0, 1));
}

NOT() don't work on PostgreSQL (8.1 in my installation) - status need be boolean type but is int2 type... This function need rewrite...

I have done some examination. Correct syntax for both (MySQL and PostgreSQL) is:

  $res = (bool) db_result(db_query_range("SELECT NOT status=1 FROM {access} WHERE type = '%s' AND LOWER('%s') LIKE LOWER(mask) ORDER BY status DESC", $type, $mask, 0, 1));

But there is another problem - PostgreSQL returns always 't' for true and 'f' for false which is still true after conversion to boolean.

Ok i have created patch. Need test in mysql.

I have better patch and i think this it is ANSI92 SQL.

Here's a simpler way to fix the problem. Works in pgsql and mysql.

Have you tried this patch in real situation? PostgreSQL return 't' or 'f' and (bool) 't' or bool ('f') is still true... (i have tried this on PostgreSQL 8.1 and 7.4). Read this issue - http://drupal.org/node/81078 - is related.

@havran: not sure what you're saying. The statement SELECT (status = 0) always returns either a boolean (t/f) or integer (1/0) value (I'm not an expert on which database systems return which type - but I'm pretty sure that they all return one of these two types). I have tested this statement on MySQL 4.1 and PostgreSQL 8.1, and it works great. I really don't see what's wrong with the value that this statement yields.

It would be great if you could also fix up http://drupal.org/node/81078 to use the approach that my patch uses (which is the approach that was originally suggested for that issue as well).

Ok. Here is real situation.

  // 1=1 is TRUE - PostgreSQL return string 't' MySQL return number 1
  $res = db_result(db_queryd('SELECT 1=1'));

  // databse returns
  echo '<hr />Return: <br />';
  var_dump($res); 
  // we have result 't' (TRUE) php after conversion to boolean return TRUE (looks ok)
  // MySQL: (bool)1 = TRUE which is ok
  echo '<hr />Return (bool): <br />';
  var_dump((bool)$res);


  // 0=1 is FALSE - PostgreSQL return string 'f' MySQL number 0
  $res = db_result(db_queryd('SELECT 0=1'));

  // databse returns
  echo '<hr />Return: <br />';
  var_dump($res);
  // PostgreSQL: we have result 'f' (FALSE) but php after conversion to boolean return TRUE (because string with content is always true)
  // MySQL: (bool)0 = FALSE which is ok
  echo '<hr />Return (bool): <br />';
  var_dump((bool)$res);

The last patch posted looks okay. Please reopen if it wasn't. (And do change the issue status if such reviews mean patches are not suitable.)

With this patch i get always Sorry, 127.0.0.1 has been banned.. I have tried value 0 and value 1 in status column... Real test is real test...

Attached patch work correct.

Committed to CVS HEAD. Thanks.

Thanks for the clarification, Havran - and good to see that the final patch that was committed was the one that worked best.

However, I think that the issue you pointed out, of pgsql returning a string value of 't' or 'f' for boolean values, needs to be seriously looked into. IMO, this is a bug. I would like to investigate why such values are ending up as strings (and not as booleans or ints), and what is responsible. The responsibility either lies with:

1. PostgreSQL (i.e. pgsql actually returns a 't' or 'f' string as its raw output)
2. PHP (i.e. the PHP pgsql driver/wrapper messes up the pgsql values, and turns them into 't' or 'f' strings)
3. Drupal (i.e. the Drupal pgsql compatibility layer messes up the values)

As far as I can see, Drupal is not responsible. The pgsql layer in Drupal is pretty thin, and I don't see how booleans are handled differently in Drupal for pgsql than for mysql. However, I haven't investigated thoroughly. Obviously, if the problem lies with Drupal, then we need to fix it.

If the problem lies with either PHP or with pgSQL, then we should submit bug reports to these projects, and in the meantime, we should modify our pgSQL compatibility layer so as to 'translate' boolean 't' or 'f' strings into booleans or ints, and hence to avoid this bug.

Either way, I really DON'T want to see any more patches that use CASE WHEN x = 0 THEN 1 ELSE 0 END SQL. This is a long-winded and annoying way to write SQL, and we shouldn't be forced to write our queries like this. Anyone else who wants to help me in investigating this problem, feel free to help out.

Hi. I search in PHP bug database and i discover this:

http://bugs.php.net/bug.php?id=29213&edit=1

This is not bug it is feature. For PostgreSQL booleans this seems be only way for compatibility (or we need some additional code in DB layer).

This is interesting too:

http://www.computer-books.us/sql_0004.php

There is no simple boolean TRUE/FALSE type in databases - only variant integer or text types, interpreted as boolean :).