Thomas L. Shinnick wrote:
> At 07:34 PM 9/9/2006, nix4me wrote:
>> Alex V. wrote:
>> > Just use this request :
>> > "UPDATE ftpusers SET `password`=3DPASSWORD(password)"
>> >
>> > Assuming that the table name is ftpusers and password field is =


>> "password"
>> >
>> > Cheers,
>> > Alex
>> >
>> > On Sam 9 septembre 2006 17:40, nix4me a =E9crit :
>> >> bieniu wrote:
>> >> =


>> >>> Witaj nix4me,
>> >>>
>> >>> Saturday, September 9, 2006, 4:14:09 PM, you wrote:
>> >>> =


>> >>>> How do I encryt the passwords to put in database?
>> >>>> =


>> >>> just use "encrypt" method to insert data in database.
>> >>> =


>> >> Well it appears I have no idea how to use mysql. Encrypt method means
>> >> nothing to me.
>> >>
>> >> I will have to do some more reading.
>> >>
>> >> Mark
>> > =


>> My password column is called passwd and the rows under it contain the
>> clear text password of each user. I guess i am just confused as to what
>> to do. I tried changing passwd to password=3D(password) in phpmyadmin b=

ut
>> that broke proftpd.
>>
>> I just can't seem to understand what to do in order to use encrypted
>> passwords. Ive tried searching the net but all howtos for proftpd-mysql
>> say to do it exactly like I have it.
>>
>> Thanks,
>> Mark

>
> I haven't seen you mention your table name, so I'll have to guess. =


> I'll assume the table name is "ftpusers" and the column name is =


> "passwd" , like you mentioned. The 'magic' SQL command uses the table =


> name and the column name, so we'd need those to be sure exactly how to =


> write the command. If your table name is different, just change the =


> commands you see below to be correct for you.
>
> What you have said is that your password strings that are in column =


> passwd are currently plaintext strings. So you might see something =


> like this:
> mysql> select userid,passwd from ftpusers;
> +--------+-----------------+
> | userid | passwd |
> +--------+-----------------+
> | me | Im in plaintext |
> | you | real password |
> +--------+-----------------+
> 2 rows in set (0.00 sec)
>
> The passwords are in 'plaintext' if you can 'read' them. And you are =


> right, that's probably a bad thing.
>
> So you want to change them into an encrypted form that _can't_ be =


> 'read'. =


>
> MySQL can store a value that has been encrypted. We don't know =


> exactly what MySQL's encryption method is. MySQL has its own function =


> to do encryption of a string value called PASSWORD(). While using the =


> MySQL client program you can see this transformation by typing in:
> mysql> select PASSWORD('foo');
> +------------------+
> | PASSWORD('foo') |
> +------------------+
> | 7c786c222596437b |
> +------------------+
> 1 row in set (0.00 sec)
>
> You want to transform the plaintext values you already have, into the =


> encrypted values that MySQL knows about, and that later we can tell =


> ProFTPD to use. =


>
> And this can be done in one 'magic' SQL command. =


>
> But first, capture your current table into a file!!! That way you can =


> recover if something goes wrong! If your database is 'proftpd_db' and =


> your table is 'users', at the command line you could enter:
> mysqldump proftpd_db users >proftpd_db.users.lst
> and capture a source copy of your current table. =


>
> SQL commands can be run on any number of rows in your database. If =


> you select a particular row you will operate on just that one row. =


> But if you don't say which row, SQL will do *all* rows of the table. =


> Very flexible, very dangerous! (You did do the mysqldump to save a =


> copy, right?)
>
> The SQL command that people (Alex) have mentioned was
> (using your column name 'passwd' and the assumed table name 'ftpusers'):
> UPDATE ftpusers SET passwd=3DPASSWORD(passwd);
>
> What this says is: For the table called 'ftpusers', for all rows of =


> that table (because we didn't say otherwise), find the value in the =


> 'passwd' column, give it to the PASSWORD() function, then take the =


> value returned from that function and change (update) the column =


> 'passwd' value.
>
> We are replacing the original value of column 'passwd', with the value =


> after encryption by PASSWORD(), for every row in the table.
>
> Don't do it twice! Once you have converted the plaintext passwords =


> into the encrypted versions, the plaintext is _gone_. (I hope you =


> made that copy ;-)
>
> If you had those values shown above (e.g. "Im in plaintext" and "real =


> password"), you would now see:
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 15979a9434bab95b |
> | you | 7c4674352105a7a6 |
> +--------+------------------+
> 2 rows in set (0.00 sec)
> Can't read that! And neither can the bad guys. =


>
> But MySQL can compare this encrypted value, with the password string =


> that the user enter, by first encrypting the user input and _then_ =


> comparing the two encrypted values. If the two encrypted values =


> match, the original passwords matched. (That's the theory, anyway)
>
> Note that above I said "by first encrypting the user input and _then_ =


> comparing". How does ProFTPD/MySQL know that the column 'passwd' is =


> encrypted and they need to do this work? You tell them.
>
> azurIt mentioned the ProFTPD configuration statement
> SQLAuthTypes Backend
> What this tells the mod_sql_mysql module is that the password is =


> encrypted, the way that MySQL encrypts values. So mod_sql_mysql =


> should first encrypt the user input, then compare against the =


> encrypted value found in the 'passwd' column for that userid. =


>
>
> Now if it were me, here's what I would do:
> - save a copy of the database table ;-p
> - add "SQLAuthTypes Plain Backend" to the configuration
> - restart ProFTPD
> - start up MySQL client and change just *one* user's entry
> update ftpusers set passwd=3Dpassword('testpswd') where userid =

=3D =

> 'nix4me';
> - try logging in as that user, using the old plaintext password - =


> should fail
> - try logging in as that user, using the new password 'testpswd' - =


> should work
> - try logging in as someone else using their correct password - =


> should work
> (check we haven't broken anything)
> great, now you know that encrypted passwords work
>
> Be sure you know that the encrypted password is working! Maybe even =


> dump out all the passwords to see that it *is* encrypted
> select userid,passwd from ftpusers;
> or =


> select userid,passwd from ftpusers where userid =3D 'nix4me';
>
> Once you are confident, finish up:
> - in MySQL client, change the test user back to plaintext
> update ftpusers set passwd=3Dpassword('testpswd') where userid =

=3D =

> 'nix4me';
> - now convert everyone's plaintext password to encrypted passwords:
> update ftpusers set passwd=3Dpassword(passwd);
> - replace that config line with "SQLAuthTypes Backend" to enable
> only encrypted passwords from the database.
>
>
> I hope that somewhere in the above is the information you need. =


> Good luck! (but save a copy of the table first)
>
>
> ----------------------------------------------------------
> Just checking out the SQL commands. Here I get into the MySQL client =


> program and try out all these commands. Review the following to see =


> what happens. I create a temporary table to use called 'ftpusersx' - =


> a different name because I don't want to break my real table.
>
> mysql> create temporary table ftpusersx ( userid char(80), passwd =


> char(80) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into ftpusersx (userid,passwd) values ('me','hiya');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into ftpusersx (userid,passwd) values ('you','bye');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select userid,passwd from ftpusersx;
> +--------+--------+
> | userid | passwd |
> +--------+--------+
> | me | hiya |
> | you | bye |
> +--------+--------+
> 2 rows in set (0.00 sec)
>
> mysql> update ftpusersx set passwd=3D'Im in plaintext' where userid =3D '=

me';
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> update ftpusersx set passwd=3D'real password' where userid =3D 'yo=

u';
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> >>>> Above is trying to show that update command changes values
> >>>> in the column(s) you select, on the rows you select. Here I
> >>>> select the column 'passwd', set it to the string value, but only
> >>>> for the one row I select. The "where clause" says match just the
> >>>> the one row where column userid value matches the string.

>
> mysql> select userid,passwd from ftpusersx;
> +--------+-----------------+
> | userid | passwd |
> +--------+-----------------+
> | me | Im in plaintext |
> | you | real password |
> +--------+-----------------+
> 2 rows in set (0.00 sec)
>
> >>>> Okay, let's try out the magic SQL command. Whoosh....

>
> mysql> update ftpusersx set passwd=3Dpassword(passwd);
> Query OK, 2 rows affected (0.00 sec)
> Rows matched: 2 Changed: 2 Warnings: 0
>
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 15979a9434bab95b |
> | you | 7c4674352105a7a6 |
> +--------+------------------+
> 2 rows in set (0.00 sec)
>
> >>>> Below I wanted to show updating just one user's password.
> >>>> So I set one row's column back to the 'plaintext' value, displayed
> >>>> the table again to check, then executed the SQL statement using
> >>>> the plaintext string as the value given to PASSWORD, and
> >>>> this time selected just one row. Then re-displayed the table.

>
> mysql> update ftpusersx set passwd=3D'real password' where userid =3D 'yo=

u';
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 1693bed1240a3b38 |
> | you | real password |
> +--------+------------------+
> 2 rows in set (0.00 sec)
>
> mysql> update ftpusersx set passwd=3Dpassword('real password') where =


> userid =3D 'you';
> Query OK, 1 row affected (0.01 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 1693bed1240a3b38 |
> | you | 7c4674352105a7a6 |
> +--------+------------------+
> 2 rows in set (0.00 sec)
>
> >>>> Below I do pretty much the same, but with the SQL syntax
> >>>> that says "use the current value in 'passwd' as the input
> >>>> to PASSWORD()."

>
> mysql> update ftpusersx set passwd=3D'real password' where userid =3D 'yo=

u';
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 1693bed1240a3b38 |
> | you | real password |
> +--------+------------------+
> 2 rows in set (0.00 sec)
>
> mysql> update ftpusersx set passwd=3Dpassword(passwd) where userid =3D 'y=

ou';
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select userid,passwd from ftpusersx;
> +--------+------------------+
> | userid | passwd |
> +--------+------------------+
> | me | 1693bed1240a3b38 |
> | you | 7c4674352105a7a6 |
> +--------+------------------+
> 2 rows in set (0.00 sec)
>
>
> ------------------------------------------------------------------------
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job ea=

sier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=...=3D263057&dat=

=3D121642
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> ProFTPD Users List
> Unsubscribe problems?
> http://www.proftpd.org/list-unsub.html

I want to thank you for the great response. I have played with all of =

your suggestions and I understand the process much more now.

I do have to say that using the PASSWORD() function is NOT working with =

SQLAuthtypes Backend. However, using Encrypt with SQLAuthtypes Crypt is =

working fine.

No matter which way I try it, the PASSWORD() option is causing failed =

logins. But when i change to the Crypt method, its works perfectly. I =

tried the password() method both from the mysql prompt just like you =

posted and from phpmyadmin with the same results. I also tried the =

Crypt method both ways and it works fine.

I will keep playing with it because from what I have read, the =

password() encryption is a little stronger than the crypt(3).

Has anyone else experienced this problem?

Mark

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easi=
er
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=...63057&dat=3D1=
21642
_______________________________________________
ProFTPD Users List
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html