--===============1790284681==
Content-Type: multipart/alternative;
boundary="=====================_553100406==.ALT"

--=====================_553100406==.ALT
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

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=20
I'll have to guess. I'll assume the table name=20
is "ftpusers" and the column name is "passwd" ,=20
like you mentioned. The 'magic' SQL command uses=20
the table name and the column name, so we'd need=20
those to be sure exactly how to write the=20
command. If your table name is different, just=20
change the commands you see below to be correct for you.

What you have said is that your password strings=20
that are in column passwd are currently plaintext=20
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=20
'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=20
encrypted. We don't know exactly what MySQL's=20
encryption method is. MySQL has its own function=20
to do encryption of a string value called=20
PASSWORD(). While using the MySQL client program=20
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=20
already have, into the encrypted values that=20
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=20
file!!! That way you can recover if something=20
goes wrong! If your database is 'proftpd_db' and=20
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=20
your database. If you select a particular row=20
you will operate on just that one row. But if=20
you don't say which row, SQL will do *all* rows=20
of the table. Very flexible, very=20
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=20
'ftpusers', for all rows of that table (because=20
we didn't say otherwise), find the value in the=20
'passwd' column, give it to the PASSWORD()=20
function, then take the value returned from that=20
function and change (update) the column 'passwd' value.

We are replacing the original value of column=20
'passwd', with the value after encryption by=20
PASSWORD(), for every row in the table.

Don't do it twice! Once you have converted the=20
plaintext passwords into the encrypted versions,=20
the plaintext is _gone_. (I hope you made that copy ;-)

If you had those values shown above (e.g. "Im in=20
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=20
the password string that the user enter, by first=20
encrypting the user input and _then_ comparing=20
the two encrypted values. If the two encrypted=20
values match, the original passwords matched. (That's the theory, anyway)

Note that above I said "by first encrypting the=20
user input and _then_ comparing". How does=20
ProFTPD/MySQL know that the column 'passwd' is=20
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=20
the password is encrypted, the way that MySQL=20
encrypts values. So mod_sql_mysql should first=20
encrypt the user input, then compare against the=20
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=20
passwd=3Dpassword('testpswd') where userid =3D 'nix4me';
- try logging in as that user, using the old=20
plaintext password - should fail
- try logging in as that user, using the new=20
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=20
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=20
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=20
into the MySQL client program and try out all=20
these commands. Review the following to see=20
what happens. I create a temporary table to use=20
called 'ftpusersx' - a different name because I=20
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 'you';
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 'you';
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=20
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 'you';
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=
'you';
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)


--=====================_553100406==.ALT
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



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
'you';

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
'you';

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
'you';

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 'you';

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)





--=====================_553100406==.ALT--


--===============1790284681==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

-------------------------------------------------------------------------
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=...057&dat=121642
--===============1790284681==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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