PHP authentication with mysql encrypt function

Discussion in 'Programming/Scripts' started by igongora, Nov 28, 2007.

  1. igongora

    igongora New Member


    I have a mysql table where I store passwords using mysql encrypt function, and I would like to be able to create a login script where I can type in login and password and then it will authenticate my user. However, whenever I try to encrypt the password I get a new character string, how can I acheive my goal without changing encrypt by md5 or other similar.

  2. Mosquito

    Mosquito New Member

    Assuming you have only a Text Field for User name and password - named 'username' and 'password',

    To verify login:
    $query = "SELECT username, password, other_fields FROM users_table WHERE username = $_REQUEST['username'] AND password = encrypt($_REQUEST['password'])";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error()); 
    if (mysql_num_rows($result) == 1)
    	//successful login
    	// not successful
    I just wrote this code up now, so I haven't tested it. However, it should work with few modifications. What this script does it make sure only 1 row is returned (matching username and password...hopefully those are unique on your database). The query itself, will use the MySQL encrypt() function to compare the input password to what is in the database.

    If you have PHP/MySQL questions, take a look at
    Last edited: Nov 29, 2007
  3. igongora

    igongora New Member

    Thanks moskito,

    but it did not work, th eporblem I have is that encrypt retunrs each time an encrypted string differetne form the previous so the passwords do not match.

    for instance:
    SELECT encrypt('abc') as string FROM `users` LIMIT 1
    SELECT encrypt('abc') as string FROM `users` LIMIT 1

    The passwords never match..
  4. Mosquito

    Mosquito New Member

    How are you inserting these into the database? Are you using a salt? Or did you use the MySQL PASSWORD() function on the insert? If you did the latter, change the ecrypt() calls in my above code to password().
  5. bvidinli

    bvidinli New Member

    goto mysql console, type:
    select encrypt('aaa','aa');

    i think you will be able to get same string with encrypt('string','salt')
    this way, you will be able to store and match passwords in mysql database..
    hope this helps,
    let me know if this worked..
  6. new

    new New Member

    encrypt function in registration form

    Moskito, verify login works, but I Wonder how ..?

    I did follow Falko's tutorial Here... and everything works perfect, the only thing that I dont get.. is how to:

    Make register form that match the "encrypt".

    I have posted a question in ubuntu forums ( here )... But dont get any result.

    If I type (in my server terminal):
    mysql -u root -p
    ####  password here
    USE mail;
    INSERT INTO `users` (`email`, `password`, `quota`) VALUES ('[email protected]'ENCRYPT('PassGoHere'), 10485760);
    works perfect, but how to insert these values (from a PHP) registration from???

    I have not idea... how to make my registration form >>> to insert "ENCRYPT" password, like I do in my server terminal.

    Last edited: Aug 13, 2011
  7. falko

    falko Super Moderator ISPConfig Developer

  8. new

    new New Member

    encrypt function in registration form

    Thanks Falko.

    There is a "SELECT" function from DB table.. with which I have no problem.

    The deal is in my PHP registration FORM, whre I have to "INSERT" ENCRYPT password.

    Eg. register.php
    <form id="register" name="register" method="post" action="exec.php" class="new.user">


    input name="uname" type="text" class="textfield" id="uname" />
    input type="hidden" name="site" value=""  id="site"  maxlength="128" />
    br />

    input name="password" type="password" class="textfield" id="password" />

    label>Confirm Password</label>
    input name="cpassword" type="password" class="textfield" id="cpassword" />



    $Umail $_POST['uname'].'/'.$_POST['site'];

    $qry "INSERT INTO users(email, password, ..., ...) VALUES('$Umail','" xxxxxxxxxxxxxxx ($_POST['password'])."','...', '...')";
    $result = @mysql_query($qry);

    Kalko... the whole process works perfect, but where the xxxxxxxxxx are .. is the part where I do not know what to put, in order to make "INSERT" the password by ENCRYPT.

    Remenber that from your tutorial, we do INSERT these values from MYSQL terminal using:
    INSERT INTO users(emailpassword, ..., ...) VALUES('$Umail','" ENCRYPT($_POST['password_here'])."','...''...')";
    But I need to let users do it from my php registration form.

    Last edited: Aug 15, 2011
  9. Mark_NL

    Mark_NL New Member

    if you don't supply a salt with mysql encrypt, mysql will use a random one, every time you call the function, that's why "SELECT encrypt('abc') as string FROM `users` LIMIT 1" returns something else every time you run it.

    Just let php encrypt the entered password (with f.e. crypt() as Falko pointed out) and supply a ready-to-insert password to mysql. I think in this case it's better to try and use that method first.
  10. new

    new New Member

    @Mark_NL ... I do not follow you :confused:

    How do I use "INSERT" ENCRYPT password .... in my php registration (exec.php)???

    Since Falko's tutorial teach how to do it in "MySQL Terminal" adding by direct way a ENCRYPT function, but... I really don't know how to make "apply" that funtion in order to get it work..

    e.g >>> if in exec.php I use:
    INSERT INTO users(emailpassword, ..., ...) VALUES('$Umail','" CRYPT($_POST['password'])."','...''...')"
    It works.. but crypt() funtions insert password ok, but don't let (IMAP) read back the pass as "encrypted", neither pure-ftp...

    Don't know if I explain this issue the corrected way, but I just ask "please", to someone let me know: how to insert users password From a web based php, into my users DB table, as I do From MySQL terminal (like Falko's tutorial said)... how to Insert from my web based registration (exec.php) :

    INSERT INTO users(email, password, ..., ...) VALUES('$Umail', '"what_do_I_place_here_to_match_encrypt_funtion_like_tutorial_said ($_POST['password'])."','...', '...')";

    Sorry my ignorance, and thanks a lot.



    Little back ground to clear up my comments:

    I did follow Falko's tutorial "virtual-users-and-domains-with-postfix-courier-mysql-and-squirrelmail-ubuntu-10.10".. but about "page 4", in chapter 13 >>> "Populate The Database And Test", using "... MySQL shell", wich I have done that part with not problem at all.

    So, how do I use (ENCRYPT) value on a registration_form.php (register.php) that match what I do in MySQL shell..?

    I collect user user name, and password info from register.php >>> ... and procces/insert that info through a php script named exec.php

    How do I use "INSERT" ENCRYPT password .... in (exec.php) ???... where:

    INSERT INTO users(..., password) VALUES('...','" CRYPT($_POST['password']))" <<< (no work, squirrelmail don't recognize the encrypted pass)
    INSERT INTO users(..., password) VALUES('...','" ENCRYPT($_POST['password']))" <<< (no work, "php error said that ENCRYPT" function is not recognize as a valid)

    Last edited: Aug 15, 2011
  11. falko

    falko Super Moderator ISPConfig Developer


    INSERT INTO users(..., password) VALUES('...', ENCRYPT(".$_POST['password']."));
  12. Mark_NL

    Mark_NL New Member

    Falko, with that query, MySQL will use a different salt every time you call it, so the same entered password, it will be saved differently in the db, and you can never match against it, since you don't know the salt mysql used during

    INSERT INTO users(name, pass) VALUES('john', ENCRYPT('password','(*#Ng383'));
    This will save the same encrypted string over and over.

    for his solution he should use:
    INSERT INTO users(..., password) VALUES('...', ENCRYPT(".$_POST['password'].", 'odufmsircklsc'));
    ("odufmsircklsc" being the salt)
  13. new

    new New Member

    @Falko and @Mark_NL .... thanks guys...
    You are the man.. works perfect .. I do have not words to thanks you...that was exactly what I was looking for.... now I can authenticate users from the same database for mail and ftp, with the same ENCRYPT password..

    Just curious .. ... what character users are allow to use in password field of my registration from... because ... ("odufmsircklsc" being the salt) ... or don't matter ???

    Regards.. you all ..!!
    Really thanks...
    Last edited: Aug 16, 2011
  14. beaney

    beaney New Member

    I need help correcting my query string

    Im having trouble getting my login to work on my site.
    it keeps telling me query failed, but when i echo out the username and password it matches the one on the server so i dont understand why its not working.
    Im a new programmer by the way very noob.

    $db mysqli_connect($server$user$password$database);
    $salt "45Gxkj9583lPMdxoekfg"
    $user $_POST['username'];
    $pass crypt($_POST['password'], $salt);
    "<h2>welcome you are now logged in ".$user." ".$pass."</h2><br />\n";
    $sql "SELECT username, password FROM faculty WHERE username = $user AND password = $pass";
    $result mysqli_query($sql,$db) or die('Query failed: ' mysql_error($db));
            if (
    mysql_num_rows($result) == 1)
    //successful login
    echo "<h2>Welcome  You are now logged in.</h2>\n";
    // not successful
    $page file_get_contents("err_login.html");
                //$_SESSION['valid'] = 1;
                //$_SESSION['user'] = $username;
  15. beaney

    beaney New Member

  16. Mark_NL

    Mark_NL New Member

    $sql "SELECT username, password FROM faculty WHERE username = '$user' AND password = '$pass'";
    notice the single quotes i put around $user and $pass
  17. beaney

    beaney New Member

    no that doesnt work.

Share This Page