C20.0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 21

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

2
Agenda

• Previously CGI, Perl
• Next
• Scripting for SQL on the web
• More Perl
• PHP
• Security
• Project part 4 due today
• Project part 5 assigned today
• Short hw?

3
Review CGI
Client
Program
Server

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

4
Perl and HTML headers

• Data sent to a browser is prefaced with a header
  describe type of data
• Hand-generated html must print this before
  anything else
• Or
• When use-ing CGI

Content-type text/html\n\n
print Content-type text/html\n\n
print CGIheader()
5
Perl, HTML, and CGI.pm

• CGI.pm offers a front-end to HTML
• Replaces mark-up language with an API
• Very simple example
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/cgi
  pm.cgi
• Somewhat simpler, but another thing to learn
• Mostly wont cover

Review Hello, World
6
New topic HTML forms

• Active parts of HTML forms
• Intuition for name paper form
• Fill in textboxes, check boxes or not, etc.
• Turn it in
• HTML form
• contains arb. of INPUTs
• Submits to somewhere (ACTION)
• By GET or POST

7
Form example (visible?)
On clicking Send, we go to script.php with
foobar
From http//www.zend.com/zend/art/art-sweat4.php

• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  ut.cgi

8
Perl and forms

• Obtain param number
• Goal display text and button
• On submit, tell user what was entered
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  utcgi.txt
• Improve also print, say, double the input

my cgi CGI-gtnew() param cgi-gtparam('number
')
9
Perl error-handling

• Many Perl scripts have lines of the form
• some-statement OR die(something happened)
• What this means
• die exits with error message
• Perl supports both and OR as or operator
• Perl supports boolean short-circuiting
• Boolean eval stops as fast as possible
• Ftns often return 0/null/false for errors
• ? if some-statement fails then we die

10
Perl and databases

• DB connectivity is done through DBI
• Database Interface
• Analogous to Javas JDBC
• Think of DBI as a Java class with static methods
• Use these to obtain a connection, prepare and
  execute queries, etc.

11
Perl DBI

• Open a connection
• Prepare and execute query

my dbh DBI-gt connect("dbimysqldatabasetestm
ysql2.stern.nyu.eduport3306", user, pass)
my sth dbh-gtprepare(query) sth-gtexecute
12
Perl DBI

• Extract next row of data from statement results,
  if available
• What this means row has two fields, whose values
  are put in a and b, in order
• Other options, but this should suffice
• In general, want to scroll through results
• Braces are required!

my (a, b) sth-gtfetchrow_array()
while (my (a, b) sth-gtfetchrow_array())
print out a and b
13
Limit Perl webpages that do something

• Semi-interesting Perl script
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kup.cgi
• Non-trivial but not huge 40 lines
• Works with two-column (a,b) table
• Takes input from user
• Returns rows whose a field contains value
• If no/empty input, returns all rows
• Bad idea in general!

14
lookup.cgi

• Two possible situations for running script
• Page opened for the first time
• User entered parameter and pressed button
• Structure of file
• Print input box and button for next search
• On button click, parameter is sent to this pages
  url
• (Try to) read input parameter
• Open MySQL connection
• Run query
• Print results in a table
• Disconnect from MySQL

15
Higher-level structure

• As one page
• If we have params, display data based on them
• Otherwise, prompt user for params, call self
• Could be
• Page 1 prompt for params, call page 2
• Page 2 display data based on params
• In e.g. always display data for convenience

16
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
  

17
Thats all, folks!

• Q Is this enough to get a job coding Perl?
• A Probably not!
• But
• Dont like Perl/CGI?
• Dont want to run start a process for every user
  of your site?
• Next well do PHP

a couple modified copies of lookup.cgi and
cia.cgi some HTML ? fairly interesting site
18
Dynamic webpages

• Original prob need webpages to respond to user
  inputs
• Soln 2
• create a an html file embedded with special
  non-html code
• upon url request, execute embedded code to
  generate more html
• Send back the modified html page to user
• An incomplete html page exists on server
• PHP, JSPs, ASPs, etc.

19
New topic PHP

• First option for each request run program,
  produce whole page, send back
• CGI and some host language
• Second option create html page with missing
  parts for each response, fill in the wholes and
  send back
• Embedded scripting
• PHP and others
• PHP Personal Home Page or
• PHP Hypertext Processor

20
hello.php

• http//pages.stern.nyu.edu/mjohnson/dbms/php/hell
  o.php
• Q What the difference between ltbrgt and \n?

lthtmlgt ltheadgtlttitlegtHello from PHPlt/titlegtlt/headgt
ltbodygt Here is the PHP partltBRgtltBRgt lt?php print
"Hello, World!ltbrgt\n ?gt ltbrgtThat's
it! lt/bodygtlt/htmlgt
21
hello2.php

• Script errors, w/ and w/o display_errors on
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo2.php
• http//pages.stern.nyu.edu/mjohnson/dbms/php/hell
  o2.php
• Local dir must contain .htaccess
• Automatically load GET/POST params as vars
• http//pages.stern.nyu.edu/mjohnson/dbms/php/.hta
  ccess

php_flag display_errors on php_flag
register_globals on
22
More on PHP

• Somewhat C-like, somewhat Perl-like
• Case-sensitive
• Comments
• Unix shell-style
• / / C-style
• // C-style
• Output
• echo(hi there)
• Cs printf

23
PHP vars

• Similar to those of Perl
• http//pages.stern.nyu.edu/mjohnson/dbms/php/math
  .php

lt? num1 58 num2 67
print "First number " . num1 . "ltbrgt"
print "Second number " . num2 . "ltbrgt"
total num1 num2 print "The sum is
" . total . "ltbrgt" ?gt
24
Combining PHP and HTML

• http//pages.stern.nyu.edu/mjohnson/dbms/php/comb
  ine.php

lt?php for(z0zlt5z) ?gt
Iteration number lt? z ?gtltbrgt lt? ?gt
25
PHP info

• PHP does not have both string and number ops like
  Perl
• Number ops treat (number) strings as numbers,
  regular strings as strings
• http//pages.stern.nyu.edu/mjohnson/dbms/php/test
  .php
• Info function displays lots of PHP/HTML info
• http//pages.stern.nyu.edu/mjohnson/dbms/php/info
  .php

lt? phpinfo() ?gt
26
PHP MySQL

• Open a connection and open our DB
• Run query

db mysql_connect("mysql2.stern.nyu.edu3306",
user, pass) mysql_select_db("test", db)
result mysql_query(query,db)
27
PHP MySQL

• Extract next row of data from statement, if
  available
• What this means myrow is an array that can then
  be accessed
• Other options, but this should suffice
• In general, want to scroll through results

myrow mysql_fetch_row(result)
while (myrow mysql_fetch_row(result))
print rows data
28
Limit PHP webpages that do something

• Semi-interesting Perl script
• http//pages.stern.nyu.edu/mjohnson/dbms/php/look
  up.php
• Non-trivial but not huge 60 lines, but much
  plain html
• Works with two-column (a,b) table
• Takes input from user
• Returns rows whose a field contains value
• If no/empty input, returns all rows
• Bad idea in general!

29
lookup.php port of lookup.cgi

• Two possible situations for running script
• Page opened for the first time
• User entered parameter and pressed button
• Structure of file
• Print input box and button for next search
• On button click, parameter is sent to this pages
  url
• (Try to) read input parameter
• Open MySQL connection
• Run query
• Print results in a table
• Disconnect from MySQL

30
Insert/delete Perl/PHP example

• Similar to search example
• NB form has two buttons
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  ate.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  atecgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  ate.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/php/upda
  tephp.txt

31
Master-detail Perl/PHP example

• Idea display list of regions
• When region clicked on, display its countries
• Mechanism pass GET param in link, not with a
  FORM
• http//pages.stern.nyu.edu/mjohnson/websys/cia.pl
  
• http//pages.stern.nyu.edu/mjohnson/websys/cia.ph
  p.txt

32
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

33
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/

34
Advice for use of novel languages

• Rerun often
• dont wait until end to try
• Use frequent prints to be sure of var vals
• When stuck, picture continuum from your current
  program to some other program
• other prog. works but doesnt do what you want
• change either/both, step by step, until they meet
  in the middle

35
Thats really all, folks!

• Q Is this enough to get a job coding PHP?
• A Again, probably not.
• But again pretty easy to produce a
  semi-interested site with a few copies of
  lookup.php and cia.php.
• Dont like PHP either?
• Lots of other choices, but again, youre strongly
  discouraged from using something else for your
  project unless you know what youre doing.