C20.0046: Database Management Systems Lecture #20

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

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

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 it’s url is requested • HTTP is stateless (so?) • This doesn’t solve our problem M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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 url’s program, sends back the program’s output M.P. Johnson, DBMS, Stern/NYU, Sp2004

6. Client Program HTTP Request Data for program HTML Generated HTML Server On-the-fly content with CGI • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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

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

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: name=a&submit=Search M.P. Johnson, DBMS, Stern/NYU, Sp2004

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/lookup.cgi?name=1&submit=Search M.P. Johnson, DBMS, Stern/NYU, Sp2004

12. Our use of CGI • We’ll 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 don’t know Perl and PHP M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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

15. Hello, World - hello.pl • Run from browser: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello.pl • What’s wrong? • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello.cgi • What’s wrong? • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello2.cgi • What’s wrong? M.P. Johnson, DBMS, Stern/NYU, Sp2004

16. Hello, World – hello3.cgi • Script errors, w/ and w/o fatalsToBrowser: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello3.cgi #! /usr/bin/perl -w use CGI qw(:standard); use CGI::Carp qw( fatalsToBrowser warningsToBrowser ); print header(); pr int "Hello World\n"; M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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

19. Perl and strings • Can compare numbers (as numbers) with usual operators • < > <=, etc. • 3 < 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” M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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

22. Perl syntax examples • Access member/field of object :: • object::member • Access member pointed to by object -> • rowhash->field • Can access array members with indices • Can access hash members with strings • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/controlscgi.txt M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

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

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