using an external database but only admin is accepted.

[jberanek]

I use an external database for the users. I can manage the database with the user "admin" but no other user is accepted.

In config.inc.php I added:

/***********************************************
 * Authentication settings - read AUTHENTICATION
 ***********************************************/

// NOTE: if you are using the 'joomla', 'saml' or 'wordpress' authentication type,
// then you must use the corresponding session scheme.

$auth["type"] = "db_ext"; // How to validate the user/password. One of
                      // "auth_basic", "cas", "config", "crypt", "db", "db_ext", "imap",
                      // "imap_php",  "joomla", "ldap", "nis", "none", "nw", "pop3",
                      // "saml", "smtp" or "wordpress".

$auth["session"] = "php"; // How to get and keep the user ID. One of
                          // "cas", "cookie", "host", "http", "ip", "joomla", "nt",
                          // "omni", "php", "remote_user", "saml" or "wordpress".

and:

//External DB (db_ext) Authentication
//-----------------------------------

//Authentication method, usable on the Internet.

//It works by using one of the installer's own databases. The database can
//be any database system for which MRBS has a database abstraction. You can
//configure how to connect to the database server, what kind of database
//system it is (mysql, mysqli, pgsql currently), the database/table to
//use and the names of the "user" and "password" columns.

//The configuration items are held in config.inc.php and are as follows:

$auth['db_ext']['db_system'] = 'mysqli'; /* Or 'mysql', 'pgsql' */
$auth['db_ext']['db_host'] = 'mysql5.XXXXX';

//The server to connect to.

$auth['db_ext']['db_username'] = 'YYYYYY';    Here is the correct name and password given.
$auth['db_ext']['db_password'] = 'XXXXXX';

//The MySQL username and password to connect with.

$auth['db_ext']['db_name'] = 'db512669';

//The name of the database.

$auth['db_ext']['db_table'] = 'gc_user';

//The table that holds the authentication data.

$auth['db_ext']['column_name_username'] = 'username';
$auth['db_ext']['column_name_password'] = 'password';

//The names of the two columns that hold the authentication data.

$auth['db_ext']['password_format'] = 'md5';

//This is the format of the password entries in the table. You can specify
//'md5', 'sha1', 'sha256', 'crypt' or 'plaintext'.

$auth['db_ext']['column_name_level'] = 'level';
$auth['db_ext']['column_name_email'] = 'email';

//This configuration item is optional and contains the name of the column
//that hold the access level of the user.

Only admin can add or edit data. Other users are not accepted, but the are in the table called 'gc_user';

Do you have an idea, what is my mistake? Thanks for your help. Gero

Reported by: *anonymous

Original Ticket: mrbs/support-requests/1949

[jberanek]

Sorry, can you explain some more what the problem is please? Are you saying that you can only login to MRBS as user 'admin'?

Original comment by: campbell-m

[jberanek]

Is the problem that other users cannot login? Or is it that they can login but then are not able to make a booking?

Original comment by: campbell-m

[jberanek]

Your first version is correct. If another user tries to log in, MRBS says: unknown user But the user is in the database.

Original comment by: *anonymous

[jberanek]

Maybe the password is incorrect?

Original comment by: campbell-m

[jberanek]

than all passwords haave to be wrong. In the database are nearly 80 users.

Original comment by: *anonymous

[jberanek]

Which version of MRBS are you running?

Original comment by: campbell-m

[jberanek]

Looking at the code, I see that the usernames have to unique in a case insensitive manner. So if you have two users, JSmith and jsmith, the MRBS code will think there are two users called jsmith and return false, ie "Unknown user".

If that's not the problem, the only thing I can suggest is to put some debugging code into authValidateUser() in auth_db_ext.inc to find out whether the user is being found in the database and if so whether the password validates.

Original comment by: campbell-m

[jberanek]

Actually that wasn't quite correct. I see that the MRBS code does a case sensitive comparison of the username in the database with the lowercase version of the username input on the form. So if your usernames have any uppercase letters in them they will never be able to login!

This is obviously not right. You could take out the utf8_strtolower in line 64 (assuming MRBS 1.7.4.1) of auth/auth_db_ext.inc to fix this while I think to see whether there is a better fix (possibly making the comparison case insensitive).

Original comment by: campbell-m

[jberanek]

Thanks, that is the problem. I changed line 64, as you mentioned it. I can log in with another name. but now the problem is, that I cannot add an entry. I get the error message Zugang verweigert Sie haben keine Berechtigung, diesen Eintrag zu ändern Access Denied You are not authorized to change this entry If I write the user name in lowercase letters I cah add an entry. There must be more "utf8_strtolower" commands.

Original comment by: *anonymous

[jberanek]

OK, I'll test it thoroughly tomorrow. Which version of MRBS are you running?

Original comment by: campbell-m

[jberanek]

I found another "utf8_strtolower" in line 175. I changed it and now it works. Thanks for your help.

Original comment by: *anonymous

[jberanek]

I've now fixed this in the mrbs_17_branch (cae89e) and default (2945e0) branches.

Note that there was another line (220) that needed to be fixed in order to make email work, though I'm sure you'll have found that by now.

Original comment by: campbell-m

[jberanek]

I am using : MRBS 1.7.4.1

Original comment by: *anonymous

[jberanek]

Next problem "Umlaute", letters like äöüß in names, are not excepted. Do you block them?

Original comment by: *anonymous

[jberanek]

A name like "Müller" is not found. What does syntax_casesensitive_equals do. Where is that function?

Original comment by: *anonymous

[jberanek]

I've just tested this. It works OK if the collation of your external databaase is utf8, but not if it's latin1. Is your external database using a collation other than utf8?

syntax_casesensitive_equals() is in lib/MRBS/DB_mysql.php and does:

  public function syntax_casesensitive_equals($fieldname, $string, &$params)
  {
    $params[] = $string;

    return " BINARY " . $this->quote($fieldname) . "=?";
  }

I'll need to think about the best way to solve this.

Original comment by: campbell-m

[jberanek]

Changing that line to

   return " BINARY CONVERT(" . $this->quote($fieldname) . "using utf8)=?";

seems to fix it. I'll need to think about whether that's suitable as a general fix.

Original comment by: campbell-m

[jberanek]

Certainly wouldn't work in PostgreSQL...

Original comment by: jberanek

[jberanek]

The collation in the database is latin1_general_ci
Using return " BINARY CONVERT(" . $this->quote($fieldname) . "using utf8)=?"; it does not work.

If I change the database entry to utf8_german2_ci it works.

If I use return " BINARY CONVERT(" . $this->quote($fieldname) . "using latin1)=?"; with latin1_general_ci , it does not work. I'm not sure, that order exists.

Original comment by: *anonymous

[jberanek]

Could you try this line please with a latin1_general_ci collation in the database:

    return " " . $this->quote($fieldname) . "=CONVERT(? using utf8mb4) COLLATE utf8mb4_bin";

and see if it works?

See this post for an explanation.

Original comment by: campbell-m

[jberanek]

Fantastic it works. Thanks so much

Original comment by: *anonymous

[jberanek]

Good. I've now committed the change to the mrbs_17_branch (fd3993) and default (315521) branches.

Original comment by: campbell-m

[jberanek]

I think PostgreSQL is OK. The PostgreSQL version of syntax_casesensitive_equals() uses an '=' . I've tested against a Latin1 database and it seems to work OK.

Original comment by: campbell-m