1.0 Overview/Case Summary

Example:

1. On today's date, John Doe contacted my office in regards to imaging a stolen laptop computer running Windows® XP Professional that had been recovered. Doe is requesting a forensic examination to see what company documents may have been stolen by the suspect(s) and is requesting a full forensic examination and report for possible criminal charges & civil litigation.

This section will vary in length. You will include any relevant information regarding what led to you as the forensic examiner/analyst becoming involved with the digital evidence. You may be just receiving the forensic image and someone else conducted the forensic acquisition and this is a good place to document that as this will correlate with your chain of custody information that you immediately started once you came into contact with the digital evidence. Remember, this is an overview and a summary of how the case was initialized and where you as the examiner/analyst became involved.

2.0 Forensic Acquisition & Exam Preparation

Example:

1. On today's date I began the forensic acquisition/imaging process of the stolen laptop. Prior to imaging the stolen laptop, I photographed the laptop, documenting any identifiers (e.g., make, model, serial #), unique markings, visible damage, etc. while maintaining chain of custody.

2. Using a sterile storage media (examination medium) that had been previously forensically wiped and verified by this examiner (MD5 hash value: ed6be165b631918f3cca01eccad378dd) using ABC tool version 1.0. The MD5 hash value for the examination medium yielded the same MD5 hash value as previous forensic wipes to sterilize this media.

3. At this point, I removed the hard drive from the stolen laptop and connected it to my hardware write-blocker, which is running the most recent firmware and has been verified by this examiner. After connecting the hardware write blocker to the suspect hard drive, I connected the hardware write blocker via USB 2.0 to my forensic examination machine to begin the forensic imaging process?

4. Etc, etc.

This section is very important, as you must detail your interaction with the digital evidence and the steps taken to preserve and forensically acquire the evidence. Any additional steps that you take (e.g. forensically wiping storage/examination media, etc.) should be notated in this section of your report. Remember, this section of your report is usually where you as the examiner/analyst came into contact with the digital evidence and thoroughly documenting what you have done is very important to the integrity of the digital evidence and your chain of custody.

Examiner's Tip: You should have a digital camera in your forensic toolkit. Take a picture of the evidence and document each step of the forensic acquisition and preparation process. Regardless, if you include the picture in your report or as an exhibit, this picture is a perfect field note for you as the examiner to reference when completing your report.

• You will also need to include that you verified your forensic image and notate the hash values (e.g., MD5, SHA-1).

• You will also need to briefly describe the process you used when making a working copy from the forensic image of the original evidence.

3.0 Findings and Report (Forensic Analysis)

Example:

1. After completing the forensic acquisition of the stolen laptop I began analyzing the forensic image of the stolen laptop with Forensic Tool

2. I used the following tools for forensic analysis, which are licensed to this examiner:

o Guidance® Software's EnCase® 6.17 o SANS Investigative Forensic Toolkit (SIFT) Version 2.0 o Internet Evidence Finder v3.3 o RegRipper by Harlan Carvey o Microsoft® Excel 2007

3. A review of the Internet history using Internet Evidence Finder, the following data was recovered from sector 117004, which shows a Facebook email between John Doe and Jane Doe. Further analysis shows that a John Doe logged into his Google Mail account. See screenshots below:

John Doe logging into Google Mail account.

John Doe logging into Google Mail account.

This is the most detailed section of your investigation. You will include all artifacts that you find during your analysis relating to the case.

Examiner's Tip: A very good practice when you are including your evidence into your report is to include hyperlinks within your report to link to pictures, documents, etc. Make sure you test and validate that the hyperlinks work properly so when your report is being reviewed, the reader can navigate easily to the evidence that you are including in your report.

4.0 Conclusion In this section, you are basing your conclusion off the forensic evidence. Remember, the goal of the forensic examination is to report the facts, regardless if the evidence is inculpatory or exculpatory in nature.

Ref: https://www.sans.org/blog/intro-to-report-writing-for-digital-forensics/

Basic SQL

Dr. Buleje

Slide 6- 1

Outline

 SQL Data Definition and Data Types

 Specifying Constraints in SQL

 Basic Retrieval Queries in SQL

 INSERT, DELETE, and UPDATE Statements in

SQL

Slide 6- 3

Basic SQL

 SQL language

 SQL Origin

 SQL Actually comes from the word “SEQUEL” which was the original term used in the paper: “SEQUEL TO SQUARE”

Slide 6- 4

SQL Data Definition, Data Types,

Standards

 Terminology:

 Table, row, and column

 CREATE statement

 Main SQL command for data definition

Slide 6- 5

SQL Standards

 SQL-86 or SQL 1.A.

 SQL-92 is referred to as SQL-2.

 Later standards (from SQL-1999) are divided into

core specification and specialized extensions.

 SQL-2006 added XML features

 SQL-3

Slide 6- 6

Schema and Catalog Concepts in

SQL

 We cover the basic standard SQL syntax

 SQL schema

 Schema elements include

 Tables, constraints, views, domains, and other

constructs

Slide 6- 6

Schema and Catalog Concepts in

SQL (cont’d.)

 CREATE SCHEMA statement

 CREATE SCHEMA COMPANY AUTHORIZATION

‘Jsmith’;

 Catalog

Slide 6- 8

The CREATE TABLE Command in

SQL

 Specifying a new relation

 Can optionally specify schema:

 CREATE TABLE COMPANY.EMPLOYEE ...

or

 CREATE TABLE EMPLOYEE ...

Slide 6- 9

The CREATE TABLE Command in

SQL (cont’d.)

 Base tables (base relations)

 Virtual relations (views)

Slide 6- 10

COMPANY relational database schema

Slide 6- 11

One possible database state for the COMPANY relational database schema

Slide 6- 12

One possible database state for the COMPANY relational database schema – continued

Slide 6- 13

SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure in Slide 10

continued on next slide

Slide 6- 14

SQL CREATE TABLE data definition statements for defining the COMPANY

schema from Figure in Slide 10 -continued

Slide 6- 15

Attribute Data Types and Domains in

SQL

 Basic data types

 Numeric data types

 Integer numbers: INTEGER, INT, and SMALLINT

 Floating-point (real) numbers: FLOAT or REAL, and

DOUBLE PRECISION

 Character-string data types

 Fixed length: CHAR(n), CHARACTER(n)

 Varying length: VARCHAR(n), CHAR

VARYING(n), CHARACTER VARYING(n)

Slide 6- 17

Attribute Data Types and Domains in

SQL (cont’d.)

 Bit-string data types

 Fixed length: BIT(n)

 Varying length: BIT VARYING(n)

 Boolean data type

 Values of TRUE or FALSE or NULL

 DATE data type

 Ten positions

 Components are YEAR, MONTH, and DAY in the

form YYYY-MM-DD

 Multiple mapping functions available in RDBMSs to

change date formats

Slide 6- 18

Attribute Data Types and Domains in

SQL (cont’d.)

 Additional data types

 Timestamp data type

Includes the DATE and TIME fields

 Plus a minimum of six positions for decimal

fractions of seconds

 Optional WITH TIME ZONE qualifier

 INTERVAL data type

 Specifies a relative value that can be used to

increment or decrement an absolute value of a date,

time, or timestamp

Slide 6- 19

Attribute Data Types and Domains in

SQL (cont’d.)

 Domain

 Example:

 CREATE DOMAIN SSN_TYPE AS CHAR(9);

 TYPE

 User Defined Types (UDTs) are supported for

object-oriented applications.

 Uses the command: CREATE TYPE

Slide 6- 18

Specifying Constraints in SQL

Basic constraints:

 Relational Model has 3 basic constraint types that

are supported in SQL:

 Key constraint

 Entity Integrity Constraint

 Referential integrity constraints

Slide 6- 19

Basic Retrieval Queries in SQL

 SELECT statement

 SQL allows a table to have two or more tuples

that are identical

Slide 6- 20

The SELECT-FROM-WHERE

Structure of Basic SQL Queries

 Basic form of the SELECT statement:

Slide 6- 21

Basic Retrieval Queries

Slide 6- 31

Basic Retrieval Queries (Contd.)

Slide 6- 32

Tables as Sets in SQL

 SQL does not automatically eliminate duplicate tuples in

query results

 For aggregate operations duplicates must be accounted

for

 Use the keyword DISTINCT in the SELECT clause

 Only distinct tuples should remain in the result

Slide 6- 38

Tables as Sets in SQL (cont’d.)

 Set operations

 UNION, EXCEPT (difference), INTERSECT

 Corresponding multiset operations: UNION ALL,

EXCEPT ALL, INTERSECT ALL)

Slide 6- 39

Substring Pattern Matching and

Arithmetic Operators

 LIKE comparison operator

 Used for string pattern matching

 % replaces an arbitrary number of zero or more

characters

 underscore (_) replaces a single character

 Examples: WHERE Address LIKE ‘%Houston,TX%’;

 WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;

 BETWEEN comparison operator

 WHERE(Salary BETWEEN 30000 AND 40000)

AND Dno = 5;

Slide 6- 40

Arithmetic Operations

 Standard arithmetic operators:

 Addition (+), subtraction (–), multiplication (*), and

division (/) may be included as a part of SELECT

Slide 6- 27

Ordering of Query Results

 Use ORDER BY clause

 Keyword DESC

 Keyword ASC

ORDER BY D.Dname DESC, E.Lname ASC,

E.Fname ASC

Slide 6- 28

INSERT, DELETE, and UPDATE

Statements in SQL

 Three commands used to modify the database:

 INSERT, DELETE, and UPDATE

 INSERT

 UPDATE

 DELETE

Slide 6- 44

The INSERT Command (examples)

 Specify the relation name and a list of values for

the tuple. All values including nulls are supplied.

 The variation below inserts multiple tuples where

a new table is loaded values from the result of a

query.

Slide 6- 46

The DELETE Command (examples)

 Removes tuples from a relation

 Includes a WHERE clause to select the tuples to be

deleted. The number of tuples deleted will vary.

Slide 6- 49

UPDATE (example)

 Example: Change the location and controlling

department number of project number 10 to

'Bellaire' and 5, respectively

U5: UPDATE PROJECT

SET PLOCATION = 'Bellaire',

DNUM = 5

WHERE PNUMBER=10

Slide 6- 51

Summary

 SQL

 A Comprehensive language for relational database

management

 Data definition, queries, updates, constraint

specification, and view definition

 Covered :

 Data definition commands for creating tables

 Commands for constraint specification

 Simple retrieval queries

 Database update commands

Slide 6- 55