Create User Table with an Encrypted Variable 1

[duckiebear]

I'm trying to come up with a user database for logging onto my server where the passwords stored in the table under the column "password" are encrypted. I also need another script I have to understand when it's been 30 days since the password was changed, so I need to capture the date the password is changed. I've looked at a few reference manuals and tried the code:

create database auth;
use auth;
create table auth (
        name            varchar(7) not null,
        pass            varchar(9) ENCRYPT not null,
        fullname		varchar(30) not null,
        date 			datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        deptnumber		varchar(4),        
        phonenumber		varchar(10),
        cellnumber		varchar(10),
        primary key     (name)
);
insert into auth values
  ('name', ENCRYPT 'pass', 'fullname', 'NOW()', 'deptnumber', 'phonenumber', 'cellnumber', );

insert into auth values
  ( 'someuser', ENCRYPT ('test12345'), 'Joe User', 'NOW()', '3421', '504 257-0000', 'none' );

But this does not appear to be correct, and adding the ENCRYPT code into the variable seems to be the wrong way of doing this. Any suggestions on how this is done? Has anyone created a table of users that log on to their server or another system using the MySQL database?

 

[sleipnir214]

This line in your table creation query:

pass varchar(9) ENCRYPT not null,

is not going to work. ENCRYPT can't be used here.


Encrypt() is a function -- you must have parentheses around the value to be encrypted. The first insert won't work because of that. Encrypt() uses the unix crypt() system call, which may or may not be available on your system. The MySQL online manual recommends using MD5() or SHA() instead. If you use MD5(), your column to store passwords must be 32 bytes, if you use SHA(), the password column must 40 bytes. These are the lengths of the values returned by the functions.


Now() is also a function. In your second insert query, you have:

'NOW()'

but what looks like a function invocation is actually a string since it's inside of quotes.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TonyGroves]

You don't need the word ENCRYPT in your table definition. It doesn't seem to be legal anyway.

Also, the ENCRYPT() function only seems to work on Unix systems, as it uses the Unix crypt() system call. An alternative would be the PASSWORD() function, used in the same way.


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Oops! Sleipnir214 got there first.

-----
ALTER world DROP injustice, ADD peace;

 

[duckiebear]

Thanks. I actually am working from a Unix system. So the crypt() function should work. Does the PASSWORD() function need to be defined to encrypt the password or is it a universally understood function.
So, should this work:

create database auth;

use auth;

create table auth (
        name            varchar(7) not null,
        pass            varchar(9) not null,
        fullname		varchar(30) not null,
        date 			datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        deptnumber		varchar(4),        
        phonenumber		varchar(10),
        cellnumber		varchar(10),
        primary key     (name)
);

insert into auth values
  ('name', ENCRYPT 'pass', 'fullname', NOW(), 'deptnumber', 'phonenumber', 'cellnumber', );

insert into auth values
  ( 'testuser', ENCRYPT ('test12345'), 'Joe User', NOW(), '3421', '504 257-0000', 'none' );

 

[sleipnir214]

I strongly recommend using MD5() or SHA() over ENCRYPT(), with a preference toward SHA(). Some crypt() implementations will only hash the first 8 letters of a plaintext. The same is true of some MD5() implementations.


Again, this line:

('name', ENCRYPT 'pass', 'fullname', NOW(), 'deptnumber', 'phonenumber',

will not pass muster. You're still missing your parentheses.


Buy try it and see. MySQL will happily tell you whether you have an error.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[duckiebear]

Ok, thanks. I'm a newbie to MySQl and am just starting off. We recently installed a zeus webserver and I'm trying to get all of the information on the old Netscape server over to the new server and add a new database for users of this server. So, I'm trying to create a database using MySQL for users accessing the server through web projects, though I have no experience with it and only a little SQL experience. And time is of the essence, as I am also trying to learn PERL and PHP at the same time. What a great learning experience! Thanks for your help.