1 16-Databases

Previous: 15-BufferOverflow.html

1.1 Exploits of a mom


1.2 Jokes of a dad

Where do I keep all these dad jokes?
In a dadabase…

1.3 Screencasts

1.4 Database Security

Computers are mostly used for storing stuff, and most of that stuff is in databases…

1.4.1 DBMS

DataBase Management System

What is a Database?
* Structured collection of data, stored for use by one or more applications
* Contains relationships between data items and groups of data items
* Often contains sensitive data that needs to be secured
* Query language provides a uniform interface to the database
* Database management system (DBMS)
* Suite of programs for constructing and maintaining the database
* Offers ad-hoc query facilities to multiple users and applications


1.4.2 Relational Databases

Multi-table database with unifying primary key:

Relational Database Elements

Primary key
* Uniquely identifies a row
* Consists of one or more column names

Foreign key
* Links attributes in one table to attributes in another

View/virtual table
* Result of a query that returns selected rows and columns from one or more tables

Relational Database

View on a Database

1.4.3 Structured Query Language (SQL)

https://www.w3schools.com/sql/default.asp (Read this guide if you want to know about Web security!)
* Standardized language to define schema, as well as manipulate and query data in a relational database
* Several similar versions of ANSI/ISO standard
* All follow the same basic syntax and semantics
* SQL statements can be used to:
* Create tables
* Insert and delete data in tables
* Create views
* Retrieve data with query statements

Table Creation
CREATE TABLE department
Dname CHAR (30),
Dacctno CHAR (6)

Table Creation
Ename CHAR (30),
SalaryCode INTEGER,
Ephone CHAR (10),
FOREIGN KEY (Did) REFERENCES department (Did)

Retrieving Information
* The basic command for retrieving information is the SELECT statement
SELECT Ename, Eid, Ephone
FROM Employee
WHERE Did = 15

View Creation
* The view in Figure 5.4(b) above is created using the following SQL statement:
CREATE VIEW newtable (Dname, Ename, Eid, Ephone)
AS SELECT D.Dname E.Ename, E.Eid, E.Ephone
FROM Department D Employee E
WHERE E.Did = D.Did

1.5 SQL Injection Attacks (SQLi)

https://www.w3schools.com/sql/sql_injection.asp (part of the above-referenced tutorial)
https://www.hacksplaining.com/exercises/sql-injection (Good interactive exercise; show in class)
* One of the most prevalent network-based security threats
* Designed to exploit the nature of Web application pages and back-end databases
* Sends malicious SQL commands to the database server
* Most common attack goal is bulk extraction of data
* Depending on the environment, SQL injection can also be exploited to:
* Modify or delete data
* Execute arbitrary operating system commands
* Launch denial-of-service (DoS) attacks

SQLi overview

1.5.1 Injection technique Example

var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable
where ShipCity = ' " +
ShipCity + " ' ";

Example usage
* When the script is executed, the user is prompted to enter a city, and if the user enters Boston, then the following SQL query is generated:
'SELECT * FROM OrdersTable WHERE ShipCity = 'Boston

Example attack
* Suppose, however, the user enters the following:
'Boston'; DROP table OrdersTable--

SQLi Attack Avenues
* User input:
* Attackers inject SQL commands by providing suitable crafted user input
* Server variables:
* Attackers can forge the values that are placed in HTTP and network headers and exploit this vulnerability by placing data directly into the headers
* Second-order injection:
* A malicious user could rely on data already present in the system or database to trigger an SQL injection attack, so when the attack occurs, the input that modifies the query to cause an attack does not come from the user, but from within the system itself
* Cookies:
* An attacker could alter cookies such that when the application server builds an SQL query based on the cookie’s content, the structure and function of the query is modified
* Physical user input:
* Applying user input that constructs an attack outside the realm of web requests

1.5.2 Attack Types In-band Attacks Tautology Example Inferential Attack Out-of-Band Attack

1.5.3 SQLi Countermeasures Defensive Coding Detection Run-time Prevention

1.6 Database Access Control

1.6.1 SQL Access Controls

Two commands for managing access rights:

  1. Grant: Used to grant one or more access rights or can be used to assign a user to a role
  2. Revoke: Revokes the access rights

Typical access rights are:
* Select
* Insert
* Update
* Delete
* References

The grant command
| | |
| GRANT | {privileges | role} |
| [ON | table] |
| TO | {user | role | PUBLIC} |
| [IDENTIFIED BY | password] |


REVOKE {privileges | role}
[ON table]
FROM {user | role | PUBLIC}


Cascading Authorizations
* The grant option enables an access right to cascade through a number of users
* The revocation of privileges also cascaded

Privilege Revocation

1.7 Role-Based Access Control (RBAC)

1.7.1 Categories of Database Users

Microsoft SQL Server Roles

1.8 Inference Attacks

1.8.1 Attack Method

What is an Inference Attack?
* The process of performing authorized queries and deducing unauthorized information from the legitimate responses received
* The inference problem arises when the combination of a number of data items is more sensitive than the individual items, or when a combination of data items can be used to infer data of a higher sensitivity

Inference Attacks

Example of an Inference Attack on (Salary-Name)

1.8.2 Inference Detection


1.9 Database Encryption

1.9.1 Remote Encryption

Database Encryption
* The database is typically the most valuable information resource for any organization
* Protected by multiple layers of security
* Firewalls, authentication, general access control systems, DB access control systems, database encryption
* Encryption becomes the last line of defense in database security
* Can be applied to the entire database, at the record level, the attribute level, or level of the individual field

How to Process Encrypted Query
1. The user issues an SQL query for fields from one or more records with a specific value of the primary key
2. The query processor at the client encrypts the primary key, modifies the SQL query accordingly, and transmits the query to the server
3. The server processes the query using the encrypted value of the primary key and returns the appropriate record or records
4. The query processor decrypts the data and returns the results

Database Encryption: Decrypt only Locally

Encrypted Query, where database is stored in encrypted form, and thus queries are made for ciphertext itself, as far as the database is concerned.

SELECT Ename, Eid, Ephone
   FROM Employee
   WHERE Did = 15
SELECT Ename, Eid, Ephone
   FROM Employee
   WHERE Did = 1000110111001110

1.9.2 Indexing on Encrypted Data


Next: 17-DefensiveProgramming.html