C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 18

• M.P. Johnson
• Stern School of Business, NYU
• Spring, 2008

2
Agenda

• Security
• Secrecy
• Integrity
• Availability
• Web issues
• Transactions
• Stored procedures?
• Implementation?

3
Goals after today

• After Today
• Know how to make your PHP-based sites (somewhat
  more) secure

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 security, jails, debugging,
  proofreading, Popperian science, fishing, MC algs

6
DB users have 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 privileges (Oracle)

• Usual of setting access levels
• Creator of object automatically gets all
  privileges to it
• Possible objects tables, whole databases, stored
  functions/procedures, etc.
• ltDB-namegt. - all tables in DB
• A privileged user can grant privileges to other
  users or groups

GRANT privileges ON object TO user ltWITH GRANT
OPTIONgt
GRANT SELECT ON mytable TO someone WITH GRANT
OPTION
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 FROM laura
10
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
11
Issue Passwords

• DBMS recognizes your privileges because it
  recognizes you
• how?
• Storing passwords in the DB is a bad idea

12
Hashed or digested passwords

• One-way hash function
• computing f(x) is easy
• Computing f-1(y) is hard/impossible
• Finding some x2 s.t. f(x2) f(x) is hard/imposs
• collisions
• Intuitively seeing f(x) gives little (useful)
  info on x
• x looks random
• PRNGs
• MD5, SHA-1
• RFID for cars http//www.rfidanalysis.org/

13
Issue Built-in accounts

• Many DBMSs (and OSs) have built-in demo accounts
  by default
• In some versions, must opt out
• MySQL root/(blank) (closed on sales)
• http//lists.seifried.org/pipermail/security/2004-
  February/001782.html
• Oracle scott/tiger (was open on sales last year)
• SQLServer sa/(blank/null)
• http//support.microsoft.com/default.aspx?scidkb
  EN-US313418

14
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 website asks George for his credit card,
  how does he know its our site?
• Maybe its a phishing site
• Secrecy
• When George enters his credit card, will an
  eavesdropper be able to see it?
• Protecting against user input
• Is it safe to run SQL queries based on user input?

15
Security on the web

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

16
Hybrid protocols (SSH,SSL/HTTPS, etc.)

• Neither private- nor public-key alone suffices
• They each only solve half of each problem
• But together they solve almost everything
• Recurring strategy
• We do private-key crypto
• Where do we get the key?
• You send it (encrypted) to me

17
SSH-like authentication (intuition)

• sales has a public-key
• When you connect to sales,
• You pick a random number
• Encrypt it (with the cert) and send it to them
• They decrypt it (with their private key)
• Now, they send it back to you
• Since they decrypted it, you trust theyre sales

18
HTTPS-like authentication (intuition)

• Amazon has a public-key certificate
• Encrypted with, say, Verisigns private key
• When you log in to Amazon,
• They send you the their Verisign-encrypted cert
• You decrypt it (with Verisigns public key), and
  check that its a cert for amazon.com
• Since the decrypt worked, the cert must have been
  encrypted by Verisign
• So this must really be Amazon

19
Authentication on the web

• Now George trusts that its really Amazon
• Assuming Amazons private key is secure
• And excluding man-in-the-middle
• But What if, say, Dick guessed Georges
  password?
• Another way What if George claims Dick guessed
  his password?
• Soln same process, but in reverse
• But now you need to get your own cert

20
Hybrid protocol for encryption

• Amazon just sent you their public-key cert
• When you log in to Amazon,
• You pick a random number (session key)
• You encrypt it (with the cert) and send it to
  them
• They decrypt it (with their private key)
• Now, you both share a secret key
• can now encrypt passwords, credit cards, etc.

21
New topic 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

22
CGI security

• Imagine scenario
• Youre Amazon
• Users can search for books
• Users can put books in the cart
• A couple pages to pay
• You need to
• Charge P (the books price) at the end
• Display P on each page
• Dont want to query of price for every single
  page
• One bad idea each page after first takes P as a
  (hidden) get var from prior

23
CGI security

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

http//amazon.com/cart.cgi?titleDatabaseSystems
price.01
24
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-Typea
pplication/x-www-form-urlencoded Content-Length
32 titleDatabaseSystemsprice.01
25
Hand-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
26
Query-related Injection attacks

• Heres a situation
• Prompt for user/pass
• Do lookup
• If found, user gets in
• test.user table in MySQL
• http//pages.stern.nyu.edu/mjohnson/dbms/php/logi
  n.php / txt
• http//pages.stern.nyu.edu/mjohnson/dbms/php/user
  s.php / txt
• Modulo the no hashing, is this a good idea?

SELECT FROM users WHERE useru AND passwordp
27
Injection attacks

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

SELECT FROM users WHERE user u AND password
p
SELECT FROM users WHERE user 'mjohnson' AND
password 'topsecret'
28
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 ''
29
Injection attacks MySQL/Perl/PHP

• Consider this one
• 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'
30
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 ''
31
Multi-command inj. attacks (other DBs)

• 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'
32
Multi-command inj. attacks (other DBs)

• 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'
33
Multi-command inj. attacks (other DBs)

• 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'
34
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'
35
Injection attacks MySQL/Perl/PHP

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

DELETE FROM users WHERE user u AND pass p
http//pages.stern.nyu.edu/mjohnson/dbms/php/user
s.php
DELETE FROM users WHERE user '' OR 11 OR
user '' AND pass '' OR 11 OR user ''
DELETE FROM users WHERE user '' OR 11 OR user
'' AND pass '' OR 11 OR user ''
36
Preventing injection attacks

• Ultimate source of problem 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 Perl, use taint mode wont show
• In PHP, turn on magic_quotes_gpc flag in
  .htaccess
• show both PHP versions

37
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
• Even more 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

38
Preventing injection attacks

• When to do security-checking for quotes, etc.?
• Temping choice in client-side data validation
• But not enough!
• As saw earlier can submit GET and POST params
  manually
• ? Must do security checking on server
• Even if you do it on client-side too
• Same with data-validation
• Example of constraints

39
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

40
Now theres Google hacking

• inurl"ViewerFrame?Mode"
• intitle"Live View / - AXIS" inurlview/view.sht
  
• intitle"toshiba network camera - User Login"
• http//200.71.42.48/ViewerFrame?ModeMotionLangua
  ge0
• http//141.211.44.254/view/index.shtml
• http//66.186.226.189/view/index.shtml

41
Security Conclusion

• Not an exhaustive list of issues
• Big, serious, difficult problems
• Each DBMS/product/tech has its own issues
• Do your hw, or you/your company can look
  ridiculous or worse