| As a brief conclusion to this chapter, I should mention a few of the security issues to consider when using and administering your databases. Then I will demonstrate a few more MySQL functions that can be used to encrypt and decrypt data. Security practices If you have administrative-level control over your database, you should keep in mind the following: Do not allow anonymous users to connect to MySQL. Always require a password to connect to MySQL. Require users to also specify a hostname. This limits from what computers users can and cannot access MySQL (although this requirement can be tedious). Assign each user the absolute minimum required privileges. Limit the root user to localhost access only. Delete the test database, which, by default, any user can access. Delete unused user accounts. When storing sensitive information in a table, particularly passwords, protect the data first using an encryption function. Validate user-submitted data before inserting it into a database as discussed earlier in the chapter and throughout the book. Use the .php extension for the file containing your database connectivity information. If at all possible, store the database connection script outside of the Web root directory.
Most of these issues relate to permissions, which is discussed in Appendix A, "Installation." Encryption MySQL has several encryption and decryption functions built into the software. You've already seen one of these, SHA(), since I've been using it to encrypt passwords stored in the database. Another function, ENCRYPT(), is like SHA() in that it encrypts a string but differs in that you can add a salt parameter to help randomize the encryption process. INSERT INTO users (username, password)  VALUES ('trout, ENCRYPT('password', 'salt));
ENCRYPT() uses the Unix crypt() software, so it may not be available on your particular system (in particular, on Windows). MySQL has another encryption function called DES_ENCRYPT(), usable only over an SSL connection. Both the SHA() and ENCRYPT() functions create an encrypted string that cannot be decrypted. This is a great safety feature because it means that stored information cannot be retrieved in readable form. If you require data to be stored in an encrypted form that can be decrypted, you'll need to use either ENCODE() and DECODE() or AES_ENCRYPT() and AES_DECRYPT(). These functions also take a salt argument, which helps to randomize the encryption. INSERT INTO users (username, password) VALUES ('trout, ENCODE('password', 'salt)); SELECT DECODE(password, 'salt') AS  passwd FROM users WHERE username= 'trout;
The AES_ENCRYPT() function is considered to be the most secure encryption option, but it is only available as of MySQL version 4.0.2. Because of this restriction, I'll begin by demonstrating ENCODE() and DECODE(). To do so, I'll run some queries on the test database using the mysql client. To encrypt and decrypt data 1. | Log into the mysql client and select the test database (Figure 10.23).
USE test;
Figure 10.23. The following examples will all be run in the mysql client, on the test database. 
Follow the steps outlined in Chapter 4, "Introduction to SQL and MySQL," to connect to the mysql client. Alternatively, you can use phpMyAdmin or another interface to run the queries in the following steps.
| 2. | Create a new encode table (Figure 10.24).
CREATE TABLE encode ( id INT UNSIGNED NOT NULL AUTO_ INCREMENT, card_number TINYBLOB, PRIMARY KEY (id) );
Figure 10.24. The encode table, consisting of only two columns, is added to the database. 
This table, encode, will contain fields for just an id and a (credit) card_number. The card_number will be encrypted using ENCODE() so that it can be decoded. ENCODE() returns a binary value that ought to be stored in a BLOB (or TINYBLOB here) column type.
| 3. | Insert a new record (Figure 10.25).
INSERT INTO encode (id, card_number) VALUES (NULL, ENCODE (1234567890123456, 'eLL10tT));
Figure 10.25. A record is inserted, using an encryption function to protect the credit card number. 
Here I am adding a new record to the table, using the ENCODE() function with a salt of eLL10tT to encrypt the card number. Always try to use a unique salt with your encryption functions.
| 4. | Retrieve the record in an unencrypted form (Figure 10.26).
SELECT id, DECODE(card_number, 'eLL10tT) AS cc FROM encode;
Figure 10.26. The record has been retrieved, decrypting the credit card number in the process. 
This query returns all of the records, decrypting the credit card number in the process. Any value stored using ENCODE() can be retrieved (and matched) using DECODE(), as long as the same salt is used (here, eLL10tT).
| 5. | Check out the table's contents without using decryption (Figure 10.27).
SELECT * FROM encode;
Figure 10.27. Encrypted data is stored in an unreadable format (here, as a binary string of data). 
As you can see in the figure, the encrypted version of the credit card number is unreadable. This is exactly the kind of security measure required by e-commerce applications.
|
 Tips
When using ENCRYPT(), ENCODE(), or DECODE() from a PHP script, be sure to store the salt in a secure place. Alternatively, you can use the technique demonstrated in the next section to store the salt in the database. As a rule of thumb, use SHA() for information that will never need to be viewable, such as passwords and perhaps usernames. Use AES_ENCRYPT() (or ENCODE() on older versions of MySQL) for information that needs to be protected but may need to be viewable at a later date, such as credit card information, Social Security numbers, addresses (perhaps), and so forth. The ENCRYPT() function will return a NULL value if it is not available on an operating system.
Secure salt storage While the preceding sequence of steps demonstrates how you can add a level of security to your Web applications by encrypting and decrypting sensitive data, there's still room for improvement. For starters, the AES_ENCRYPT() function is a more secure option and is recommend if you are using MySQL 4.0.2 or later. Its syntax is the same as that of the ENCODE() function: INSERT INTO users (username, userpass) VALUES ('trout, AES_ENCRYPT ('password, 'salt')) SELECT AES_DECRYPT(userpass, 'salt') AS passwd FROM users WHERE username= 'trout
Another issue is the encryption salt, which is key to the encryption process. In order for a Web application to use a salt in its queries, PHP must store this value somewhere, most likely in the database connection script (as a constant). But storing this value in a plain text format on the server makes it more vulnerable. Instead, I'll store the salt in a database table. To use it in a query without going through PHP, I then select it and assign the value to a MySQL user-set variable. To just establish a user-defined variable, use this SQL command: SELECT @var:='value'
To define a variable based upon a value stored in a table, the syntax is SELECT @var:=some_column FROM tablename
Once you've established @var, it can be used in other queries: INSERT INTO encode (card_number) VALUES (AES_ENCRYPT(1234567890123456, @var))
This last sequence of steps will demonstrate this approach in action. To use a database-stored salt 1. | Log into the mysql client and select the test database, if you haven't already.
| 2. | Empty the encode table (Figure 10.28).
TRUNCATE TABLE encode;
Figure 10.28. Run a trUNCATE query to empty a table. 
Because I'm going to be using a different encryption function, I'll want to clear out all the existing data before repopulating it. The TRUNCATE command is the best way to do so.
| 3. | Create and populate an aes_salt table (Figure 10.29).
CREATE TABLE aes_salt ( salt VARCHAR(12) NOT NULL ); INSERT INTO aes_salt (salt) VALUES ('0bfuscate);
Figure 10.29. The aes_salt table has one column and should only ever have one row of data. The INSERT query stores the salt value in this table. 
This table, aes_salt, will store the encryption salt value in its one column. The INSERT query stores the salt, which will be retrieved and assigned to a user-defined variable as needed.
| 4. | Retrieve the stored salt value and use it to insert a new record into the encode table (Figure 10.30).
SELECT @salt:=salt FROM aes_salt; INSERT INTO encode (card_number) VALUES (AES_ENCRYPT (1234567890123456, @salt));
Figure 10.30. These two queries show how you can use an established salt that was previously stored in a database table. 
The first line retrieves the stored salt value from the aes_salt table and assigns this to @salt (the figure shows the results of the SELECT statement). Then a standard INSERT query is run to add a record to the encode table. In this case, @salt is used in the query instead of a hard-coded salt value.
| 5. | Decrypt the stored credit card number (Figure 10.31).
SELECT @salt:=salt FROM aes_salt; SELECT id, AES_DECRYPT(card_number, @salt) AS cc FROM encode;
Figure 10.31. A similar process (see Figure 10.30) is used to decrypt stored information using a database-stored salt. 
The first step retrieves the salt value so that it can be used for decryption purposes. (If you followed these steps without closing the MySQL session, this step wouldn't actually be necessary, as @salt would already be established.) The @salt variable is then used with the AES_DECRYPT() function.
|
Tips User variables are particular to each connection. When one script or one mysql client session connects to MySQL and establishes a variable, only that one script or sessions has access to that variable. Prior to version 5.0 of MySQL, user variable names are case-sensitive. You cannot use a user-defined variable as a parameter in a LIMIT clause. Never establish and use a user-defined variable within the same SQL statement.
|
