=====
CS 325 - Week 8 Asynchronous Materials
=====

=====
"TODAY" WE WILL
=====
*   announcements
*   SQL aside: quick intro to the UNION operator
*   SQL aside: quick intro to the SQL*Plus & operator, for
    interactive input
*   intro to database security, part 2: quick small intro
    SQL INJECTION
*   if time: intro to ANSI join notation
*   if time: demo of a 3-table join
*   if time: more discussion of kinds of integrities supported
    by DBMSs
*   prep for next class

=====
READING for next week:
=====
*   for NEXT WEEK, starting Monday:
    *   DB Reading Packet 6: Normalization
    *   SQL Reading Packet 5: "Order by, group by, and having"

=====
Other announcements:
=====
*   Remember: your PROJECT SCENARIO SELECTION milestone is due
    by 11:59 pm on FRIDAY, October 15th
    *   and once you've chosen/developed/decided on your project
        scenario, you should start working on your ER MODEL DRAFT

        ...that ER MODEL DRAFT is due by 11:59 pm on Sunday, October 24th

*   Watch for class emails as parts of Homework 6 become available
    (after Froday's labs)

=====
*   lead-up to SQL INJECTION intro: part 1:
    
    *   SQL UNION operator

    *   relational UNION operation:
        since relations are SETS of tuples,

	then if TWO relations R1 and R2 are UNION-COMPATIBLE,
	then the UNION of Relation R1 and Relation R2 is the set of
	    tuples that are in R1 along with the tuples that are in R2
	    (but there should be no duplicates in the result,
	    since a tuple is considered to be either in a set or a union
	    or not!)

    *   so, what does it mean for two relations to be UNION-compatible?
        *   the two relations have to have the SAME number of attributes

        *   those attributes in each relation need to have the SAME
	    DOMAIN, respectively

    *   so -- how do you write a UNION in SQL?

        (sub-select)
	union
	(sub-select)

        *   ...and this WILL return a "true" relational union!

=====
*   lead-up to SQL INJECTION intro: part 2:
    *   using SQL*Plus operator & for interactive input into a
        SELECT statement (see 325lect08-2.sql)

=====
STARTING intro to SQL INJECTION (to be continued in CS 328!)
=====
*   originally from: 
http://st-curriculum.oracle.com/tutorial/SQLInjection/index.htm, sect 1.1:

   "SQL injection is a technique to maliciously exploit applications 
   that use client-supplied data in SQL statements. 
            ^^^^^^^^^^^^^^^^^^^^

   Attackers trick the SQL engine into executing unintended commands 
   by supplying specially crafted string input, thereby gaining 
   unauthorized access to a database in order to view or manipulate 
   restricted data.

   SQL injection techniques may differ, but they all exploit a single 
   vulnerability in the application:

   vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
   Incorrectly validated or nonvalidated string literals are
   concatenated into a dynamic SQL statement,

    and interpreted as code by the SQL engine."

*   Consider:

    select salary
    from   empl
    where  job_title = &job_ttl;

    What if the user enters:
    'Manager' or 1=1

*   Furthermore, consider:

    What if you have this query?

    select empl_last_name
    from   empl
    where  job_title = &job_ttl;

    What if the user enters:
    'Manager' union select table_name from user_tables

    'Manager' union select column_name from user_tab_columns where table_name = 'DEPT'

=====
*   a SQL script is not the MOST insecure example of this --
    web forms for applications that access a database are WAY more available
    and can open you up to LOTS more damage;

*   we'll discuss this more in CS 328, BUT in the meantime here are SOME means
    for combatting SQL injection:

    *   be careful to always SANITIZE user input before "plugging it into"
        DYNAMICALLY-built SQL statement

    *   we might restrict our use of dynamically-built SQL statements;
        (static, unchanging SQL statements aren't subject to SQL injection)

    *   we might take care to ENCRYPT certain dats stored in database tables;

    *   we might carefully use GRANT and REVOKE statements, along with a feature
        we'll discuss later, SQL views, to carefully control who can access
	which tables (and sometimes, with the help of views, who can access
	particular columns via a kluge...)

    *   KEEP this in mind, it is IMPORTANT when you have dynamic SQL!!!!

    AND: http://xkcd.com/327/
         8-)