A few programming examples
Read in Elmasri & Navathe (EN)
- Chapter 15: the standard normalizations
- Chapter 16: more about
http://localhost/company/employee.php (only on my machine!)
1. The PHP language: very handy for mixing code and large blocks of html.
The purpose of a server-side PHP program is to generate the html (as its
standard output) that will be sent to the user's browser.
2. Note there is no authentication.
3. The deRalph button: this executes the following MySQL
(stored in employee.php):
delete from employee;
insert into employee select * from employeebackup;
If the table employeebackup did not exist, this would have the effect of
deleting everything in table employee.
4. Drop-down menus as a way of enforcing foreign-key constraints
5. How about inserting a user "ralph j <script>". What happens? (If
necessary, I may need to edit /var/www/html/company/lib353pdo.php)
This represents "html poisoning". The problem is that when the server sends
the data to the browser, with "<script>" in the lname field of the
html table, the browser interprets <script> in a special
way, and stops displaying further data. The browser is in fact interpreting
syntactically incorrect, but silently. If we had room in the text field, we
This can be fixed by applying the PHP htmlspecialchars() function to all
output before printing. In my lib353pdo.php file, output in the function
table_format_pdo that prints a MySQL table is run through htmlsc($str). This
can either do nothing, or call htmlspecialchars($str). The latter is safer!
Generally speaking, SQL injection attacks (below) are prevented by using
prepared statements, and html-based attacks are prevented through careful
use of htmlspecialchars. It is not clear, however, if either technique is a
complete panacea, alone.
Programmers who write applications that connect to databases generally use prepared
statements, in which queries are transmitted to the database in
the form of a base query with parameters, and a list of parameter values.
The parameter locations in the base query are replaced by a placeholder,
most often "?". Below is a Java/JDBC example:
PreparedStatement p = connection.prepareStatement(
e.lname, e.ssn, e.bdate, e.salary, d.dname from employee e, department d "
+ "where e.dno=d.dnumber and
e.dno=? and e.lname=?"
Prepared statements, or parameterized statements/queries, can be either client-side or server-side.
Client-side means that the client replaced the prepared-statement '?'
placeholders with appropriately escaped strings, and then passed the entire
query to the database. The client must be sure to handle the escaping of
embedded quotes correctly or risk a SQL injection attack. Server-side
prepared statements mean that the query with embedded '?'s is sent to the
database server, and then later the values to be plugged in are sent.
Ideally, the value strings are sent as an array of raw string objects, so
there is no ambiguity as to how escaping of quotation marks is to work.
To see some of this in action, turn on the general_log_file in the MySQL
configuration file (/etc/mysql/my.cnf for me), and enable query logging
general_log_file = /var/log/mysql/mysql.log
After doing this, let's run employee2.java again, with e.lname of "O'Hara".
We get (or got; this is actually
from a while back) something like
52 Query SHOW COLLATION
52 Query SET NAMES latin1
52 Query SET character_set_results = NULL
52 Query SET autocommit=1
52 Query SET sql_mode='STRICT_TRANS_TABLES'
52 Query select e.fname, e.lname, e.ssn, e.bdate,
e.salary, d.dname from employee e, department d where e.dno=d.dnumber and
e.dno=5 and e.lname='O\'Hara'
Note that the escaped value for e.lname does show up here, 'O\'Hara', but
that was done by the client, that
is, by the JDBC connection. The last line above is also what appears when we
print the preparedStatement object p after the final p.setString(),
suggesting that at this point (before query execution) java has already
replaced the '?' with its corresponding argument.
By comparison, here's the same sort of thing after using MDB2 PHP
preparedStatements, which use server-side
prepares (I've omitted a few unnecessary items)
56 Query PREPARE
MDB2_STATEMENT 'select e.fname, e.lname, e.ssn, e.bdate, e.salary, d.dname
from employee e, department d where e.dno=d.dnumber and e.dno=? and
56 Query SET @0 = 5
56 Query SET @1 = 'O\'Hara'
56 Query EXECUTE MDB2_STATEMENT USING @0, @1
This time it is the server doing the escaping where 'O\'Hara' is displayed
(although that isn't completely obvious).
This isn't what PDO uses, however. The PDO library has an
option, PDO::ATTR_EMULATE_PREPARES, normally true (meaning client-side
emulation of prepared statements is done). When this is true,
does nothing; the following is the result of the
execute($queryargs). There is no mention of a
prepare() being invoked by MySQL (because it is not!).
47 Query insert into
employee values ('ralph','j','wiggums','abcdefghi','1980-07-04','no fixed
Here is the MySQL log result of an employee.php insert with
PDO::ATTR_EMULATE_PREPARES set to false.
48 Prepare insert into
employee values (?,?,?,?,?,?,?,?,?,?)
48 Execute insert into employee values
48 Close stmt
I am not sure why the "48 Execute" above shows the quoted/escaped values,
except that the log is from MySQL itself and may be showing the completed
public at grik dot net
With PDO_MYSQL you
need to remember about the PDO::ATTR_EMULATE_PREPARES option.
The default value is TRUE, like
This means that no prepared statement is created with
$dbh->prepare() call. With exec() call PDO replaces the
placeholders with values itself and sends MySQL a generic query
The first consequence is that the call
reports no error. You will get an SQL error during the
The second one is the SQL injection risk in special cases, like
using a placeholder for the table name.
The reason for emulation is a poor performance of MySQL with
prepared statements. Emulation works significantly faster.
I became suspicious when I added better error checking to employees.php, and
discovered that misspelled table names did not
cause errors in $db->prepare($query).
Server-side preparation can infer the type of any column value from the
appropriate table definition. Client-side preparation must do a little more
Why use prepared statements? Why not just have the client build SQL strings
manually, eg with
query = "select e.fname, e.lname, e.salary from employee
e where e.ssn = " + theSSN;
A problem occurs if there are quotes in the data, particularly if that data
is user-supplied. Suppose the user provides a USERNAME and a PASSWORD
string, and the query does
query = "select * from AUTH_USERS where user='" +
USERNAME + '\'';
Suppose the user submits the following as the value of USERNAME:
Then the above may be naively formatted into
select * from AUTH_USERS where user='x' or '1'='1'
(note two extra quotes!)
This is now everyone.