Database Reference
In-Depth Information
your username, password and many other things that you enter into the site are being
stored behind the scenes in a database. If you are designing such a system you should give
some thought to how you are going to store your passwords. The temptation is to store
them as plain text strings, but that is not the safest way of doing it. If you look back at
Figure 12.6 you will remember that SQL automatically encrypted the password so that it
was not stored in an obvious way within the user table.
So if you are creating your own user table for your website, MySQL has provided an
encrypt function that allows you to code your passwords before you store them in the table.
This function is called PASSWORD(), and is used as follows:
PASSWORD('password_string')
The function will return an encrypted version of the password string, which can then be
inserted into your database table. For example, the following insert statement would insert
an encrypted password into a table containing your web users:
INSERT INTO webuser (username,
password,
email)
VALUES ( 'mountainman',
PASSWORD('bigfoot'),
'someone@nowhere.com')
In reality, of course, instead of running the query as above, you would have inserted vari-
ables obtained from web form fields into the table, and you would have encrypted the vari-
able using PASSWORD, not just the typed text.
Interestingly, there is no DECRYPT function. This is to stop you doing:
SELECT user,
DECRYPT(password) FROM mysql.user
This obviously would be a huge security risk. So how do you check to see if someone has
entered the same password that is in the database? Well, everytime that you encrypt the
same text string with PASSWORD, it always produces the same encrypted string. For
instance, run the following query:
SELECT PASSWORD('yeti'), password
FROM
user
This query will encrypt the word yeti, and output it along with all of the password
strings stored in the user table. Figure 12.11 shows the results.
You may remember when we GRANTed some rights to the Mary user, we identified her
by the password yeti . Look at the results in Figure 12.11 and you should see that the 6th row
down contains the same string in both columns. So you can compare encrypted strings to
see if they match. If an existing user typed his password into a webpage, we can encrypt it
and use it in a where clause to check that the password typed is the same as the one stored.
For example, the following:
Search WWH ::




Custom Search