C20.0046: Database Management Systems Lecture #21

1. C20.0046: Database Management SystemsLecture #21 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 M.P. Johnson, DBMS, Stern/NYU, Sp2004

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? M.P. Johnson, DBMS, Stern/NYU, Sp2004

3. HTTP Request Data for program HTML Generated HTML Review: CGI Client Program Server • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 CGI::header(); M.P. Johnson, DBMS, Stern/NYU, Sp2004

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/cgipm.cgi • Somewhat simpler, but another thing to learn • Mostly won’t cover Review: Hello, World M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

7. Form example (visible?) On clicking Send, we go to script.php with “foo=bar” From http://www.zend.com/zend/art/art-sweat4.php • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/input.cgi M.P. Johnson, DBMS, Stern/NYU, Sp2004

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/inputcgi.txt • Improve: also print, say, double the input my $cgi = CGI->new(); $param = $cgi->param('number'); M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

10. Perl and databases • DB connectivity is done through DBI • Database Interface • Analogous to Java’s JDBC • Think of DBI as a Java class with static methods • Use these to obtain a connection, prepare and execute queries, etc. M.P. Johnson, DBMS, Stern/NYU, Sp2004

11. Perl DBI • Open a connection: • Prepare and execute query: my $dbh = DBI-> connect("dbi:mysql:database=test;mysql2.stern.nyu.edu;port=3306", user, pass); my $sth = $dbh->prepare($query); $sth->execute; M.P. Johnson, DBMS, Stern/NYU, Sp2004

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->fetchrow_array() while (my ($a, $b) = $sth->fetchrow_array()) { # print out $a and $b } M.P. Johnson, DBMS, Stern/NYU, Sp2004

13. Limit: Perl webpages that do something • Semi-interesting Perl script: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/lookup.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! M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 page’s url • (Try to) read input parameter • Open MySQL connection • Run query • Print results in a table • Disconnect from MySQL M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

17. That’s all, folks! • Q: Is this enough to get a job coding Perl? • A: Probably not! • But: • Don’t like Perl/CGI? • Don’t want to run start a process for every user of your site? • Next we’ll do PHP… a couple modified copies of lookup.cgi and cia.cgi + some HTML  fairly interesting site M.P. Johnson, DBMS, Stern/NYU, Sp2004

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. M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

20. hello.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/hello.php • Q: What the difference between <br> and \n? <html> <head><title>Hello from PHP</title></head> <body> Here is the PHP part:<BR><BR> <?php print "Hello, World!<br>\n“; ?> <br>That's it! </body></html> M.P. Johnson, DBMS, Stern/NYU, Sp2004

21. hello2.php • Script errors, w/ and w/o display_errors on: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello2.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/hello2.php • Local dir must contain .htaccess: • Automatically load GET/POST params as vars • http://pages.stern.nyu.edu/~mjohnson/dbms/php/.htaccess php_flag display_errors on php_flag register_globals on M.P. Johnson, DBMS, Stern/NYU, Sp2004

22. More on PHP • Somewhat C-like, somewhat Perl-like • Case-sensitive • Comments: • # Unix shell-style • /* */ C-style • // C++-style • Output: • echo(“hi there”); • C’s printf M.P. Johnson, DBMS, Stern/NYU, Sp2004

23. PHP vars • Similar to those of Perl • http://pages.stern.nyu.edu/~mjohnson/dbms/php/math.php <? $num1 = 58; $num2 = 67; print "First number " . $num1 . "<br>"; print "Second number " . $num2 . "<br>"; $total = $num1 + $num2; print "The sum is " . $total . "<br>"; ?> M.P. Johnson, DBMS, Stern/NYU, Sp2004

24. Combining PHP and HTML • http://pages.stern.nyu.edu/~mjohnson/dbms/php/combine.php <?php for($z=0;$z<=5;$z++) { ?> Iteration number <? = $z ?><br> <? } ?> M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 <? phpinfo(); ?> M.P. Johnson, DBMS, Stern/NYU, Sp2004

26. PHP & MySQL • Open a connection and open our DB: • Run query: $db = mysql_connect("mysql2.stern.nyu.edu:3306", user, pass); mysql_select_db("test", $db); $result = mysql_query($query,$db); M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 row’s data M.P. Johnson, DBMS, Stern/NYU, Sp2004

28. Limit: PHP webpages that do something • Semi-interesting Perl script: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookup.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! M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 page’s url • (Try to) read input parameter • Open MySQL connection • Run query • Print results in a table • Disconnect from MySQL M.P. Johnson, DBMS, Stern/NYU, Sp2004

30. Insert/delete Perl/PHP example • Similar to search example • NB: form has two buttons • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/update.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/updatecgi.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/update.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/php/updatephp.txt M.P. Johnson, DBMS, Stern/NYU, Sp2004

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.php.txt M.P. Johnson, DBMS, Stern/NYU, Sp2004

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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

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/ M.P. Johnson, DBMS, Stern/NYU, Sp2004

34. Advice for use of novel languages • Rerun often • don’t 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 doesn’t do what you want • change either/both, step by step, until they meet in the middle M.P. Johnson, DBMS, Stern/NYU, Sp2004

35. That’s 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. • Don’t like PHP either? • Lots of other choices, but again, you’re strongly discouraged from using something else for your project unless you know what you’re doing. M.P. Johnson, DBMS, Stern/NYU, Sp2004