C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 20

• Matthew P. Johnson
• Stern School of Business, NYU
• Spring, 2004

2
Agenda

• Previously PL/SQL
• Next
• Project part 3 really due now
• Bad date
• Project part 4 due next week
• Tuesday
• Scripting for SQL on the web
• CGI/Perl
• PHP
• Security

3
New topic web apps

• Goal web front-end to database
• Present dynamic content, on demand
• Not canned (static) pages/not canned queries
• (perhaps) modify DB on demand
• Naïve soln static webpage HTTP
• index.html written, stored, put on server,
  displayed when its url is requested
• HTTP is stateless (so?)
• This doesnt solve our problem

4
Dynamic webpages

• Soln 1 upon url request
• somehow decide to dynamically generate an html
  page (from scratch)
• send back new html page to user
• No html file exists on server, just created on
  demand
• CGI, Java servlets, etc.

5
New topic CGI

• First, and still very popular, mechanism for
  first soln
• CGI Common Gateway Interface
• Not a programming language!
• Just an interface (connection) between the
  webserver and a program
• Very simple basic idea user chooses an url ?
• webserver runs that urls program, sends back
  the programs output

6
On-the-fly content with CGI

• Image from http//www.scit.wlv.ac.uk/jphb/cp3024/

7
Using CGI

• CGI works with any prog./scripting lang.
• Really?
• Well, any language your server works with
• I.e., the machine running your webserver program
• pages/soho, not sales
• And that the user the webserver is running as
  (e.g. nobody) can use and has env. vars. for
• And whose jars/libaries are available
• and whose permissions are set
• And (for us) whose MySQL dependencies are
  installed
• Plausible choices Perl, Python, C

8
CGI admin

• Most webservers CGI program/script must either
• End in .cgi or
• Reside in cgi-bin
• Ours needs .cgi extention
• If a program, the cgi file is just the name of
  the executable

gcc -o myprog.cgi myproc.gcc
9
CGI admin

• If a script, first (shebang) line says which
  interpreter to use
• Either way, cgi file must be executable
• Make sure your cgi file runs at cmd prompt
• But not a guarantee!

!/usr/local/bin/perl
sales chmod x .cgi
sales myprog.cgi
10
CGI input

• CGI programs must respond to input
• Two mechanisms
• GET read env. var. QUERY_STRING
• POST get length from env. var. CONTENT_LENGTH
  read from STDIN
• This diff. mostly invis. to Perl, PHP
• Both send a sequence of name/value pairs,
  separated by s

nameasubmitSearch
11
CGI input

• Appearance/security differences
• GET string is part of the URL, following a ?
• POST string can be read by program from an
  environmental variable
• Vars not visible to the browser user
• Not automatically put in server log, etc.

http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
kup.cgi?name1submitSearch
12
Our use of CGI

• Well discuss CGI and Perl
• One option for your project
• Can try C, C, etc.
• But not recommended!
• For CGI, only Perl will be supported
• Scripting languages v. programming languages
• Development v. IT
• Other languages are still not recommended
  especially if you dont know Perl and PHP

13
New topic Just Enough Perl

• Very popular, powerful scripting language
• Very good at regular expressions, text
  manipulation, but not very relevant to us
• Instead
• simple text/html production
• Basic language constructs
• MySQL connectivity
• Perl Practical Extraction and Report Language
  or
• Pathologically Eclectic Rubbish Lister

perl -pi -e 's/tcsh/sh/' HOME/.login
See http//perl.org.il/pipermail/perl/2003-Februar
y/001047.html
14
hello.pl

• Hello, World - hello.pl
• Running at command prompt

! /usr/bin/perl -w print "Hello World\n"
sales perl hello.pl Hello World sales
15
Hello, World - hello.pl

• Run from browser
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo.pl
• Whats wrong?
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo.cgi
• Whats wrong?
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo2.cgi
• Whats wrong?

16
Hello, World hello3.cgi

• Script errors, w/ and w/o fatalsToBrowser
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo3.cgi

! /usr/bin/perl -w use CGI qw(standard) use
CGICarp qw( fatalsToBrowser warningsToBrowser
) print header() pr int "Hello World\n"
17
More on Perl

• Perl is mostly C-like
• Perl is case-sensitive
• Use for rest-of-line comments
• Creation of functions are supported but optional
• Perl has modules/packages
• CGI module
• Provides header() function, access to params
• Mysql module

use CGI qw(standard)
use Mysql
18
Perl and strings

• Can use for strings
• Concatenate with . op
• Print text with print function
• Or, parentheses can be dropped!

Hi . there\n
print (Hi there)
print Hi there
19
Perl and strings

• Can compare numbers (as numbers) with usual
  operators
• lt gt lt, etc.
• 3 lt 5
• These do not apply to strings
• String ops are based on initials of operations
• eq, ne, lt, gt, le, ge
• hi ne there
• hi le hi there

20
Perl and variables

• Regular variables begin with
• input, query
• Declare vars with my
• Q What about var types?
• A Perl is loosely typed!

my s hi my query select
my s hi s 10 s 3.5
21
Perl, strings, and variables

• print takes var-many arguments
• Variables are always escaped
• Vars may appear within strings
• Prints out Hello Dolly.
• To prevent, use single quotes

print (Hello , Dolly. .\n)
name Dolly
print (Hello name.\n)
22
Perl syntax examples

• Access member/field of object
• objectmember
• Access member pointed to by object -gt
• rowhash-gtfield
• Can access array members with indices
• Can access hash members with strings
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/con
  trolscgi.txt

23
Tutorials on Perl

• Some material drawn from the following good
  tutorials
• http//perl.com
• CGI backend programming using perl
• http//www.scit.wlv.ac.uk/jphb/sst/perl/
• Perl Basics
• http//www.cs.wcupa.edu/rkline/csc417/perl-basics
  -1.html
• CGI Basics
• http//www.cs.wcupa.edu/rkline/csc417/cgi-basics-
  1.html
• MySQL/Perl/CGI example
• http//www.scit.wlv.ac.uk/jphb/sst/perl/ex3d.html
  

24
Tutorials on PHP

• Some material drawn from the following good
  tutorials
• http//php.net
• PHP introduction and examples
• http//www.scit.wlv.ac.uk/jphb/sst/php/
• Interactive PHP with database access
• http//www.scit.wlv.ac.uk/jphb/sst/php/gazdb.html
  
• Longer PHP/MySQL Tutorial from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index2a.
  html
• Nice insert/update/delete example from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index3a.
  html
• MySQL/Perl/PHP page from U-Wash
• http//www.washington.edu/computing/web/publishing
  /mysql-script.html

25
Comparison of scripting languages

• PHP v. Perl
• http//php.weblogs.com/php_versus_perl
• PHP v. Perl v. Java servlets v.
• http//www.developerspot.com/tutorials/php/server-
  side-scripting-language/