SQL Members table (how do I update a users password to MD5)

DJMK4

DJMK4

DJMK4

Soldato
Joined
1 Dec 2004
Posts
23,052
Location
S.Wales
I have two questions really.

If I have a members SQL table which consists of:

ID
Username
Password

and the password field is normal, how to I either update a single record to an MD5 password through SQL or the entire table list to MD5 passwords?
 
create a script which does the following:

SELECT id, password FROM users

start loop

password = getpasswordDB

password = MD5(password)

UPDATE users SET password = password WHERE id = idDB

next

You will need to create the md5 function - what language u using?

i have examples in classic asp/vbscript and c-sharp
 
Iv created a PHP login script

(See below)

Code: 
<?php
$host='**'; // Host name
$username='**'; // Mysql username
$password='**'; // Mysql password
$db_name='**'; // Database name
$tbl_name='**'; // Table name

// Connect to server and select databse.
mysql_connect($host, $username, $password)or die('cannot connect');
mysql_select_db($db_name)or die('cannot select DB');

// username and password sent from form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$encrypted_password=md5($mypassword);

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

//$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
//$result=mysql_query($sql);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$encrypted_password'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "**.php"
session_register('myusername');
session_register('mypassword');
header('location:**');
}
else {
echo "Wrong Username or Password";
}
?>

However at present I am not planning on using a script to register users from the php page as the login script is for my use only, i currently have one user in the SQL table but the password is not encrypted, all I want to do is to use some SQL code in phpmyadmin to update either a single user or all users passwords to MD5 encypted passwords.

Hope this is enough info :)

EDIT: OK iv managed to get SQL table to encrypt the password, so now I have

id
username
password (showing as the encrypted password)

however when i try to log in it keeps saying "incorrect password" unless i enter the password in its encypted format.
 
Last edited:
this:
Code: 
$mypassword = mysql_real_escape_string($mypassword);
to this:
Code: 
$mypassword = mysql_real_escape_string($encrypted_password);
and to be a pedant, it's not encrypted, it's hashed. You should also not be using stripslashes without checking that magic_quotes is enabled.
 
Dj_Jestar said:
this:
Code: 
$mypassword = mysql_real_escape_string($mypassword);
to this:
Code: 
$mypassword = mysql_real_escape_string($encrypted_password);
and to be a pedant, it's not encrypted, it's hashed. You should also not be using stripslashes without checking that magic_quotes is enabled.
Click to expand...


Thanks for this, although this didnt work, it still redirects me to a page saying username or password incorrect.


Code: 
// username and password sent from form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$encrypted_password=md5($mypassword);

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($encrypted_password);

//$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
//$result=mysql_query($sql);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$encrypted_password'";
$result=mysql_query($sql);

SQL Table fields
userid:1
username: darren
password:
 
suarve said:
As far as I know session_register isn't the preferred way to register a session - see http://us2.php.net/manual/en/function.session-register.php

Also, have you considered salting - http://php.robm.me.uk/#toc-TakingitfurtherSalting
At the minute someone could still run your hashed passwords through some sort of dictionary tool as they know you've used md5. As the guide says, the small extra effort required to salt your passwords is well worth it.
Click to expand...


Thanks for this, ill give this a read :)

Im aware that there are still flaws in my script although I wanted to get MD5 working first, then I was going to go through and improve the security of my script.
 
Last edited:
Would anybody give me some tips on salting out my MD5 password?

Do I need to add any extra fields in my SQL table (at the moment I have id, username, password (MD5 hashed), would I need to enter another field called "salt" and have some random value in there - im assuming no seeing as the salt is stored in this checklogin.php file?

Im assuming the salting is all done in my checklogin.php file

something like this
Code: 
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$salt= "abcdef";
$encrypted_password = md5($salt.$mypassword);

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$salt = stripslashes ($salt);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($encrypted_password);
$salt = mysql_real_escape_string($salt);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$encrypted_password'";
$result=mysql_query($sql);

Tell me if im doing this completely wrong :p (Please bear in mind I do not have a registration form so the user details are manually added into my SQL database table).

Thanks
 
Last edited:
There's a bit much going on here. There's no need for the $encrypted_password variable as you can just do

... and password=md5('".$salt.$mypassword".');.

Also, you'd be a lot better off using sha() rather than md5(). md5 is fairly easy to bruteforce even without a dictionary.

And I don't think that you're salting very efficiently either, someone only needs to bruteforce two passwords to find the salt...
 
andrewdodd13 said:
There's a bit much going on here. There's no need for the $encrypted_password variable as you can just do

... and password=md5('".$salt.$mypassword".');.

Also, you'd be a lot better off using sha() rather than md5(). md5 is fairly easy to bruteforce even without a dictionary.

And I don't think that you're salting very efficiently either, someone only needs to bruteforce two passwords to find the salt...
Click to expand...

OK Thanks very much for this :)

So how are you suggesting I would get around the efficianty issue? double salted sha()? if that even will work?

Thanks bud
 
I usually tend to generate a random hash (per application, using whichever algorithm) and XOR it with the hash of the users password. Combining this with the salting which you're doing to prevent word-password attacks is fairly secure.

A further way (and I'm not that sure how secure this is) to use MySQL's AES function. AES is an asymmetrical encryption algorithm, so you can't simply generate a massive dictionary list. What you then do is use the password as the key, and the salted password as the plaintext. This method will become slightly less secure if your salt is compromised - it becomes easier to bruteforce AES if you know what the plaintext is (in this case it's the key you're trying + salt).

Also, now that you've encrypted all your data in MD5 you'll have to reset everyone's passwords...

Code: 
$myusername = mysql_real_escape_string(stripslashes($_POST['myusername']));
$mypassword = mysql_real_escape_string(stripslashes($_POST['mypassword']));

$salt= "abcdef";

// Waste of cycles, you know the salt is safe (of course if you change the salt, escape it manually)
// $salt = stripslashes ($salt);
//$salt = mysql_real_escape_string($salt);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and aes_decrypt(password, '$mypassword') = '$salt.$mypassword')

$result=mysql_query($sql);

Major note I thought of while writing that, you'll need to use:
password = aes_encrypt('$salt.$password', '$password')
in your insert / update line.
 
You must log in or register to reply here.
Back
Top Bottom