C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 22

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

2
Agenda

• Previously Scripting
• Next Security
• Secrecy
• Integrity
• Availability
• Web issues
• Project part 4 due today (really!)
• Project part 5 is up
• gt1 multi-table query
• Cite (in app) any sources of data!

3
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
• Other program is often commented-out version

4
New topic Security issues

• Secrecy
• E.g. You can see only your own grades
• Integrity
• E.g. Only an instructor can assign grades, and
  only to his students
• Web issues
• E.g. injection attacks

5
Why security is hard

• Its a negative deliverable
• Its an asymmetric threat
• Its open-ended
• Tolstoy Happy families are all alike every
  unhappy family is unhappy in its own way.
• Analogs homeland, jails, debugging, proofing

6
Users may have privileges

• Possible privileges
• SELECT read access to all columns
• INSERT(col-name) can insert rows with
  non-default values in this column
• INSERT can insert rows with non-default values
  in all columns
• DELETE
• REFERENCES(col-name) can define foreign keys
  that refer to (or other constraints that mention)
  this column
• TRIGGER triggers can reference table
• EXECUTE can run function/SP

7
Granting privilegs

• One method of setting access levels
• Creator of table gets all privileges to it
• A privileged user can grant privileges to another
  user
• Possible objects tables, databases, functions,
  etc.
• ltDB-namegt. - all tables in DB

GRANT ALL ON tbl TO george_at_whitehouse.org
IDENTIFIED BY evil WITH GRANT OPTION
GRANT privileges ON object TO users ltWITH GRANT
OPTIONgt
8
Granting and revoking

• Privileged user has privileges
• Privileged-WGO user can grant them, w/wo GO
• Granter can revoke privileges or GO
• Revocation cascades by default
• To prevent, use RESTRICT (at end of cmd)
• If would cascade, command fails
• Can change owner

ALTER TABLE my-tbl OWNER TO new-owner
9
Granting and revoking

• What we giveth, we may taketh away
• mjohnson (effects?)
• george (effects?)
• mjohnson (effects?)

GRANT SELECT, INSERT ON my-table TO george WITH
GRANT OPTION
GRANT SELECT ON my-table TO laura
REVOKE SELECT ON my-table TO laura
10
Passwords

• DBMS recognizes your privileges because it
  recognizes you
• -how?
• Storing passwords in the DB is not safe
• Soln hashed or digested passwords
• One-way hash function
• computing f(x) is easy
• Computing f-1(y) is hard/impossible
• MD5, SHA, PRNGs

11
Role-based authorization

• In SQL-1999, privileges assigned with roles
• Not yet supported in MySql
• For example
• Student role
• Instructor role
• Admin role
• Each role gets to do same (sorts of) things
• Privileges assigned by assigning role to users

GRANT SELECT ON my-table TO employee
GRANT employee TO billg
12
Built-in accounts

• One other thing many DBMSs (and OSs for that
  matter) have built-in demo accounts by default
• Must opt out
• Oracle scott/tiger (open on sales)
• MySQL root/(blank) (closed on sales)
• http//lists.seifried.org/pipermail/security/2004-
  February/001782.html
• SQLServer sa/(blank/null)
• http//support.microsoft.com/default.aspx?scidkb
  EN-US313418

13
New topic Security on the web

• Authentication
• If the website user wants to pay with Georges
  credit card, how do we know its George?
• If the our website asks George for his credit
  card, how does he know its our site?
• man in the middle attack
• Secrecy
• When George enters his credit card, will an
  eavesdropper be able to see it?
• Protecting against user input
• Is it safe to use user input in our SQL query?

14
Authentication on the web

• Obvious soln passwords
• Whats the problem?
• Less obvious soln passwords encryption
• Traditional encryption symmetric / private
  key
• DES, AES fast solves problem?
• Newer kind asymmetric / public key
• RSA slow solves problem?
• Public key is published somewhere
• Private key is top secret

15
Encryption on the web

• Neither private- nor public-key solves the
  problem
• But together they do!
• SSL/SHTTP high-level gloss
• Amazon has a public-key certificate
• When you log in to Amazon, they
• Pick a random number
• Send you the encryption of it
• You can decrypt it with Amazons certificate
• Now, you both share a key and can encrypt
  passwords, credit cards, etc.

16
Encryption on the web

• Now George trusts that its really Amazon
• Assuming Amazons key is safe
• But What if, say, Dick guessed Georges
  password?
• Another way What if George claims Dick guessed
  his password?
• Soln digital certificates
• George encrypts his order with his private key
  (not a typo!)
• Amazon tries to decrypt the order with Georges
  public key
• If it works, then it must really have been
  George

Yes, yes
17
Security and CGI

• CGI has two parameter methods
• GET
• POST
• For secret information, GET is obviously insecure
• Displays in browser
• Written into server log
• Either way, data can still be sniffed
• Soln encryption

18
CGI security

• Imagine scenario
• Youre Amazon
• Allow look-up of book
• Allow putting book in cart
• A couple pages to pay
• We need to
• Charge price P at the end
• Display price P each on each page
• Dont want to do DB lookup of price for every
  single page
• One bad idea each page after first takes P as a
  get var from prior

19
CGI security

• Attack type in false data in GET request
• Very insecure!
• Soln 1 Use POST, not GET

http//amazon.com/cart.cgi?titleDatabase
Systemsprice.01
20
Send price, etc., by POST

• This is more secure
• Fewer users will know how to break POST than GET
• But some do!
• Attack hand-code the POST request

sales telnet amazon.com 80 POST
http//amazon.com/cart.cgi HTTP/1.0 Content-Type
application/x-www-form-urlencoded Content-Length
32 titleDatabase Systemsprice.01
21
Handed-written POST example

• POST version of my input page
• http//pages.stern.nyu.edu/mjohnson/dbms/php/post
  .php
• Not obvious to web user how to hand submit
• And get around any client-side validation
• But possible
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/postb
  yhand.txt

sales telnet pages.stern.nyu.edu 80 POST
http//pages.stern.nyu.edu/mjohnson/dbms/php/post
.php HTTP/1.0 Content-Type application/x-www-form
-urlencoded Content-Length 15 val6submitOK
22
Injection attacks

• Heres a situation
• Take user and password from user
• Look up user/pass
• If found, user gets in
• http//pages.stern.nyu.edu/mjohnson/dbms/php/logi
  n.php
• Is this safe?

SELECT FROM users WHERE useru AND password p
23
Injection attacks

• We expect to get input of something like
• user mjohnson
• pass abc
• ?

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user mjohnson AND
password abc
24
Injection attacks MySQL/Perl/PHP

• Consider another input
• user ' OR 11 OR user '
• pass ' OR 11 OR pass '
• ?

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user '' OR 11 OR
user '' AND password '' OR 11 OR pass
''
http//pages.stern.nyu.edu/mjohnson/dbms/php/logi
n.php http//pages.stern.nyu.edu/mjohnson/dbms/eg
/injection.txt
SELECT FROM users WHERE user '' OR 11 OR
user '' AND password '' OR 11 OR pass ''
25
Injection attacks MySQL/Perl/PHP

• Consider another input
• user your-boss' OR 11
• pass abc
• ?

SELECT FROM users WHERE user u AND password
p
http//pages.stern.nyu.edu/mjohnson/dbms/php/logi
n.php
SELECT FROM users WHERE user 'your-boss' OR
11 ' AND password 'abc'
SELECT FROM users WHERE user 'your-boss' OR
11 ' AND password 'abc'
26
Injection attacks MySQL/Perl/PHP

• Consider another input
• user your-boss
• pass ' OR 11 OR pass '
• ?

SELECT FROM users WHERE user u AND password
p
http//pages.stern.nyu.edu/mjohnson/dbms/php/logi
n.php
SELECT FROM users WHERE user 'your-boss' AND
password '' OR 11 OR pass ''
SELECT FROM users WHERE user 'your-boss' AND
password '' OR 11 OR pass ''
27
Multi-command injection attacks

• Consider another input
• user ' DELETE FROM users WHERE user 'abc'
  SELECT FROM users WHERE password '
• pass abc
• ?

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user '' DELETE FROM
users WHERE user 'abc' SELECT FROM users WHERE
password '' AND password 'abc'
SELECT FROM users WHERE user '' DELETE FROM
users WHERE user 'abc' SELECT FROM users WHERE
password '' AND password 'abc'
28
Multi-command injection attacks

• Consider another input
• user ' DROP TABLE users SELECT FROM users
  WHERE password '
• pass abc
• ?

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user '' DROP TABLE
users SELECT FROM users WHERE password '' AND
password 'abc'
SELECT FROM users WHERE user '' DROP TABLE
users SELECT FROM users WHERE password '' AND
password 'abc'
29
Multi-command injection attacks

• Consider another input
• user ' SHUTDOWN WITH NOWAIT SELECT FROM users
  WHERE password '
• pass abc
• ?

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user '' SHUTDOWN
WITH NOWAIT SELECT FROM users WHERE password
'' AND password 'abc'
SELECT FROM users WHERE user '' SHUTDOWN
WITH NOWAIT SELECT FROM users WHERE password
'' AND password 'abc'
30
Injection attacks MySQL/Perl/PHP

• Consider another input
• user your-boss
• pass ' OR 11 AND user 'your-boss
• ? Delete your boss!

DELETE FROM users WHERE user u AND password p
http//pages.stern.nyu.edu/mjohnson/dbms/php/user
s.php
DELETE FROM users WHERE user 'your-boss' AND
pass '' OR 11 AND user 'your-boss'
DELETE FROM users WHERE user 'your-boss' AND
pass ' ' OR 11 AND user 'your-boss'
31
Injection attacks MySQL/Perl/PHP

• Consider another input
• user
• pass ' OR 11 OR user '
• ? Delete everyone!

DELETE FROM users WHERE user u AND password p
DELETE FROM users WHERE user '' AND pass
'' OR 11 OR user ''
DELETE FROM users WHERE user '' AND pass ''
OR 11 OR user ''
32
Preventing injection attacks

• Source of problem (in SQL case) use of quotes
• Soln 1 dont allow quotes!
• Reject any entered data containing single quotes
• Q Is this satisfactory?
• Does Amazon need to sell OReilly books?
• Soln 2 escape any single quotes
• Replace any with a or \
• In PHP, turn on magic_quotes_gpc

33
Preventing injection attacks

• Soln 3 use prepare parameter-based queries
• Supported in JDBC, Perl DBI, PHP ext/mysqli
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/log
  insafe.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/use
  rssafe.cgi
• Very dangerous using tainted data to run
  commands at the Unix command prompt
• Semi-colons, prime char, etc.
• Safest define set if legal chars, not illegal
  ones

34
Preventing injection attacks

• When to do security checking for quotes, etc.?
• Natural choice in client-side data validation
• But not enough!
• As saw can still manually submit GET and POST
• ? Must do security checking on server

35
More Info

• phpGB MySQL Injection Vulnerability
• http//www.securiteam.com/unixfocus/6X00O1P5PY.htm
  l
• "How I hacked PacketStorm
• http//www.wiretrip.net/rfp/txt/rfp2k01.txt

36
Next time

• Next XML
• For next time read section 4.7, hand-outs
• Now one-minute responses
• Vote on advanced topic(s) to drop/any topic to
  expand