PASSWORD function missing in Mysql 8 - what to do!

Databases
1

Hey all,

We are setting up a new server to handle some services for our main site.
Anyway, our log-in and sign-in are based on the Mysql PASSWORD() function.
But we just see that Mysql has removed this KEY function!

What can we do to address this issue without breaking our Php code that runs into Millions of lines.

Thanks

2

Interesting post over on stack overflow that may solve your questionsā€¦

Look at accepted answer, last sentence. It has two links of interest. :slight_smile:

3

Marty, I looked at this post at stackflow and 1st suggestion does not work
and the 2nd one that is this:

SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $
CREATE FUNCTION PASSWORD2 (pass_in varchar(50)) RETURNS varchar(50)
BEGIN
  declare n_pass varchar(50);
  set n_pass = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(pass_in))))); 
  return n_pass;
END$

1- Are we supposed to enter this at Mysql itself?

2- What does delimiter $ mean

Or are we to create this as a Php like function?

Thanks

4

Yes, that is a MySQL custom function. You can write your own functions. Reference to how those work can be found hereā€¦

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

But the first answer I pointed you to references the idea that PASSWORD has been deprecated and removed in favor for a more secure set of encryption functionality. I think this second solution you are looking at is only going to be a crutch to keep you running until you eventually can run the first solution. I mean since the SHA-1 algorithm is no longer considered secure, I am not sure if that solution is going to be something long term.

:slight_smile:

Edit: By the way the delimiter is telling MySQL where the function ends. Notice the dollar sign after the ā€œENDā€.

5

Are you saying you are using the built-in MySQL permissions system for your application login? :open_mouth:

6

Hi Marty,

So I did create this function in Mysql. It confirmed it was created OK.
But when we call it to check password, it return Mysql error:
No database selected

SELECT user_id, user_status FROM pns.users WHERE nick_name = ā€˜$unā€™ AND psssd = PASSWORD2(ā€˜$pwā€™);

whats going on!

7

I assume pns is your database where you also created the function password2? If you are sure you created the function in that database, then make sure you are also using pns when running the query. You can also try using pns.password2('$pw').

Just double check that your function is in the right database and that you are calling on that function in context of the pns database.

8

OMG!
After making adjustment you specified above, Now Mysql is throwing this Error message:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation ā€˜=ā€™

What does this mean! How to fix it?

Thanks,

Man of Man, why do MySQL creators make changes to Mysql without consideration to prior work :frowning:

9

Ummm yeahā€¦ as you probably know that databases have character sets (also known as collations) that they use with data. One character set may cover things like latin based languages and thus have certain accent characters etc. Another character set is Unicodeā€¦ aka UTF-8 which is suppose to cover every character in every language in the world. Most people these days are leaning towards just using UTF-8 for everything.

Now you can have a default character set for the database, for tables and I believe even individual columns of your table. Here the error is telling you that it is trying to coerce (translate) one character set to the other so that it can compare them (and it is not working). It is like comparing apples to oranges.

I am not quite sure why you are getting this message as it should be a simple string comparison, but it just depends on how your database is setup. Obviously it is using two different character sets to compare them. For some solutions you can try things mentioned hereā€¦

Now I assume you are running into this stuff because you have recently updated your version of MySQL? You should always test before changing over to a new DB version. Hopefully this is what you are doing now and not in a production system. :slight_smile:

10

Yes, we have moved the site to a new dedicated server which is running Mysql 8x on CentOS7 and from old place which was RedHat 6 Mysql older. To be exact moved one of the services to a new server from old server
And yes, I am testing the site in host mode to see what problems might occur due to new Mysql/Php/ etc.
before we put it live
And yes, all these Mysql darn errors are popping up

That is why I was gripping as to: why do MySQL creators make changes to Mysql without consideration to prior work :frowning:

Anyway, I shall look at your new stackoverflow suggestion to see about fixing this latest Mysql error due to change to new

11

Hello Marty,

So after much checking, I was able to suppress that (new) Mysql (8) related error message by doing this:

SELECT user_id FROM pnc.users WHERE nick_name = ā€˜$unā€™ AND binary psssd = binary pnc.PASSWORD2(ā€˜$pwā€™);

with PASSWORD2 being the function created via above recommended function to replace Old Mysql PASSWORD() which was throwing Error with this newly created one

So now no more (new) Mysql (8) related error messages, but correctly entered Passwords are not matching which means member logins are not working :frowning:

Any suggestions??
Thanks

12

Hello Marty,

After much deep diving, I can confirm that the password result that the old Mysql PASSWORD() function was returning for a given value is NOT the same that the newly created PASSWORD2() function is returning for the same value, with the new PASSWORD2() function being:

CREATE FUNCTION PASSWORD2 (pass_in varchar(50)) RETURNS varchar(50)
BEGIN
  declare n_pass varchar(50);
  set n_pass = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(pass_in))))); 
  return n_pass;
END$

So obviously our member log-ins will not work with this PASSWORD2() function replacing the old Mysql provided PASSWORD() function which is a HUGE problem since we have 2Million+ members whose passwords are recorded with the old Mysql provided PASSWORD() function

Any suggestions will be appreciated

Thanks

13

Very interesting blogā€¦

They claim it matches. I did notice from their example that there might be a capitalization issue? Perhaps check that. But run the examples and verify their results.

Edit: If the results donā€™t match up, perhaps MySQL 8 is doing something new in which case you might be better off just creating passwords in your programming language and storing them in the database rather than having the database do it. You should probably be doing that anyways. I never use the MySQL password function.

1 Like
14

Given that the function was removed as it was no longer secure, perhaps you could bite the bullet, change how you encrypt your usersā€™ passwords, and force them to change their passwords next time they log in?

4 Likes
15

If you do, donā€™t rely on MySQL but rather use the PHP functions, password_hash and password_verify.

4 Likes
16

Are you nuts! Even if we had 20000 members this would be a Impossible, END of the community Event, set aside that we have 2.5-Million members. Very practically speaking If were to send out 20,000 emails per day to inform of need to change their password that would take Half of the Year for these emails to go out meanwhile they could not login :frowning:

17

Marty, thanks. I need to study this carefully and retry tomorrow.
Time to go for a walk by the ocean to clear my head from all this crap caused by Mysql :frowning:

OTN, couple of points & questions:

1- everything else works fine in the new Php/Mysql installation except and only for this PASSWORD() thing

2- if your suggestion stated above does no work, do you think a solution would to replace this Mysql installation with MariaDB? Or even an earlier Mysql 5 like install?

Thanks
Dean

18

Probably, but thatā€™s not really the issue.

The issue is that you want to continue using a known insecure method of encrypting your usersā€™ passwords.

Far bigger sites than yours have had to force users to change passwords. And I would put money on most of your registered users not being active anyway.

1 Like
19

Would it not be simpler to wait until they do log in and show them a message saying they need to change their passwords? Iā€™ve encountered sites where that has happened.

4 Likes
20

I think that is what @Gandalf actually suggested, there was no mention of sending out any emails.

I believe itā€™s a fairly standard way to update from obsolete hashing methods.

2 Likes