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.

Basic Web Application ( Week 8 )

1. Installaion
( How to Install PHP and MySQL Under Windows XP )
http://heliotropicsystems.com/pubs/TSa092005.pdf
http://www.builderau.com.au/program/mysql/print.htm?TYPE=story&AT=339271632-
339028784t-320002018c
( How to configure LAMP )
http://lamphowto.com/
http://www.mysql-apache-php.com/

There are good sources to help installing LAMP and WAMP.

2. PHP Configuration (php.ini)
Sometimes, We need to modify this file to set up our system. Because PHP stores
all kinds of configuration in this file. This file is in the directory where we
installed PHP.

Following is good source to understand how to use php.ini file
http://www.washington.edu/computing/web/publishing/php-ini.html

3. Uploading Files to MySQL Database
This is very interesting skill and sometimes very useful.. So, I wanna mention
here.
Using PHP to upload files into MySQL database sometimes needed by some web
application. For instance for storing pdf documents or image.

A. First, Make the teable for the upload files
id, name, type, size, content

We can use one of three BLOB data types for column content.
They are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

BLOB is limited to store up to 64 kilobytes of data and MEDIUMBLOB is to store
up to 16 megabytes.

( Example )
CREATE TABLE upload (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY(id)
);

B. Second, uploading a file to MYSQL DB.
uploading the file to the server then read the file and insert it to MySQL.

Using post method, and then using file as input type. Bellow is the example

And then we can use $_FILES to upload the file to MySQL

$_FILES['userfile']['name']
$_FILES['userfile']['type']
$_FILES['userfile']['size']
$_FILES['userfile']['tmp_name']
$_FILES['userfile']['error']

Following is the example of uploading the file to MySQL

$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp = fopen($tmpName. 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

$query = "INSERT INTO upload (name, size, type, content) VALUES
('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die ('Error, query failed');

Wow! I'm feeling there are so many interesting techniques in PHP+MySQL....
Continue to next

JavaScript

1. Functions of JavaScript
A. Giving HTML designers a programming tool
B. Enabling put dynamic text into an HTML page
C. Enabling react to events
D. Enabling read and write HTML elements
E. Enabling validating data
F. Enabling detect the visitor's browser
G. Enabling create cookies

2. JavaScript Event Reference
onabort, onblur, onchange, onclick, ondbclick, onerror, onfocus, onkeydown,
onkeypress, onkeyup, onload, onmousedown, onmousemove, onmouseout, onmouseover,
onmouseup, onreset, onresize, onselect, onsubmit, onunload

** onerror event **
This event is useful to show a script error in the page.

(( Syntax ))
onerror=handleErr

function handleErr(msg,url,l)
{
// Handle the error here
return true or false
}

For using this onerror event, we need to create a function to hadle the errors.
Then we call the function with the onerror event handler. The event handler is
called with three arguments: msg(error message), url(the url of the page that
caused the error) and line(the line where the error occured).

3. How to break a code line in javascript

(( Example ))
document.write("This is \
DMT class.")

With a backslash, we can break up a code line.
But, we can't break up a code like the follwoing:

document.write \
("This is DMT class.")

4. Comments for JavaScript
A. // -- For single line comment
B. /* */ -- For multi line comment

5. Built-in JavaScript Objects

A. String Object
*Method
anchor, big, charAt, indexof, substr, toLowerCase and so on
*Property
constructor, length, prototype
B. Date Object
*Method
Date, getDate, getSeconds, getTime, setDate, toString, UTC and so on
*Property
constructor, prototype
C. Array Object
*Method
concat, join, reverse, shift, sort, toString and so on
*Property
constructor, length, prototype
D. Boolean Object
E. Math Object
*Method
abs, asin, ceil, log, random, sin, tan and so on
*Property
constructor, prototype
F. More Objects
Window, Navigator, Screen, History, Location

6. JavaScript Browser Detection

(( Example ))
var browser=navigator.appName
var b_version=navigator.appVersion
var version=parseFloat(b_Version)

document.write("Browser name:"+browser)
document.write("
")
document.write("Browser version: "+ version)

* With NAVIGATOR object, JavaScript can detect the visitor's browser type and
version.

7. JavaScript Cookie

(( Example ))
A. SetCookie
function setCookie(c_name, value, expiredays)
{
var exdate=new Date()
exdate.setDate(exdate.getDate()+expiredays)
document.cookie=c_name+"="+escape(value)+
((expiredays==null) ? "" : ";expires="+exdate.toGMTString())
}

B. GetCookie
function getCookie(c_name)
{
if (document.cookie.length>0)
{
c_start=document.cookie.indexOf(c_name + "=");
.........
}
}

C. CheckCookie
function checkCookie()
{
username=getCookie('username')
if (username!=null && username!="")
{ alert('Welcome again '+username+'!')}
else
{
username=prompt('Please enter your name:',"")
if (username!=null && username!="")
{
setCookie('username',username,365)
}
}
}

* Cookies are stored on the visitor's computer. Whenever the same computer
requests a page with a browser, it will send the cookie too.
--- Cookie is usually used to identify user.

8. Creating JavaScript Objects
A. Creating instance of an object
(( Example ))
personinfoObj=new Object()
personinfoObj.firstname="John"
personinfoObj.lastname="Doe"
personinfoObj.age=50
personinfoObj.eyecolor="blue"

B. Creating template of object

function personinfo(firstname, lastname, age, eyecolor)
{
this.firstname=firstname;
this.lastname=lastname;
this.age=age;
this.eyecolor=eyecolor;
}

StudentOne=new personinfo("Mike","Doe",30,"blue")

Week 5 : XHTML + CSS

A. Three flavors of XHTML 1.0
1.XHTML 1.0 Strict -- Use this when you want really clean structual mark-up, free of any markup associated with layout. Use this together with W3C's
Cascading Style Sheet language(CSS) to get the font, color, and layout effects
you want.
2.XHTML 1.0 Transitional -- Many people writing Web pages for the general public to access might want to use this flavor of XHTML 1.0. The idea is to take advantage of XHTML features including style sheets but nonetheless to make
small adjustments to your markup for the benefit of those viewing your pages
with older browsers which can't understand style sheets. These include using
the body element with bgcolor, text and link attributes.
3.XHTML 1.0 Frameset -- Use this when you want to use Frames to partition the browser window into two or more frames.
B.XHTML 2.0
XHTML 2.0 is a markup language intended for rich, portable web-based applciations. While the ancestry of XHTML 2.0 comes from HTML 4, XHTML 1.0, and XHTML 1.1, it is not intended to be backward compatible with its earlier versions. Application developers familiar with its earlier ancestors will be comfortable working with XHTML 2.0.
XHTML 2.0 updates many of the modules defined in Modularization of XHTML, and includes the updated versions of all those modules and their semantics.
C.The benefits of CSS
1.Control layout of many documents from one single style sheet
2.More precise control of layout
3.Apply different layout to different media-types(screen, print, etc.)
4.numerous advanced and sophisticated techniques
D. Grouping of elements (Span and Div)
1.The elements and are used to group and structure a document and will often be used together with the attributes class and id.
The element is what you could call a neutral element which does not add anything to the document itself. But with CSS, can be used to add visual features to specific parts of text in your documents.
E.The Box Model
The box model in CSS describes the boxes which are being generated for HTML-elements. The box model also contains detailed options regarding adjusting margin, border, padding and content for each element.
F.Floating elements
An element can be floated to the right or to left by using the property float. That is to say that the box with its contents either floats to the right to the left in a document.
G.Layer on layer with z-index
CSS operates in three dimensions - height,widht and depth. Layers can be used in many situations. For example, try to use z-index to create effects in headlines instead of creating these as graphics. For one thing, it is faster to load text and for another, it provides a potentially better ranking in search engines.
H.XML
1.Well-formed documents
--One and only one root element exists for the document.
--Non-empty elements are delimited by both a start-tag and an end-tag.
--Empty elements may be marked with an empty-element tag.
--All attribute values are quoted, either sing or double quotes.
--Tags may be nested but must not overlap. Each non-root element must be completely contained in another element.
--The document complies to its character set definition. The charset is usually defined in the xml declaration but it can be provided by the transport protocol, such as HTTP.
2.DTD
--The oldest schema format for XML is the Document Type Definition(DTD), inherited from SGML. While DTD support is ubiquitous due to its inclusion in the XML 1.0 standard.
3.XML Schema
--A newer XML schema language, described by the W3C as the successor of DTDs, is XML Schema, or more informally referred to by the intialism for XML Schema instances, XSD(XML Schema Definition). XSDs are far more powerful than DTDs in descrbing XML languages. They use a rich datatyping system, allow for more detailed constraints on an XML document's logical structure and are required to be processed in a more robust validation framework.
I.RSS
--RSS stands for Really Simple Syndication
--RSS allows you to syndicate your site content
--RSS defines an easy way to share and view headlines and content
--RSS files can be automatically updated
--RSS allows personalized views for different sites
--RSS is written in XML
--RSS is useful for web sites that are updated frequently
News sites, Companies, Calendars, Site changes
--With RSS, information on the internet becomes easier to find, and web developers can spread their information more easily to special interest groups.
J.CSS Pseudo-classes & elements
-- :active Adds special style to an activated element
-- :focus Adds special style to an element while the elements has focus
-- :hover Adds special style to an element when you mouse over it
-- :link Adds special style to an unvisited link
-- :visisted Adds special style to a visited link
-- :first-child Adds special style to an element that is the first child of some other element
-- :lang Allows the author to specify a language to use in a specified element
-- :first-letter Adds special style to the first letter of a text
-- :first-line Adds special style to the first line of a text
-- :before Inserts some content before an element
-- :after Inserts some content after an element

My first Web-page

I just put my first webpage. Link will show error page. Because I didn't create other web pages yet. ( ^^ )

http://students.mim.iml.uts.edu.au/users/10355042/Project/index.php