Using BCrypt hash algorithm in php and SQL Server

[kelechi]

Greetings mates,

 

I found this awesome article on hashing BCrypt algorithm in SQL Server and using it with php.

 

http://blog.tcs.de/using-the-bcript-hash-algorithm-in-ms-sql-server/

 

I have completed the install and setup in sql server management studio.

 

I am a bit confused as to how to apply it in both my register.php user and login.php pages.

Can someone please give me?

 

Sigh!!

The post is not allowing me to copy and paste and it is not allowing me to attach the file as .txt or .sql or .rar.

 

Basically, I am trying to do an insert statement to insert user's username, password and type. Type means whether admin or regular user.

 

Then I am doing a basic select username, password from mytable where username='varuser' and password='varpassw'

 

I will read up on how to copy and paste and what is allowed as attachment.

 

Thank you

 

 

[Jacques1]

Letting the database handle the bcrypt hashing is a poor approach, because it's complex, inflexible and potentially insecure:

• Passing the plaintext password to the database system increases the risk of leaking it and forces you to put extra effort into database security. For example, database systems typically log all queries, so you may find the precious passwords piling up in some log file. If the database is on a remote server, the passwords may also be intercepted in transit in case the traffic isn't properly encrypted.
• Should you ever decide to give up MS-SQL in favor of a different database system (which isn't too unlikely), you have to go through all the trouble again and find a database-specific bcrypt implementation.

Instead, do the hashing in the application. If you have at least PHP 5.5, there's a native password hashing extension which currently uses bcrypt as the default algorithm:

 

http://php.net/manual/en/function.password-hash.php

 

If you have at least PHP 5.3.7, there's an external library which emulates the above API:

 

https://github.com/ircmaxell/password_compat

[kelechi]

$hash = password_hash($password, PASSWORD_BCRYPT);

$strSQL = "INSERT INTO member (Username,Password,Name,Status) VALUES ('".$_POST["txtUsername"]."',
'".$_POST["txtPassword"]."','".$_POST["txtName"]."','".$_POST["ddlStatus"]."')";
$objQuery = sqlsrv_query($strSQL);

$strSQL = "SELECT * FROM users WHERE username = '".ms_escape_string($_POST['txtusername'])."'
and password = '".ms_escape_string($_POST['txtpassword'])."' ";

Thanks very much. I really appreciate that advise.

So, in the event that I am creating a user and saving the username and password in the db, how would I integrate the code from github to my register and login samples above?

 

Sorry, it is not quite clear to me.

[Jacques1]

Your code above is wide open to SQL injection attacks, so this needs to be fixed first. Stuffing variables into query strings should be avoided altogether, because it's way too easy to make a mistake (as you can see).

 

Instead, use a modern database interface which allows you to safely pass parameters to the database system via prepared statements. I'm no MS-SQL expert, but SQLSRV looks like the right approach. See the sqlsrv_prepare() function for examples how to use prepared statements.

 

As to bcrypt:

 

I assume you've already set up your database table? A bcrypt hash is exactly 60 bytes long, so you'll want something like CHAR(60) to store the hash.

 

The API is pretty straightforward:

• password_hash() creates a new hash from a plaintext password. This is what you use in the registration script right so that you can store the hash in the database.
• password_verify() checks a plaintext password against an existing hash. This is what you use in the log-in script to see if the user has entered the correct password.

One special feature of bcrypt is that it's an adaptive hash algorithm, which means you can adjust its strength according to your current hardware and security needs. This is what you should do first. The strength is controlled through a cost parameter:

$hash = password_hash('test', PASSWORD_BCRYPT, array('cost' => 10));

Write a test script with the above line and increase or decrease the parameter until the hash calculation takes roughly one second. Then put this value into a configuration file so that you can change it later:

// hash parameters for bcrypt
define('PASSWORD_HASH_COST', 10);     // just an example; you may have a different value
define('PASSWORD_MAX_LENGTH', 56);    // bcrypt has a maximum input length of 56 bytes

In the registration script, you simply call password_hash() with the above parameters:

<?php

/**
 * Registration
 */

// ... check input, connect to database etc.

// check password length
if (strlen($_POST['password']) <= PASSWORD_MAX_LENGTH)
{
    $password_hash = password_hash($_POST['password'], PASSWORD_BCRYPT, array('cost' => PASSWORD_HASH_COST));
    
    // insert user data including $password_hash into the database
}
else
{
    // password too long, display error
}

In the log-in script, you fetch the hash for the given username and then check it with password_verify(). If the password is correct, you'll want to update the hash in case the cost parameter has changed:

<?php

/**
 * Log-in
 */

// ... check input, connect to database etc.

// load password hash from database, put it into $stored_password_hash

// check password
if (strlen($_POST['password']) <= PASSWORD_MAX_LENGTH && password_verify($_POST['password'], $stored_password_hash))
{
    // the password is correct; check if the hash needs to be updated
    if (password_needs_rehash($stored_password_hash, PASSWORD_BCRYPT, array('cost' => PASSWORD_HASH_COST)))
    {
        // update the hash in the database
    }

    // the user is now authenticated; store the user ID in the session etc.
}
else
{
    // password incorrect, display error
}

Edited March 13, 2015 by Jacques1

[kelechi]

Thank you so much sir for your help