A few programming examples

Read in Elmasri & Navathe (EN)


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):

set foreign_key_checks=0;   
delete from employee;   
insert into employee select * from employeebackup;   
set foreign_key_checks=1;   

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 the rest of the data as javascript; it ends up being ignored as syntactically incorrect, but silently. If we had room in the text field, we could serve up a javascript attack this way.

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.


Prepared Statements

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(
            "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 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
    general_log          = 1

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 e.lname=?'
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, $db->prepare($insertion) does nothing; the following is the result of the prepare($insertion) and 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 abode','M','9999','999887777','1')

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 ('ralph','j','wiggums','abcdefghi','1980-07-04','no fixed abode','M','9999','999887777','1')
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 query.

From http://php.net/manual/en/pdo.prepare.php:

public at grik dot net 07-Mar-2012 12:23
With PDO_MYSQL you need to remember about the PDO::ATTR_EMULATE_PREPARES option.

The default value is TRUE, like
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

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 string.

The first consequence is that the call  $dbh->prepare('garbage');
reports no error. You will get an SQL error during the $dbh->exec() call.
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 work.

SQL Injection

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:

x' or '1'='1

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.

bobby tables

Also, here is the government record of a UK business that closed in 2020: find-and-update.company-information.service.gov.uk/company/10542519. This was a legitimate consulting business.

The UK government says the name of the company is "available on request", but we can find it by clicking "Filing History" -> "View pdf" (the 2020 one will do). The name is

; DROP TABLE "COMPANIES";-- LTD