1] I am converting an ASP/Access site to PHP/mySQL. I know Access, but am new to mySQL. If I have a MEMO field in Access to store lengthy paragraphs of text, what field type should I use in mySQL?
The variable-length, character field types available to you are:
Datatype Storage Storage Maximum
w/ max of re'q for re'q for String Size
150 chars 144 char 30 char
indicated string string
CHAR(150) 150 150 255
VARCHAR(150) 145 31 255
TINYTEXT(150) 145 31 255
TEXT(150) 146 32 65,535
MEDIUMTEXT(150) 147 33 16,777,215
LONGTEXT 148 34 4,294,967,295
(After that, youwould need to go to a BLOB datatype.)
2] To store usernames and passwords in the mySQL database, how can I encrypt the password so it's not stored in plain text (in a VARCHAR field)?
There are a couple of approaches.
Approach #1:
You could use the ENCRYPT() function to encrypt the username and password. Unfortunately, I do not know of a corresponding DECRYPT() function. That would mean that, once a username and/or password was encrypted, you could only encrypt the username/password that has been provided and verify that it encrypts correctly . . . you could never extract the username/password even as an administrator. This would lead to your needing a "backdoor" (possibly a known administration Username/Password) in order to "resetting" the Username/Password and (perhaps) let/require the User change the Username/Password when they get access again.
Approach #2:
You could also create a routine (which could be incorporated into a DLL or COM object) that you would use to validate the login. This routine could could provide the means to encrypt and decrypt the Username and Password (as well as any other, e.g. credit card related) fields.
