And another tips :

To add a new user after that, when using phpmyadmin, just do like before
but select PASSWORD function for the passwd field.

I think now, with the very detailed answer from Thomas and this, you
should be able to do all you need.

Alex

On Dim 10 septembre 2006 5:49, Thomas L. Shinnick a =E9crit :
> 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 but
>>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
> easier
> 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




-------------------------------------------------------------------------
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