Integrating SMF with old CSV style user database

[zeridon]

Hello,

We've got the following problem:

* There is a userdb which is CSV based
* This DB is used for authentification on certain portions of our site
* Part of the users still register trough the old system (legacy code don't ask)
* we are migrating to SMF

The question ... how to make the integration between the old DB and SMF userDB

Our current progress:

* add a new function/code block in Sources/LogInOut.php which will check if the logging user exists in SMF if not try to find it in the CSV DB and add it to the SMF DB.
* This is already done but has some quirks:
* * On first login try nothing happens the user is redirected to the home page
* * on the second try it works flawlesly

here is the piece of code we added (later will be migrated to mod)

function Login2()
{
       global $txt, $scripturl, $user_info, $user_settings, $smcFunc;
       global $cookiename, $maintenance, $modSettings, $context, $sc, $sourcedir;

//integration start
       $SITE_user = isset($_REQUEST['user']) ? preg_replace('~&#\\d{1,7}|x[0-9a-fA-F]{1,6});~', '&#\\1;', htmlspecialchars($_REQUEST['user'])) : '';
       if ($SITE_user){
               $request = $smcFunc['db_query']('','
                       SELECT id_member, member_name
                       FROM {db_prefix}members
                       WHERE member_name = {string:SITE_user}
                       LIMIT 1',
                       array (
                               'SITE_user' => $smcFunc['db_case_sensitive'] ? strtolower($_REQUEST['user']) : $_REQUEST['user'],
                       )
               );

               if ($smcFunc['db_num_rows']($request) == 0){
                       $smcFunc['db_free_result']($request);
                       // nope ... it is not in the db ... find it and stuff the data
                       $SITE_USER_DB = '/tmp/temp.tbl';
                       $fh = @fopen($SITE_USER_DB, 'r');
                       if ($fh) {
                               while (!feof($fh)) {
                                       $line = fgets($fh, 4096);
                                       $fieldset = explode('|', $line);
                                       if (!$fieldset) break;
                                       if ($fieldset[1] == $SITE_user) {
                                               $smcFunc['db_query']('','
                                                       INSERT IGNORE INTO {db_prefix}members
                                                               (member_name, real_name, passwd, email_address, hide_email, password_salt)
                                                       VALUES
                                                               ({string:username}, {string:realname}, {string:password}, {string:email}, {int:hide}, {string:salt})',
                                                       array(
                                                               'username' => $fieldset[1],
                                                               'realname' => $fieldset[1],
                                                               'password' => $fieldset[2],
                                                               'email' => $fieldset[4],
                                                               'hide' => 1,
                                                               'salt' => ''
                                                       )
                                               );
                                               $smcFunc['db_free_result']($request);
                                       break;
                                       }
                               }
                               fclose($fh);
                       }
               }
       }
// integration end

From this point on it's the normal flow of the Login2 function.

I need also the following advices/comments

* Are the queries properly writen/sanitized (i was not able to find any good reference how to write them
* Are they properly created etc.
* is the second db_free_result needed (sometimes it throws warnings)
* is this the proper place to hook our code
* is there an easier way to create user in SMF (this looks a bit ugly though it works)

[SleePy]

On the first and second login. Which login boxes are you using?
I wonder if this has to do with it.

btw, is the password clear text? If it is, at that point when you insert it into the database you can just do the work SMF would do to update it to use its sha1 hashing method.

[zeridon]

On the first and second login. Which login boxes are you using?
I wonder if this has to do with it.

I don't understand the question ... i am going trough the normal login way ... click on login (or use the embedded quicklogin boxes)

The pass is cleartext and by inserting it with an empty salt on the first login SMF updates it propperly.

[zeridon]

Partial improvement is to use db_insert instead db_query for the member insertion but still ... the normal flow does not work. The user gets redirected to the boardindex (with session set) but is not logged on

[SleePy]

Is caching enabled?

As for the login boxes, I meant this just to know which login page we where using.

As for the password. If you look at how the salt is created and password is matched, then you could easily replicate this on the insert. Which means SMF won't need to figure it out, and the top login box would work.

[zeridon]

No caching enabled.

The problem is pinned down to the fact that the $user_info array is not correctly filled (and probably part of the session is missing)

Addition of the user directly in the db works (salt etc is not a problem)

[SleePy]

After the intergration code. Do this:

Code Select Expand

$_REQUEST['user'] = $SITE_user;

I wonder if it is just a mismatch that is occurring for some reason here. Since that variable is matched up during login.

I realized I missed some questions.
* Are the queries properly writen/sanitized (i was not able to find any good reference how to write them
SMF does its best to clean queries. Although you should do your own work such as making sure what is being received as input is actually being used.

* Are they properly created etc.
There is other methods, but this works.

* is the second db_free_result needed (sometimes it throws warnings)
After a INSERT you do not need to free the result. The result freeing is only needed after a select where you have a resource id that is being kept for you to get the results.

* is this the proper place to hook our code
This would be the best place as no other location do you have the user login details yet.

Code Select Expand

* is there an easier way to create user in SMF (this looks a bit ugly though it works)
There is a registerMember function built into SMF. http://support.simplemachines.org/function_db/index.php?action=view_function;id=594

[zeridon]

Hi Again

The problem got solved but for that a bit later

SleePy ... the code $_REQUEST['user'] = $SITE_user; does nothing ... in fact it aggravates the problem.

Aproach with registerMember() also failed miserably (some authentification gimmicks i suppose) and was abandoned.

We are sticking to the first proposed variant but encoding the password and generating the salt as suggested. I suppose this was the problem (unclear why).

Problem is solved

[SleePy]

I am not quite sure why either. But glad you got it working