PHP 5/MySQL Programming- P85 pot
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (123.82 KB, 5 trang )
<hr>
<form action = “editTable.php”
method = “post”>
<table border = 1>
<tr>
<td colspan = 2><center>
<h2>Edit / Delete table data</h2>
</center></td>
</tr>
<tr>
<td>Password:</td>
<td>
<input type = “password”
name = “pwd”
value = “absolute”><br>
</td>
</tr>
<tr>
<td colspan = 2><center>
<select name = “tableName”
size = 5>
<option value = “agent”>agents</option>
<option value = “specialty”>specialties</option>
<option value = “operation”>operations</option>
<option value = “agent_specialty”>agent_specialty</option>
<option value = “storedQuery”>storedQuery</option>
</select>
</center></td>
</tr>
<tr>
<td colspan = 2><center>
<input type = “submit”
value = “edit table”>
</center></td>
</tr>
</table>
398
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
</form>
</body>
</html>
To make debugging easier, I preloaded the password field with the appropriate
password. In a production environment, you should, of course, leave the password
field blank so the user cannot get into the system without the password.
Building the viewQuery.php Program
When the user chooses a query, program control is sent to the viewQuery.php
program. This program does surprisingly little on its own:
<html>
<head>
<title>View Query</title>
</head>
<body>
<center>
<h2>Query Results</h2>
</center>
<?
include “spyLib.php”;
$dbConn = connectToSpy();
//take out escape characters
$theQuery = str_replace(“\’”, “‘“, $theQuery);
print qToTable($theQuery);
print mainButton();
?>
</body>
</html>
TRICK
399
C
h
a
p
t
e
r 1
2B
u
i
l
d
i
n
g
a
T
h
r
e
e
-T
i
e
r
e
d
D
a
t
a
A
p
p
l
i
c
a
t
i
o
n
Once viewQuery.php connects to the library, it uses functions in the library to con-
nect to the database and print desired results. The
qToTable() function does most
of the actual work, taking whatever query is passed to it and generating a table
with add, delete, and edit buttons.
The
str_replace() function is necessary because SQL queries contain single quo-
tation mark (
‘) characters. When I store a query as a VARCHAR entity, the single quo-
tation marks embedded in the query cause problems. The normal solution to this
problem is to use a backslash, which indicates that the mark should not be imme-
diately interpreted, but should be considered a part of the data. The problem with
this is the backslash is still in the string when I try to execute the query. The
str_replace() function replaces all instances of \’ with a simple single quote (‘).
Note that the
qToTable() function doesn’t actually print anything to the screen.
All it does is build a complex string of HTML code. The
viewQuery.php program
prints the code to the screen.
If you are using a library, it’s best if the library code does not print anything directly
to the screen. Instead, it should return a value to whatever program called it. This
allows multiple uses for the data. For example, if the qToTable() function printed
directly to the screen, you could not use it to generate a file. Since the library code
returns a value but doesn’t actually do anything with that value, the code that calls
the function has the freedom to use the results in multiple ways.
TRICK
400
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
WHY STORE QUERIES IN THE DATABASE?
You might wonder why I chose to store queries in the database. After all, I could
have let the user type in a query directly or provided some form that allows the
user to search for certain values. Either of these approaches has advantages, but
they also pose some risks. It’s very dangerous to allow direct access to your data
from a Web form. Malicious users can introduce Trojan horse commands that
snoop on your data, change data, or even delete information from the database.
I sometimes build a form that has enough information to create an SQL query and
then build that query in a client-side form. (Sounds like a good end-of-chapter
exercise, right?) In this case, I stored queries in another table. People with
administrative access can add new queries to the database, but ordinary users
do not. I preloaded the storedQuery database with a number of useful queries,
then added the capacity to add new queries whenever the situation demands it.
Drawbacks remain (primarily that ordinary users cannot build custom queries),
but it is far more secure than a system that builds a query based on user input.
The mainButton() function produces a simple HTML form that directs the user
back to the
spyMaster.php page. Even though the code for this is relatively simple,
it is repeated so often that it makes sense to store it in a function rather than
copying and pasting it in every page of the system.
Viewing the editTable.php Program
The editTable.php follows a familiar pattern. It has a small amount of PHP code,
but most of the real work is sent off to a library function. This module’s main job
is to check for an administrative password. If the user does not have the appro-
priate password, further access to the system is blocked. If the user does have the
correct password, the very powerful
tToEdit() function provides access to the
add, edit, and delete functions.
<html>
<head>
<title>Edit table</title>
</head>
<body>
<h2>Edit Table</h2>
<?
include “spyLib.php”;
//check password
if ($pwd == $adminPassword){
$dbConn = connectToSpy();
print tToEdit(“$tableName”);
} else {
print “<h3>You must have administrative access to proceed</h3>\n”;
} // end if
print mainButton();
?>
</body>
</html>
The $pwd value comes from a field in the spyMaster.php page. The $adminPassword
value is stored in spyLibrary.php. (The default admin password is
absolute,
but
you can change it to whatever you want by editing
spyLib.php.)
401
C
h
a
p
t
e
r 1
2B
u
i
l
d
i
n
g
a
T
h
r
e
e
-T
i
e
r
e
d
D
a
t
a
A
p
p
l
i
c
a
t
i
o
n
Viewing the editRecord.php Program
The editRecord.php program is called from a form generated by editTable.php.
(Actually, the
tToEdit() function generates the form, but tToEdit() is called from
editTable.php.) This program expects variables called $tableName, $keyName, and
$keyVal. These variables, automatically provided by tToEdit(), help editRecord
build a query that returns whatever record the user selects. (You can read ahead
to the description of
tToEdit() for details on how this works.)
<html>
<head>
<title>Edit Record</title>
</head>
<body>
<h1>Edit Record</h1>
<?
// expects $tableName, $keyName, $keyVal
include “spyLib.php”;
$dbConn = connectToSpy();
$query = “SELECT * FROM $tableName WHERE $keyName = $keyVal”;
print smartRToEdit($query);
print mainButton();
?>
</body>
</html>
The editRecord.php program prints the results of the smartRToEdit() library func-
tion. This function takes the single-record query and prints HTML code that lets
the user appropriately update the record.
Viewing the updateRecord.php Program
The smartRToEdit() function calls another PHP program called updateRecord.php.
This program calls a library function that actually commits the user’s changes to
the database.
402
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
