CSE 154 Extra Sessions

Lecture 2: Advanced MySQL, Validation, and URL Rewriring

Reading: (none)

Except where otherwise noted, the contents of this document are Copyright 2012–2013 Morgan Doocy. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Creating a Table: CREATE TABLE

CREATE TABLE blackbook (
	id int(11) unsigned NOT NULL AUTO_INCREMENT,
	first_name varchar(100) NOT NULL,
	last_name varchar(100) DEFAULT NULL,
	phone varchar(10) DEFAULT NULL,
	owes_money enum('yes','no') NOT NULL DEFAULT 'yes',
	PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

• Default values can be specified for each field.

  • Specifies the value to be entered if none is specified in the INSERT command.

• Some basic constraints can be placed:

  • NOT NULL means a value must be entered.
    • (But can get around this with DEFAULT '', for example)
  • AUTO_INCREMENT means the value defaults to the next-highest number.

Creating New Records: INSERT

INSERT INTO `tablename` (col1name, col2name, ...) VALUES (
	value1,
	value2,
	...
);

• If you don’t specify list of columns, you must give a value for each field.

• Can also INSERT results from a SELECT query:

  INSERT INTO `table1name` (columns)
  SELECT fields FROM table2name WHERE ...;

Updating Existing Records: UPDATE

UPDATE tablename SET
field1name = field1value,
field2name = field2value,
...
WHERE condition;

UPDATE blackbook SET
owes_money = 'no'
WHERE first_name = 'Joey';

• Updates all records matched by the WHERE clause.

• Warning: Might match more than one row!

  • If you want to update a single row, select by ID in the WHERE rather than using other, more ambiguous fields.
• Use caution: there is no undo!

Deleting Records: DELETE

DELETE FROM tablename WHERE conditions...;

DELETE FROM blackbook
WHERE first_name = "Jack 'The Hat'"
AND last_name = "McVitie";
-- he's been "taken care of"

• Updates all records matched by the WHERE clause.

• Warning: Might match more than one row!

  • If you want to delete a single row, select by ID in the WHERE rather than using other, more ambiguous fields.
• Use caution: there is no undo!

Using alternative delimiters

if (preg_match('/http:\/\//', $str)) { ... }
if (preg_match('|http://|', $str)) { ... }

• The default delimiter for a regular expression is the slash: /

  • Goes at beginning and end to indicate where the regular expression starts and ends.
• Slashes inside the pattern need to be backslashed: /https?:\/\/[\w\.]+\//
• To avoid excessive backslashing, alternative delimiters can be used:
  • |...|, ~...~, #...#, @...@, !...!
• Can even use open/close pairs (uncommon):
  • {...}, [...], (...), <...>
• Pick a delimiter that you don’t need to use inside!

Capturing text with ()

$str = '<title lang="en">Pride and Prejudice and Zombies</title>';
if (preg_match("!<([^\s>]+)[^>]+>([^<]+)</\\g{1}>!", $str, $captures)) {
	list($entire, $element, $content) = $captures;
	print "The $element of this book is: '$content'";
	// "The title of this book is: 'Pride and Prejudice and Zombies'"
}

• Anything parenthesized in a regular expression is called a capture group
• preg_match will put captured text into optional third parameter
  • $captures[0] contains the entire matched regular expression
  • $captures[1] contains the first parenthesized group
  • $captures[2] contains the second parenthesized group, and so on
• within the regular expression, \g{n} refers back to the nth capture
  • the above looks for a closing tag with the same name as the opening tag
  • (must be double-backslashed inside the string)

Capturing multiple matches: preg_match_all

$str = "<10><20><30><40>";
if (preg_match_all("/<(\d\d)>/", $str, $occurrences)) {
	$n = 1;
	foreach ($occurrences as $captures) {
		list($entire, $number) = $captures;
		print "number $n is: $number\n";
		$i++;
	}
	// number 1 is: 10\n
	// number 2 is: 20\n
	// ...
}

• Applies the regular expression repeatedly, as many times as possible
• Required third parameter is a two-dimensional array:
  • $occurrences[0] is an array of all parenthesized captures from the first time the pattern applied
  • $occurrences[1] is an array of all parenthesized captures from the second time the pattern applied, and so on

Regular expressions in HTML forms

How old are you?
<input type="text" name="age" size="2" pattern="[0-9]+" title="an integer" />
<input type="submit" />

• HTML5 adds a new pattern attribute to input elements
• the browser will refuse to submit the form unless the value matches the regex

Recall: A Basic URL

http://www.example.com/foo/bar.php
~~~~   ~~~~~~~~~~~~~~~ ~~~~~~~~~~~
protocol    host          path

• Specifies a document to retrieve (or program to execute) on a remote server.
• Server looks in the specified directory for the specified file, and returns it.
• If the file or directory don’t exist, the server returns a 404 Not Found error.

Limitations of “Real” URLs

• A physical file with that name, in that directory, must exist on the server.

• What if we instead use a PHP program to fetche and display content dynamically?

  • Example: Blogging tool / Content Management System

• To display dynamic content, we need to pass a query parameter. Example:

  http://blog.example.com/entries.php?entryid=12345678

  (PHP script which fetches and displays the single blog entry whose ID is passed as a parameter.)

• Query strings are ugly. Search engines also sometimes ignore query strings, grouping links by filename instead.

  • Meaning: A search engine might think think the unique content of entry 12345678 lives at entries.php instead of at entries.php?entryid=12345678.
• We want each entry be considered its own page, but we don’t want a physical file for each entry.

Solution: “Virtual” URLs

• Goal: Translate the requested “virtual” URL into an internal “real” URL
• Often we’ll extract portions of the external URL to pass as parameters to a script internally

• Example:

  • User makes request to “external” URL:

    http://blog.example.com/entries/12345678

  • We translate this into the “internal” URL:

    http://blog.example.com/entries.php?entryid=12345678

• Called “URL Rewriting”
• Most web servers support this functionality; we’ll use Apache’s mod_rewrite to do this

URL rewriting with mod_rewrite

# Turn on URL rewriting
RewriteEngine on

# Change requests for 'foo.html' to 'bar.html'
RewriteRule foo\.html bar.html

• This example “rewrites” requests for foo.html to bar.html internally.
• The URL in the browser will still be foo.html — but the file sent to the browser will be bar.html!

• All mod_rewrite directives go in a special file called .htaccess inside the directory you want to rewrite URLs in.

  • Most operating systems consider this a “hidden” file, since it begins with a . (also called a “dot-file”)
  • You may need to configure your desktop, text editor, and SFTP program to show hidden files!

The RewriteRule directive

Rewrites any URLs that match a regular expression pattern.

RewriteRule pattern replacement [flags]

RewriteRule fakename\.html realdir/realname.html
RewriteRule movedfile\.php http://www.anotherserver.com/movedfile.php [QSA,R=301]
RewriteRule stillworks\.html - [L] # don't show maintenance page
RewriteRule .+\.(html|php) maintenance.html # down for maintenance!

• pattern is a Perl-compatible regular expression

  • Just like in PHP and JavaScript, except without the /…/ delimiters
  • Remember to escape special characters like . ? * +

• replacement is the replacement URL

  • Can be relative or absolute, on this or another server
  • Special replacement of - means “no change”
• flags can specify additional options

Capturing and anchoring in RewriteRules

• Without anchors, a pattern may match unintended requests:

  RewriteRule abc\.html def.html
  # matches: 'abc.html', 'foo/bar/defabc.html', 'BLAHBLAHabc.htmlBLAHBLAH'
  RewriteRule abc\.html$ def.html
  # matches: 'abc.html', 'foo/bar/defabc.html'
  RewriteRule ^abc\.html$ def.html
  # matches: 'abc.html'

• Parenthesized groups can be used for capturing. $n can be used to inject the nth parenthesized capture into the replacement:

  RewriteRule ^entries/(\d+)/?$ entries.php?entryid=$1
  # 'entries/12345678/' → 'entry.php?entryid=12345678'
  RewriteRule ^users/(\w+)/.*(\w+\.(m4a|mov|wmv|webm))$ http://media.example.com/$1/$2
  # 'users/mdoocy/foo/bar/lecture.m4a' → 'http://media.example.com/mdoocy/lecture.m4a'

.htaccess file precedence and URL matching

• Suppose we have two .htaccess files in www.example.com:

  /.htaccess
  /one/two/.htaccess

• Then suppose we make the following request:

  http://www.example.com/one/two/foo/bar?baz=qux

• Our .htaccess files will be loaded outermost to innermost, with RewriteRules matched against the portion of the URL after the current directory:

  1. /.htaccess first, matching against one/two/foo/bar.
  2. /one/two/.htaccess second, matching against foo/bar.
• Notice that the ?baz=qux query string is not part of the matched string! (It will still be appended to result, however.)

RewriteRule chaining and options

RewriteRule ^foo$ abc/foo [S=1]
RewriteRule ^abc/(\w+)$ def/$1 [L]
RewriteRule ^(\w+)/(foo|bar)$ script.php?$2=$1 [QSA]

Multiple rules may apply in sequence, with the output of one feeding the input of another. [S=n] can be used to skip n rules

• [L] means “Last”; evaluation will stop after this rule if it applies.
• [R] or [R=3xx] means “Redirect”; will issue an external redirect, optionally with the HTTP redirect code specified.
• [NC] means “No Case”; will be evaluated case-insensitively.
• [QSA] or [R=3xx] means “Query String Append.” If your replacement has a query string, normally any query string in the requested URL will be dropped. With QSA, it will be tacked on to the replacement.

The RewriteCond directive

Specifies the conditions under which subsequent RewriteRules will be evaluated.

RewriteCond condition [flags]

RewriteCond %{HTTP_HOST} ^example.com$ [NC]
RewriteRule ^(.*)$ http://www.example.com/$1 [R=301,L]

• Like an if statement: if it fails, the RewriteRules after it (until the next RewriteCond statement) won’t be evaluated.
• Other common conditions to evaluate: %{HTTP_USER_AGENT}, %{QUERY_STRING}, %{REQUEST_URI}, %{REQUEST_FILENAME}, %{REQUEST_METHOD}
• Options: use [OR] to create a logical OR with the next RewriteCond
• Special match values: -f “exists and is a file”; -d “exists and is a directory”