MySQL password() function to PHP

Question

I tried researching this but still have no answer for it. A program my friend designed writes to the MySQL db passwords using the MySQL password() function.

I am looking for a way to use this through the web front I designed but still have no luck. Does anyone have any suggestions?

The passwords look just like this example

mysql> SET old_passwords = 0;
mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

I just need to figure out how to turn this into a function i.e function password_hash

Here's the rest the login query for an example

if (isset($_POST["username"], $_POST["password"], $_POST[$CONF["LOGIN_SIGNAL_TRIGGER"]])) {

    /*
        If we got a login signal, a password and a username, we will
        proceed to check login information. We will first extract
        the user row from the db.
    */
    $user = myF(myQ("
        SELECT `username`,`password`,`id`,`disable_until`,`active`
        FROM `[x]users` 
        WHERE LCASE(`username`)='".strtolower($_POST["username"])."'
    "));

    if (!$user["id"]) $GLOBALS["LOGIN_FAIL_TYPE"] = "e.user";
    elseif ($user["active"] != 1 && $CONF["LOGIN_REQUIRE_ACTIVE"]) $GLOBALS["LOGIN_FAIL_TYPE"] = "e.active";

    else {
        /*
            If the user's account 'disabled' value is greater than 
            the actual date value, and that the bruteforce protection
            system is enabled, we will show an error message
        */
        if (($user["disable_until"] > date("U")) && ($CONF["LOGIN_BRUTEFORCE_PROTECT:ENABLE"])) {
             $GLOBALS["LOGIN_FAIL_TYPE"] = "e.bruteforce";
            (isset($_SESSION["loginFailCount"])?session_unregister('loginFailCount'):false);
        }

        /*
            Account is not disabled
        */
        else {
            if ((isset($_SESSION["loginFailCount"])) && ($_SESSION["loginFailCount"] > $CONF["LOGIN_BRUTEFORCE_FAILCOUNT"])) {

                myQ("UPDATE `[x]users`
                    SET `disable_until` = ".(date("U")+$CONF["LOGIN_BRUTEFORCE_DISABLE_DURATION"])."
                    WHERE LCASE(`username`)='".strtolower($_POST["username"])."'
                    LIMIT 1"
                );

                (isset($_SESSION["loginFailCount"])?session_unregister('loginFailCount'):false);
                $GLOBALS["LOGIN_FAIL_TYPE"] = "e.bruteforce";
            }

            else {

                /*
                    All the information correct, we will proceed to login
                */
                if ($user["password"] == md5(trim($_POST["password"]))) {
                    $_SESSION["id"] = (integer)$user["id"];

                    session_write_close();

                    /*
                        Update the last login key
                    */
                    $me_last_login = me("last_login");
                    myQ("UPDATE `[x]users` SET `last_login`='".date("U")."' WHERE `id`='".me('id')."'");

                    /*
                        Route the user
                    */
                    if (!$GLOBALS["WAP_MODE"]) {
                        header("Location: ".(!$me_last_login?$CONF["LOGIN_FIRST_ROUTE_TO"]:$CONF["LOGIN_ROUTE_TO"]));
                    } else header("Location: {$CONF["WAP_LOGIN_ROUTE_TO"]}");

                } 

                else {
                    (isset($_SESSION["loginFailCount"])?$_SESSION["loginFailCount"]++:$_SESSION["loginFailCount"]=1);
                    $GLOBALS["LOGIN_FAIL_TYPE"] = "e.password";
                }
            }
        }
    }
}

if ((isset($_GET[$CONF["LOGOUT_SIGNAL_TRIGGER"]])) && (!isset($_POST[$CONF["LOGIN_SIGNAL_TRIGGER"]]))) {

    /*
        Handle admin swapping
    */
    if (isset($_SESSION["swap_id"])) {
        $_SESSION["id"] = $_SESSION["swap_id"];
        session_unregister("swap_id");
        header("Location: ?L=admin.index");
    }

    else {
        (isset($_SESSION["id"])?session_unregister('id'):false);
        (isset($_SESSION["SELF_USER_DATA"])?session_unregister('SELF_USER_DATA'):false);

        header("Location: {$CONF["LOGOUT_ROUTE_TO"]}");
    }
}

Use it for what? If you just want to generate the password hash using mysql - execute the same query that's in the question — AD7six
– AD7six, Commented Jul 20, 2013 at 21:43
@user2603089 From the code you just commented with, it looks like you're not using prepared statements, which leaves a gaping security hole for you. — christopher
– christopher, Commented Jul 20, 2013 at 21:52
As linked by @HieuNguyen it's just '*' . sha1(sha1('mypass', true)); - but you could just call the function on the db anyway. and also from the same question The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. — AD7six
– AD7six, Commented Jul 20, 2013 at 21:52
@AD7six It's the concatenation around the md5 function that makes me think that. Plus we don't know what else is in the query. It could be "WHERE Password = ".mdf($_POST["adminpass"])." AND Username = ."$_POST["adminuser"]; which would still be hugely open to SQL injection. — christopher
– christopher, Commented Jul 20, 2013 at 21:56

chiliNUT · Accepted Answer · 2017-10-10 22:01:53Z

10

OP asked how to do this in php. This is how to do it in php:

function sqlPassword($input) {
    $pass = strtoupper(
            sha1(
                    sha1($input, true)
            )
    );
    $pass = '*' . $pass;
    return $pass;
}

Added for posterity (No reason you would use this, use it if mysql decides to deprecate the PASSWORD function?, for informative purposes only) the mysql equivalent of the php equivalent

SELECT 
  UPPER(
    CONCAT('*', SHA1(UNHEX(SHA1('password'))))
  )

Also see MySQL Hashing Function Implementation

edited Oct 10, 2017 at 22:01

answered Dec 3, 2014 at 21:45

chiliNUT

19.5k14 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peterm · Accepted Answer · 2013-07-20 22:23:00Z

5

If I understand you correctly there is no need to reproduce PASSWORD() in php do all your validation in one go on mysql side using PASSWORD() in your select like this

SELECT `username`,`password`,`id`,`disable_until`,`active` 
  FROM `[x]users`
 WHERE `username` = 'user1' 
   AND `password` = PASSWORD('password')

Here is SQLFiddle demo

Unless you use case sensitive collation don't use LCASE() on username column in your statements. It prevents MySql from using an index (indices) if any is defined on that column and cause a full scan on the table.

On a side note: your code is vulnerable to sql-injections. Consider to use prepared statements.

edited Jul 20, 2013 at 22:23

answered Jul 20, 2013 at 22:15

peterm

93k16 gold badges157 silver badges162 bronze badges

2 Comments

Corbin Over a year ago

There's a security implication of this worth mentioning. In particular, SQL queries can be logged (MySQL's slow query log for example). If you do the hash inside of the query, the password may be written to a log file. (There are other security implications as well, but this is the most urgent.)

Gert van den Berg Over a year ago

The other problem with this is that it will break if MySQL changes the definition of the password function again, like they did in MySQL 4.1.

sjagr · Accepted Answer · 2014-12-03 22:36:01Z

4

Copy-paste from a password encoder interface for a Symfony2 app I wrote that migrated from an old system using the MySQL PASSWORD function:

function mysqlPassword($raw) {
    return '*'.strtoupper(hash('sha1',pack('H*',hash('sha1', $raw))));
}

answered Dec 3, 2014 at 22:36

sjagr

16.6k5 gold badges43 silver badges69 bronze badges

Comments

Community · Accepted Answer · 2019-02-20 14:59:28Z

1

My version of the pre-MySQL 4.1 code.

/* PHP implementation of MySQL pre-4.1 password function.
Based on Perl Crypt::MySQL C code */
function mysql_old_password_hash($password) {
  $len = strlen($password); 
  $add = 7;
  $nr  = 1345345333;
  $nr2 = 0x12345671;
  $tmp = 0;

  foreach (str_split($password) as $chr) {
     if ($chr == " " || $chr == "\t") {
       continue;
     }

     $tmp  = ord($chr);
     $nr  ^= ((($nr & 0x3f)+$add)*$tmp) + ($nr << 8);
     $nr2 += ($nr2 << 8) ^ $nr;
     $nr2 &= 0xffffffff; # We need to limit this to 32-bit
     $add += $tmp;
  }

  // Strip sign bit
  $nr &= 0x7fffffff;
  $nr2 &= 0x7fffffff;
  return sprintf("%08x%08x",$nr,$nr2);
}

For post-4.1, pre-5.7.something versions, look at sjagr or chiliNUT's answers.

The only reason to use this is in order to check passwords in a database that was written in spite of MySQL's advice not to use PASSWORD() for your own applications.

edited Feb 20, 2019 at 14:59

CommunityBot

11 silver badge

answered Oct 23, 2015 at 12:51

Gert van den Berg

2,84136 silver badges45 bronze badges

10 Comments

Gert van den Berg Over a year ago

Hmm... Interesting... It works up to 8 characters... Might need to compare with Crypt::MySQL again....

Gert van den Berg Over a year ago

Fixed it, $nr2 extending beyond 32-bit seems to have been the problem...

Gert van den Berg Over a year ago

@zx1986: I mainly ported the Crypt::MySQL Perl module's XS (C) code to PHP, and that seems to mainly be based on the MySQL source code... The code is not especially easy to follow... (The character ($tmp) gets mixed into the first part of the hash ($nr), the second part ($nr2) gets affected in an indirect way). Probably not very good crypt.... (Unix crypt is a different function, which is DES based...)

Gert van den Berg Over a year ago

@zx1986: The 32-bit issue was mainly checking the C code for what might be different (I know that second part ($nr2) is all that is affected). Noticed their types and though it is worth a try limiting the value with an and. That happened to work... (Had a little test shell function that output the same text encoded via the Perl module, this function and MySQL itself....)

Gert van den Berg Over a year ago

@zx1986: cpansearch.perl.org/src/IKEBE/Crypt-MySQL-0.04/lib/Crypt/… actually

|

icodebuster · Accepted Answer · 2013-07-21 05:56:46Z

0

first use mysql_escape_string($_POST['password']) to escape the password.

then use that as a variable inside the sql query in

WHERE password_field = PASSWORD(USER_SUPPLIED_PASSWORD)

edited Jul 21, 2013 at 5:56

icodebuster

8,9747 gold badges68 silver badges68 bronze badges

answered Jul 20, 2013 at 22:30

Muhammad Adeel

2,8841 gold badge24 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL password() function to PHP

5 Answers 5

Comments

2 Comments

Comments

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related