c20.0046: database management systems lecture #20
DESCRIPTION
C20.0046: Database Management Systems Lecture #20. Matthew P. Johnson Stern School of Business, NYU Spring, 2004. 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. - PowerPoint PPT PresentationTRANSCRIPT
M.P. Johnson, DBMS, Stern/NYU, Sp2004
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
1. somehow decide to dynamically generate an html page (from scratch)
2. 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
On-the-fly content with CGI
Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/
ProgramClient
Server
HTTP Request
Data for program
Generated HTML
HTML
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
either1. End in .cgi or
2. 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.gccgcc -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#!/usr/local/bin/perl
sales% chmod +x *.cgisales% chmod +x *.cgi
sales% myprog.cgisales% 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=Searchname=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
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/.loginperl -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";
#! /usr/bin/perl -w
print "Hello World\n";
sales% perl hello.plHello Worldsales%
sales% perl hello.plHello Worldsales%
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";
#! /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 CGI qw(:standard);
use Mysql;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”“Hi ” . “there\n”
print (“Hi there”);print (“Hi there”);
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”;my $query = “select …”;
my $s = “hi”;$s = 10;$s = 3.5;
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 ‘ ‘
$name = “Dolly”;$name = “Dolly”;
print (“Hello $name.\n”);print (“Hello $name.\n”);
print (“Hello ”, “Dolly”. “.\n”);print (“Hello ”, “Dolly”. “.\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/