Intro to Databases (Week 11)

1. SQL - Transaction Statements

SQL - Transaction Statements control transactions in database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL).

There are two types of SQL-Transaction Statements :
A. COMMIT statement ----- commit all changes for the current transaction
General Format : COMMIT [WORK]
B. ROLLBACK statement --- roll back all changes for the current transaction
General Format : ROLLBACK [WORK]

* Overview

A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table. The upshot is that operations which require modification of several tables must involve multiple modifcation statements. For example, A bank operation transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.

A transaction begins with the execution of a SQL-Data statement when there is no current transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction.

The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent.

* Transactional Isolation
In most cases, transactions are executed under a client connection to the DBMS. Multiple client connections can initiate transactions at the same time. This is known as concurrent transactions.

In the relational model, each transaction is completely isolated from other active transactions. At transaction isolation level Serializable, a transaction is fully isolated from changes made by other sessions. Queries issued under Serializable transactions cannot see any subsequent changes.

2. SQL-Schema Statements

SQL-Schema Statements provide maintenance of catalog objects for a schema -- tables, views and privileges. This subset of SQL is also called the Data Definition Language for SQL (SQL DDL).

There are six types of SQL-Schema Statements:
A. CREATE TABLE Statement
B. CREATE VIEW Statement
C. DROP TABLE Statement
D. DROP VIEW Statement
E. GRANT Statement
F. REVOKE Statement

-- REVOKE Statement
The REVOKE Statement revokes access privileges for database objects previously granted to other users.
General Format : REVOKE privilege-list ON [TABLE] object-list FROM user-list
The REVOKE Statement revokes each privilege in privilege-list for each object in object-list from each user in user-list. All privilleges must have been previously granted.
Example :
REVOKE SELECT ON s, sp FROM PUBLIC
REVOKE SELECT , INSERT, UPDATE(color) ON p FROM art, nan
REVOKE SELECT ON supplied_parts FROM sam

2. Object-Oriented Database Management System (OODBMS)

-- Overview --
An OODBMS is the result of combining object oriented programming principles with database management principles. An OODBMS should be able to store objects that are nealry indistinguishable from the kind of objects supported by the target programming language with as little limitation as possible. Persistent objects should belong to a class and can have one or more atomic types or other objects as attributes. The normal rules of inheritance should apply with all their benefits including polymorphism, overriding inherited methods and dynamic binding. Each objet has an object identifier (OID) which used as a way of uniquely identifying a particular object. OIDs are permanent, system generated and not based on any of the member data within the object.

A primary feature of an OODBMS is that accessing objects in the database is done in a transparent manner such that interaction with persistent objects is no different from interacting with in-memory objects. This is very different from using an RDBMSs in that there is no need to interact via a query sub-language like SQL nor is there a reaon to use a Call Level Interface suchas ODBC, ADO or JDBC.

-- List of OODBMS
Object Store, O2, Gemstone, Versant, Ontos, DB/Explorer ODBMS, Ontos, Poet, Objectivity/DB, EyeDB, Ozone, Zope, FramerD, XL2

Bits and Pieces (Week 10)

1. SQL Injection Attacks (Some Prevention tips)

A. Definition

A SQL Injection attack is a form of attack that comes from user input that has not been checked to see that it is valid. The objective is to fool the database system into running malicious code that will reveal sensitive inforamtion or otherwise compromise the server.
There are two main types of attacks: First-order attacks, and Second-order attacks....
First-order attacks are when the attacker receives the desired result immediately, either by direct response from the application they are interacting with or some other response mechanism, such as email.
Second-order attacks are when the attacker injects some data that will reside in the databaase, but the payload will not be immediately activated....

B. Prevention tips

* Locking down
If a web application were to request that the user choose a data, it would be normal that the values for the data checked in some JavaScript function on the web page before any data was posted back to the server. This improves the user experience by reducing the wait between lots of server requests. However, the value needs to be validated again on the server as its is possible to spoof the request with a deliberately crafted invalid data.
* Encrypting data
* Least Privilege - Database account
* Least Privilege - Process account
* Cleaning and Validating input
* Parameterised Queries
( Example ) SQL Server
string commandText = "SELECT * FROM Customers "+
"WHERE Country=@CountryName";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName", countryName);
*Stored Procedures
Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data beyond the simple constraints otherwise available on the tables. Parameters can be checked for valid ranges. Information can be cross checked with data in other tables.
If we consider a database that has the user details for a website, this includes the user name and password. It is important that an attacker is unable to get a list of passwrods or even one password. The stored procedures are designed so that a password can be passed in, but it will never put a password in any result set.
* Re-validation of data in Stored Procedures
* Ensure that error messages give nothing away about the internal architecture of the application or the database


2. PHP Error Logging

By default, PHP sends an error log to the servers logging system or a file, depending on how the error_log configuration is set in the php.ini file. By using the error_log() function you can send error logs to a specified file or a remote destination.
Sending errors messgeas to ourself by e-mail can be a good way of getting notified of specific errors.
[Example Code]

function customError($errno, $errstr)
{
echo "Error: [$errno] $errstr
";
echo "Webmaster has been notified";
error_log("Error: [$errno] $errstr", 1,
someone@example.com, "From: webmaster@example.com");
}

set_error_handler( "customerError", E_USER_WARNING);

$test=2;
if ($test>1)
{
trigger_error("Value must be 1 or below", E_USER_WARNING);
}
?>


}

Feeds ( Week 9 )

1. PodCasting

-- How to Podcasting
A. create audio file using recording software like Replay Radio, RecordForAll
and so on.
B. add the audio file to an RSS 2.0 feed
( For instance, how to podcast using FeedForAll )
http://www.feedforall.com/podcasting-tutorial.htm
C. tell the world about your podcast
http://www.podcasting-station.com/submitrss.php
http://www.podcastingnews.com/topics/Add_Your_Podcast.html
http://www.podcastalley.com/add_a_podcast.php
http://www.podcasterworld.com/add1.php
and so on.......

-- Definition of Podcasting
Podcasting is online audio content that is delivered via an RSS feed. Many
people liken podcasting to radio on demand. However, in reality, podcasting
gives far more options in terms of content and programming than radio does.
In addtion, with Podcasting, listners can determine the time and the place,
meaning they decide what programming they want to receive and when they
want to listen to it. Podcasting will be used for self-Guided Walking Tours, Music, Talk Shows, Training, Story telling and so on. Podcasting is the syndication of audio files using RSS. It works the same as a standard RSS feed reader or news aggregator, the only difference is that the feed you subscribe to contains an audio file in it. Instead of reading content in our RSS feed reader or aggregator, we can listen to the contents of our feed using a reader or aggregator that supports podcasting.

-- Tools for supporting Podcasting
A. Recording Audio Software
http://www.recordforall.com/
http://www.applian.com/replay-radio/index.php
B. Creating Podcasts
http://www.feedforall.com/
C. Managing podcast downloads
http://www.podfeeder.com/
D. Windows Podcast Client Software
http://www.bradsoft.com/feeddemon/beta/
http://sourceforge.net/projects/jpodder
E. MAC Podcast Client Software
http://www.ipodder.org/whatIsIpodder
http://juicereceiver.sourceforge.net/index.php

2. Vlogging

-- The definiton of Vlogging
vlog is a blog that comprises video. vlogs also often take advantage of web syndication to allow for the distribution of video over the Internet using either the RSS or Atom syndication formats, for automatic aggregation and playback on mobile devices and personal computers.