In-class notes for 01/16/2018 (CS 284 (MCA), Interim 2018)

In-class notes for 01/16/2018

CS 284 (MCA), Interim 2018

This session: Lab 9: JDBC, for SQL processing in Java

Submitted questions on assignments and technology

Submitted questions on SQL reading

JDBC

Database terms; DBMSs; queries; Postgres postmaster
Relational data model; records/rows and fields/columns
SQL commands:
- Data definition: e.g., CREATE TABLE
- Data modification: e.g., INSERT; UPDATE; DELETE; etc.
- Querying: mostly SELECT
JDBC concepts:
- making a connection, JDBC driver; CLASSPATH (used for all of Java, not just JDBC)
- executeQuery() and executeUpdate();
- result sets, cursors

Java properties and configuration management

Database security

Database passwords
SQL injection; managed queries
- Demo (see JDBC lab)
- Example: suppose a text box requests user to enter a value to substitute into the following SQL query:
```
   SELECT key FROM mytable WHERE value ~ '______';
```
  What if the user enters
  
  dummy'); DROP TABLE password; SELECT key FROM mytable WHERE value ~ 'val
  
  to be substituted for ______ in that SQL query?
- The vulnerability arises from the fact that strings the user enters are parsed (interpreted) by SQL.
- A managed or prepared query (also called a query with parameters) is submitted to the DBMS ahead of time, and parsed before the data are received. Then, a user's data never gets parsed, only entered as data.
- Postgres example:
```
PREPARE myquery (text) AS
    SELECT key FROM mytable WHERE value ~ $1;
...
EXECUTE myquery('______');
```
  where ______ is obtained from user
xkcd

General Security Principles

Database Schemas

Postgres uses the term schema for namespaces within a database that can have owners, privileges, etc. Each person in class has their own such namespace.
More generally in databases, a schema is a structural model (plan) for organizing data in a database.

Example of external vs logical schema: SQL provides views, which are named results from queries that may be used like tables in many respects.

Example of defining and using a view:

rab_mca_i18=> create view res as 
	select s.sname, b.bname as mfr, b.color, r.day 
	from boats b, sailors s, reserves r
	where s.sid = r.sid and b.bid = r.bid;


rab_mca_i18=> select * from res;
  sname  |    mfr    | color |   day    
---------+-----------+-------+----------
 Dustin  | Marine    | red   | 10/7/98
 Dustin  | Clipper   | green | 10/8/98
 Dustin  | Interlake | red   | 10/10/98
 Dustin  | Interlake | blue  | 10/10/98
 Lubber  | Marine    | red   | 11/12/98
 Lubber  | Clipper   | green | 11/6/98
 Lubber  | Interlake | red   | 11/10/98
 Horatio | Interlake | red   | 9/8/98
 Horatio | Interlake | blue  | 9/5/98
 Horatio | Clipper   | green | 9/8/98
(10 rows)

Operations such as SELECT, GRANT (on "TABLE"), and ALTER TABLE may be performed on a view.
Data-changing operations such as INSERT, DELETE, or UPDATE may not be performed on views. You would need to change the tables in the SELECT statement used to define a view in order to affect the data "contained" in that view.

By defining a view and giving users permission to SELECT on it, we can create "tables" (such as that view) that are present in an external schema but not in the logical schema. It is not necessary for a user to have access to the underlying tables required for a view's defining SELECT statement in order to have access to that view.

< >