A PHP Application: The Wedding Gift Registry - Learning MySQL - page 481

In our environment, the web server and the MySQL DBMS are running on the same
host machine, so the user needs access only from localhost . Having the DBMS and
web server on the same machine is a good decision for small- to medium-size web
database applications because there is no network communications overhead between
the DBMS and the web server. For high-traffic or complex web database applications,
it may be desirable to have dedicated hardware for each application.
Verifying New Users
In our simple application, we've allowed users to create new accounts for themselves.
For applications where security is more important, new accounts might need to be
added or approved by the system administrator. To ensure that the email address is
valid and owned by the person requesting the account, you can also ask new users to
authenticate themselves through an email verification step. For each new account re-
quest, you can generate and store a random verification key, and then send an email
with a verification link, specifying the user and key:
http://www.invyhome.com/verify.php?user_id=313&key=b114bcf8e4a110a786f19f5
When the user reads the email and opens this address in their browser, the application
can check that the key matches the one stored in the database for this user; if so, the
account can be activated. This is still vulnerable to a brute-force attack, where an at-
tacker tries all possible permutations of characters to find the correct one—rather like
trying all possibilities on a combination lock. For added security, you can count how
many times you receive verification attempts for a particular user and block the account
(and notify the administrator) if there are more than, say, 10 attempts.
Authenticating the User
Once we have successfully created an active connection to the MySQL server, we can
use it in conjunction with other PHP functions to run queries on the database and
retrieve data. We can execute an SQL query on the MySQL server using the
mysqli_query( ) function. This function takes two parameters: the DBMS connection
to use and the query to execute.
The query does not need to be terminated with a semicolon. For a successful query that
returns no answer rows, it returns TRUE ; for a successful SELECT , SHOW , DESCRIBE , or
EXPLAIN query, it returns the query results for later processing. For an unsuccessful
query, it returns FALSE :
// Create a query to find any rows that match the provided username
$query = "SELECT username, password FROM users WHERE username = '$username'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);