Don't Use Select *
Something you see in a lot of database access code is a select statement
that looks something like this:
SELECT * FROM TableName WHERE ...
While there's technically nothing wrong with it, using the SELECT * syntax
could be stealing away precious performance from your application, and even it
it's not now, it might someday soon.
When you do a SELECT * against a database table it tells the database to
return all the fields from the selected rows in the given table. The problem
with doing that is that you rarely actually need all the fields for any one
page and moving around all that extra data can really slow things down. This is
especially true if your database and web server run on separate computers since
the extra data then needs to be transferred over the network.
The response that I usually get when I tell people this is that the table
is small and it doesn't really matter. I wholeheartedly disagree. Even if
you're retrieving data from a table that only contains a few fields, how do you
know that table will only contain those fields in the future? Very few
databases and web development projects are static. Things change and your
application needs to be able to roll with the punches. Here's an example to
illustrate my point.
Let's say that you've got an employee table in your database that lists
your current employees. You'll obviously want a page on your intranet (and
maybe even your public site) that lists these employees. Assume the employee
table contains just a few fields: id, first name, last name, department, and
phone number. If you were to build a simple phone list that lists employees by
department and provides their phone numbers you might use a database query
something like this:
SELECT * FROM Employee
as opposed to typing out what you really mean:
SELECT id, first_name, last_name, department, phone_number FROM Employee
Right now there's really no difference between the two, but if six months
down the road you decide to add a picture of each employee to the database, are
you going to remember to go back and change the SQL query? If not, now with
every call to that page the web server requests every field and is transferred
a picture of each employee that it doesn't even use. Since pictures tend to be
large, you'd be transferring a lot of data for no reason!
It may take a few extra seconds to type out the field names, but it's a
good habit to get into and the performance savings can make it well worth the
time.
Basic authentication vs. NT
Challenge and Response
When you password protect a web page using Internet Service Manager, you
have the option of choosing either Basic authentication or NT Challenge and
Response (aka: Integrated Windows authentication). The difference in the two
methods is in the way the username and passwords are transmitted over the
Internet. NT Challenge and Response encrypts the password so malicious snoopers
can not intercept and use the information. Basic authentication sends the
password as plain text. While it would be great to use NT Challenge and
Response for all secured web pages, the only web browsers that currently
support this protocol are Internet Explorer 3 and higher. If you might have
users with other web browsers, your only choice is Basic authentication.
If you would like to have a secure website take advantage of using
encrypted usernames and passwords but still want to be compatible with Netscape
browsers, you can use Basic authentication over SSL. Using Secure Sockets will
encrypt the user name and password but at the same time will still let Netscape
browsers use your site, the best of both worlds.