Reset password in database

[rpc-scandinavia]

Does anybody have the SQL to reset a user password in the Kopano database, including information about how to hash the password ?

This information make it possible to write your own password reset program.

According to this thread, the hash is MD5:

[rpc-scandinavia]

I found the table.

The users are saved in the “objectproperty” table.
Example of finding a user, by e-mail:

use "Kopano";
select * from objectproperty where propname = "emailaddress" and value = "EMAIL@ADDRESS";
select * from objectproperty where propname = "password" and objectid = 12;

Example of changing the password, with a new hashed password:

update objectproperty
set value = "247a46027b07580993c84382d6a9f9ef34da20a9"
where propname = "password" and objectid = 12;

The hashed password in the database are like this:

[salt, 8 bytes][MD5 hashed password, 32 bytes]

This is my example C# class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
namespace RpcScandinavia.Test;

#region RpcKopano
//----------------------------------------------------------------------------------------------------------------------
// RpcKopano.
//----------------------------------------------------------------------------------------------------------------------
/// <summary>
/// Utility class for the Kopano mail system.
/// </summary>
class RpcKopano {

	#region User password methods
	//------------------------------------------------------------------------------------------------------------------
	// User password methods.
	//------------------------------------------------------------------------------------------------------------------
	/// <summary>
	/// Validates the password with the hashed password.
	/// The hashed password is read from the Kopano database.
	/// </summary>
	/// <param name="password">The clear text password.</param>
	/// <param name="hashedPassword">The hashed password.</param>
	/// <returns>True when the passwords match, false when they do not match.</returns>
	public Boolean ValidateHashedPassword(String password, String hashedPassword) {
		// Get the salt from the hashed password.
		Byte[] salt = this.GetSaltFromHashedPassword(hashedPassword);

		// Hash the password.
		String compareHashedPassword = this.GetHashedPassword(salt, password);

		// Return true if the two hashed passwords match.
		return compareHashedPassword == hashedPassword;
	} // ValidateHashedPassword

	/// <summary>
	/// Hashes the password, using a random salt.
	/// The hashed password can be saved in the Kopano database.
	/// </summary>
	/// <param name="password">The clear text password.</param>
	/// <returns>The hashed password.</returns>
	public String GetHashedPassword(String password) {
		Byte[] salt = this.GetSalt();
		String hashedPassword = this.GetHashedPassword(salt, password);
		return hashedPassword;
	} // GetHashedPassword

	/// <summary>
	/// Hashes the password, using the salt.
	/// The hashed password can be saved in the Kopano database.
	/// </summary>
	/// <param name="salt">The salt, must be 8 bytes long.</param>
	/// <param name="password">The clear text password.</param>
	/// <returns>The hashed password.</returns>
	public String GetHashedPassword(Byte[] salt, String password) {
		// Validate.
		if (salt == null) {
			throw new ArgumentNullException(nameof(salt));
		}
		if (salt.Length != 8) {
			throw new ArgumentException($"The salt should be 8 bytes long.");
		}
		if (password == null) {
			throw new ArgumentNullException(nameof(salt));
		}
		if (String.IsNullOrWhiteSpace(password) == true) {
			throw new ArgumentException($"The password is empty.");
		}

		// Combine salt and password bytes.
		Byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
		Byte[] saltAndPassword = new Byte[salt.Length + passwordBytes.Length];
		salt.CopyTo(saltAndPassword, 0);
		passwordBytes.CopyTo(saltAndPassword, salt.Length);

		// Calculate the MD5 hash.
		Byte[] hashedPasswordBytes = HashAlgorithm.Create("MD5").ComputeHash(saltAndPassword);

		// Convert the salt and hash to strings.
		String saltString = Encoding.UTF8.GetString(salt)
			.ToLower();
		String hashedPassword = BitConverter.ToString(hashedPasswordBytes)
			.Replace("-", "")
			.ToLower();

		// Return the MD5 hash as a string, with the salt prefixed.
		return saltString + hashedPassword;
	} // GetHashedPassword

	/// <summary>
	/// Gets the salt from the hashed password.
	/// The hashed password is read from the Kopano database.
	/// The salt is the first 8 bytes, of the hashed password.
	/// </summary>
	/// <param name="hashedPassword">The hashed password.</param>
	/// <returns>The salt.</returns>
	public Byte[] GetSaltFromHashedPassword(String hashedPassword) {
		// Validate.
		if (hashedPassword == null) {
			throw new ArgumentNullException(nameof(hashedPassword));
		}
		if (hashedPassword.Length != 40) {
			throw new ArgumentException($"The hashed password should be 40 characters long.");
		}

		// Get the first eight bytes, which is the salt.
		Byte[] saltBytes = Encoding.UTF8.GetBytes(hashedPassword.Substring(0, 8));

		// Return the salt.
		return saltBytes;
	} // GetSaltFromHashedPassword

	/// <summary>
	/// Gets a new random salt, 8 bytes long.
	/// </summary>
	/// <returns>The salt.</returns>
	public Byte[] GetSalt() {
		// Generate random 4 bytes.
		Random random = new Random();
		Byte[] salt = new Byte[4];
		random.NextBytes(salt);

		// Convert into HEX string.
		String saltString = BitConverter.ToString(salt)
			.Replace("-", "")
			.ToLower();

		// Return the HEX string as a byte array.
		// This makes the original 4 bytes 8 bytes long.
		return Encoding.UTF8.GetBytes(saltString);
	} // GetSalt
	#endregion

} // RpcKopano
#endregion