NZEYIMANA Emery Fabrice

After googling around for a plug-in to help me authenticate PHPBB3 forums against an already existing web application and finding none, I decided to write my own. I modelled it to auth_db and auth_apache and named it auth_dbext as short for authentication using DB from external source (external to PHPBB Database).

I am sharing it with others who might have a similar need and of course any improvements are welcome.

I have not implement some optional parts (see http://wiki.phpbb.com/Authentication_plugins for more info)

The login code is in the function (The full source code is at auth_dbext.phps)

[code lang="php"]
/**
* Login function
*/
function login_dbext(&$username, &$password)
{
global $db;

// do not allow empty password
if (!$password)
{
return array(
'status' => LOGIN_ERROR_PASSWORD,
'error_msg' => 'NO_PASSWORD_SUPPLIED',
'user_row' => array('user_id' => ANONYMOUS),
);
}

if (!$username)
{
return array(
'status' => LOGIN_ERROR_USERNAME,
'error_msg' => 'LOGIN_ERROR_USERNAME',
'user_row' => array('user_id' => ANONYMOUS),
);
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Note: on my systems, I include these following lines from an external file that is not web-accessible
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
$db_host = "localhost"; // Here goes the MySQL server address, hostname or IP
$db_user = "username"; // Here goes the MySQL user allowed to read the table below (GRANT SELECT ON ....)
$db_password = "passwd"; // Here should go the password associated with the above user
$db_database = "dbName"; // Here goes the Database containing the table below
$db_table = "tblUsers"; // Here will goes the table list users allowed to login into PHPBB
////////////////////////////////////////////////////////////////////////////////////////////////////////////
$col_username = "username";
$col_password = "password";
$hashMethod = "sha1"; // Can be one of: md5, sha1, plain
// In case you choose to use a non-standard hashing function, be
// sure to change below where the $hashedPassword variable is created

$objMySqli = new mysqli($db_host, $db_user, $db_password, $db_database);

/* check connection */
if (mysqli_connect_errno())
{
return array(
'status' => LOGIN_ERROR_EXTERNAL_AUTH ,
'error_msg' => 'LOGIN_ERROR_EXTERNAL_AUTH ',
'user_row' => array('user_id' => ANONYMOUS),
);
}

// Check the User/Password
if($hashMethod == 'sha1')
{
$hashedPassword = sha1($password);
} elseif($hashMethod == 'md5') {
$hashedPassword = md5($password);
} else {
$hashedPassword = $password;
}
$sql =
"SELECT 11 as ID
FROM " . $db_table . "
WHERE
" . $col_username . " = '" . mysqli_real_escape_string($username) . "' AND
" . $col_password . " = '" . mysqli_real_escape_string($hashedPassword) . "'
";

if ( $result = $objMySqli->query($sql) )
{
if ( $result->num_rows <= 0 )
{
return array(
'status' => LOGIN_ERROR_USERNAME,
'error_msg' => 'LOGIN_ERROR_USERNAME',
'user_row' => array('user_id' => ANONYMOUS),
);
}

$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type
FROM ' . USERS_TABLE . "
WHERE username = '" . $db->sql_escape($username) . "'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);

if ($row)
{
// User inactive...
if ($row['user_type'] == USER_INACTIVE || $row['user_type'] == USER_IGNORE)
{
return array(
'status' => LOGIN_ERROR_ACTIVE,
'error_msg' => 'ACTIVE_ERROR',
'user_row' => $row,
);
}

// Successful login...
return array(
'status' => LOGIN_SUCCESS,
'error_msg' => false,
'user_row' => $row,
);
}

// this is the user's first login so create an empty profile
return array(
'status' => LOGIN_SUCCESS_CREATE_PROFILE,
'error_msg' => false,
'user_row' => user_row_dbext($username, sha1($password)),
);
} else {
// TODO: Handle this situation
}

// Not logged in using the external DB
return array(
'status' => LOGIN_ERROR_EXTERNAL_AUTH,
'error_msg' => 'LOGIN_ERROR_EXTERNAL_AUTH',
'user_row' => array('user_id' => ANONYMOUS),
);
}
[/code]

To use this plugin, copy it to the directory /includes/auth/ (the file should be /includes/auth/auth_dbext.php ) in your PHPBB3 install location.  This file can be downloaded at auth_dbext.php (ZIP) or view a highlighted source file at auth_dbext.phps

With my need to exchange data between a ASP.NET and PHP web applications, I decided to use JSON. The .NET team did a good job by integrating JSON de/serialization into the language (NET 3.5) but they decided not to follow JSON specifications for some good reasons. Serializing an object that has a DateTime property will insert a string that won’t be understood by json_decode of PHP.

On the site of JSON, there is no such thing as a date type.  I have taken the ISO 8601 path which is my preferred date format (MySQL and Swedish Locale standard)

Consider the following class:

[code lang="C#"]
[DataContract]
public class Person
{
[DataMember]
public DateTime DateOfBirth { set; get; }
[DataMember]
public string Names { set; get; }
}
[/code]

By Serializing it you will get something like

[code]

{"DateOfBirth" : "\/Date(1210408872000+0200)\/", "Names" : "Kavuna ka Lyaziga"}

[/code]

That Date is not defined as a JSON type. In case your JSON will be used directly by JavaScript or .NET (C#, VB) you will not need to write extra codes.

For formatting dates in MySQL one can use the DATE_FORMAT function like DATE_FORMAT(`date_col or value`, dateFormatString)

In a select query, that looks like SELECT DATE_FORMAT(`date_col or value`, dateFormatString) FROM `tableName`
. In case one wants to use the server current date, the query would look like SELECT DATE_FORMAT(NOW(), dateFormatString) FROM `tableName`

For a detailed explanation of the options that can be in a dateFormatString, read the MySQL date related page (dev.mysql.com/doc/).

In the general examples below, I will use the date 22 April 2007 16:15:23 (date of creation of this article)

For PHP formatString, visit http://www.php.net/date