Configuring Servers and Databases with Chrome - Creating account tables
(Page 5 of 5 )
Rather than having the PHP script compare usernames and passwords that are kept in the script, we can write code to access the database and compare its entries with the fields entered in the XUL interface.
Databases are organized as tables of information rows. Users can access row information through SQLSELECTstatements that include qualifiers to help identify the information being sought. We can embed theSELECTstatement in our PHP scripts that support the SQL access libraries.
The database administrator creates the database table, specifies its structure, and assigns privileges to restrict a user account’s ability to access and modify table data.
For our simple application, we will create a table namedaccountsto hold the username and password information. We will also add information to indicate a user’s status (e.g., active or inactive), and the time and date of the last login. The initial assumptions of the format for the row data are:
Username
Up to 40 characters (arbitrary)
Password
Up to 40 characters
Status
A string of up to 16 characters to support entries
such as active, suspended, provisional (for temporary
accounts), and terminated
Date
A string that describes the date and time of the last
successful login session
The SQL statement to create such a table consists of aCREATEstatement:
mysql> create table account (
-> username char(40) not null primary key,
-> password char(40) not null,
-> last_session datetime,
-> status char(32) not null);
Query OK, 0 rows affected (0.47 sec)
This statement creates our tables with rows named username, password, last_session, andstatus. The data types include character strings and a timestamp (an SQL-formatted statement for time of day). Additional qualifiers allow us to specify that the username will be unique (as a primary key), and which fields must have values specified when a table row is created.
We can see the results of our work through thedescribecommand:
mysql> describe account;
Field | Type | Null | Key | Default | Extra |
username | char(40) | | PRI | |
password | char(40) | | | |
last_session | datetime | YES | | CURRENT_TIMESTAMP |
status | char(32) | | | |
4 rows in set (0.00 sec)
mysql>
We can now create the accounts for the application users—individuals who will be using our NewsSearch service.
Rather than storing the passwords for our users in plain text, we will add some security by using MySQL’s one-way encryption.
A one-way encryption scheme applies a function to input text to render an encrypted form of it in the database. The programmer or application scripts do not need to know what the encrypted output is, only that the encryption function is used consistently for comparison purposes. Should the database be compromised, there is no way for the intruder to reverse the encrypted data into the user-provided passwords.
A number of encryption functions are available for both PHP and MySQL. For this application, we will use MySQL’s function for the Secure Hash Algorithm (SHA):sha1('someEntry'). We can now create a couple of application users who will be in our authentication database.
Although we could use the MySQL command-line interpreter to do this, it is often easier to create a text file with the commands we need to manipulate the database. A createUser.sql file to create two users would look like this:
use newssearch;
insert into account values ('bugsbunny',sha1('wabbit'),'','active');
insert into account values ('elmerfudd',sha1('scwewy'),'','active');
We could read in this script file directly from the operating system command prompt:
%mysql –u root –p < createUsers.sql;
>password: 'sqlRootPassword'
%
We could also use the source command (\.) to enter the script name while within the MySQL interpreter. The following command shows how to load the script from the current working directory:
mysql> \. createUsers.sql
We can view the results of our script files by issuing aSELECTcommand:
mysql> select * from account;
The results will show the account information created by the script file, along with the encrypted passwords. Thelast_sessionentries will be initialized to 0, as no entry was entered in theINSERTstatement.
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter four of Programming Firefox, written by Kenneth C. Feldt (O'Reilly, 2007; ISBN: 0596102437). Check it out today at your favorite bookstore. Buy this book now.
|
|
