Chapter 10 ASP.NET Security
Download
Report
Transcript Chapter 10 ASP.NET Security
Database Programming
Yingcai Xiao
Introduction to Database
Definitions
.
Computer Science: the science of data processing using
a computational device.
Database (DB): a persistent store of data in a compact,
secure, easy-and-fast-to-retrieve form.
Database Engine: a software program that creates and
manages databases. (e.g. MS Jet Engine)
Database Management System (DBMS): a database
engine plus user interfaces and other supporting software.
DBMS Examples: Oracle, DB2 (IBM), Sybase, Informix,
Microsoft SQL Server, Microsoft Access, MySQL (public
domain), …
Database Server: a DBMS that provides data to its
remote clients.
Definitions Cont.
Database API: application programming interface to
DBMSs.
Database Table: data are stored in a database as
“tables”. Each row of a table is called a record, each
column of a table is called an attribute.
PhoneBook (Database Table Name)
Name
Office
Home
Cell
Tom
330-972-5809
330-888-8888
330-168-8888
John
330-972-7777
330-777-7777
330-168-7777
…
…
…
…
Database schema: meta data for databases, defining
tables and their attributes.
SQL
SQL: Structured Query Language, a standardized
language for accessing and manipulating databases.
The Select-From-Where Clause:
Select Office From PhoneBook Where Name=’Tom’;
Select * From PhoneBook;
Three Parts of SQL:
Query: data retrieval
DML - Data Manipulation Language: inserting, deleting, updating, …
DDL - Data Definition Language: table creation, alteration and drop.
SQL Example
CREATE DATABASE pubs;
USE pubs;
CREATE TABLE authors
(
au_id
varchar(11)
NOT NULL,
au_lname
varchar(40)
NOT NULL,
au_fname
varchar(20)
NOT NULL,
phone char(12) NOT NULL DEFAULT 'UNKNOWN',
address
varchar(40)
NULL,
city
varchar(20)
NULL,
state
char(2)
NULL,
zip
char(5)
NULL,
contract
bit
NOT NULL,
PRIMARY KEY(au_id)
);
SQL Example
insert authors
values('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1);
insert authors
values('213-46-8915', 'Green', 'Marjorie', '415 986-7020',
'309 63rd St. #411', 'Oakland', 'CA', '94618', 1);
DBMS Example: SQL Server
•
•
•
•
By Microsoft
Needs runtime license
Best fit for .NET
Features
http://www.microsoft.com/sql/prodinfo/features/top30features.mspx
• Free version: SQL Server Express
http://www.microsoft.com/sql/editions/express/default.mspx
• Available in MSDNAA.
http://www.cs.uakron.edu/~xiao/msdnaa.html
DBMS Example: MySQL
•
•
•
•
•
•
By MySQL AB (part of Sun after 1/16/08)
Free: http://dev.mysql.com/
No need of runtime license
Not the best fit for .NET
Installed on db1.cs.uakron.edu
How to install it at home:
http://www.cs.uakron.edu/~xiao/windows/MySQLInstallation.html
Database Programming
Architecture of a Three-Tier Application
Supporting Software
App User Interface
User Interface
Application Logic
Database Engine
Database
DBMS / Database Server
Database API
Application Server
Architecture of a Three-Tier Application
C
L
I
E
N
T
Architecture of a Four-Tier Application
Supporting Software
App User Interface
WEB
WEB
S
E
R
V
E
R
C
L
I
E
N
T
User Interface
Application Logic
Database Engine
Database
DBMS / Database Server
Database API
Application Server
Architecture of a Four-Tier Application
Building a Four-Tier Application on the
CSNET
Supporting Software
App User Interface
WEB
WEB
S
E
R
V
E
R
C
L
I
E
N
T
User Interface
Application Logic
Database Engine
Database
db1.cs.uakron.edu//MySQL
Database API
paush.cs.uakron.edu//PHP
http://www.cs.uakron.edu/~xiao/isp/MySQL-HowTo.html
PHP/MySQL Example
• Create the database using
cars.sql
• Read and display the “Covette”
database table using
access_cars.php
PHP/MySQL Example: Programming
1.Open Database.
2.Prepare SQL string for query
3.Execute the query.
4.Display a table to show the
query results.
5.Read and display the rows of
data from the query.
PHP/MySQL Example
<!-- access_cars.php
A PHP script to access the cars database through MySQL
-->
<html>
<head>
<title> Access the cars database with MySQL </title>
</head>
<body>
<?php
// Connect to MySQL
$db = mysql_connect("db1.cs.uakron.edu:3306", "xiaotest", "wp2009");
if (!$db) {
print "Error - Could not connect to MySQL”;
exit;
}
PHP/MySQL Example
// Select the cars database
$er = mysql_select_db("xiaotest");
if (!$er) {
print "Error - Could not select the cars database”;
exit;
}
// Clean up the given query (delete leading and trailing whitespace)
$query = "SELECT * FROM Corvettes";
trim($query);
//$query = stripslashes($query);
print "Striped query is $query <br />”;
$query_html = htmlspecialchars($query);
print "<p> <b> The query is: </b> " . $query_html . "</p>”;
PHP/MySQL Example
// Execute the query
$result = mysql_query($query);
if (!$result) {
print "Error - the query could not be executed”;
$error = mysql_error();
print "<p>" . $error . "</p>”;
exit;
}
PHP/MySQL Example
// Display the results in a table
print "<table><caption> <h2> Query Results </h2> </caption>";
print "<tr align = 'center'>";
// Get the number of rows in the result, as well as the first row
// and the number of fields in the rows
$num_rows = mysql_num_rows($result);
print "Number of rows = $num_rows <br />";
$row = mysql_fetch_array($result);
$num_fields = mysql_num_fields($result);
// Produce the column labels
$keys = array_keys($row);
PHP/MySQL Example
for ($index = 0; $index < $num_fields; $index++)
print "<th>" . $keys[2 * $index + 1] . "</th>”;
print "</tr>”;
// Output the values of the fields in the rows
for ($row_num = 0; $row_num < $num_rows; $row_num++) {
print "<tr align = 'center'>”;
$values = array_values($row);
for ($index = 0; $index < $num_fields; $index++){
$value = htmlspecialchars($values[2 * $index + 1]);
print "<th>" . $value . "</th> ”;
}
print "</tr>";
$row = mysql_fetch_array($result);
}
PHP/MySQL Example
print "</table>”;
?>
</body>
</html>
ADO .NET
ADO .NET
ADO.NET is the database API for managed applications
(application servers) to talk to database servers (DBMS:
Database Management Systems).
a database API for managed applications;
a set of classes in .NET FCL System.Data namespace;
designed to work over the Web;
integrates effortlessly with XML;
maps very well to stateless, text-based protocol HTTP;
accesses databases through modules known as data
providers ( a set of APIs that make the accesses easy to
program).
Two Data Providers
1. The SQL Server .NET provider
interfaces to Microsoft SQL Server (7.0 or later)
all managed code
code runs faster
code not portable to other databases
2. The OLE DB .NET provider
OLE: Object Linking and Imbedding
interfaces to databases through unmanaged OLE
DB providers: SQLOLEDB for SQL Server (6.5 or
earlier), MSDAORA for Oracle and Microsoft,
Jet.OLEDB.4.0 for Microsoft Jet database engine.
code runs slower
code portable to other databases
The System.Data.SqlClient and System.Data.OleDb Namespaces
Classes in System.Data.SqlClient are for SQL Server .NET
using System.Data.SqlClient;
SqlConnection conn = new SqlConnection
("server=localhost;database=pubs;uid=sa;pwd=");
try { conn.Open ();
SqlCommand cmd = new SqlCommand ("select * from titles",
conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.Read ()) Console.WriteLine (reader["title"]);
} catch (SqlException ex) {
Console.WriteLine (ex.Message);
} finally { conn.Close (); }
The System.Data.SqlClient and System.Data.OleDb Namespaces
Classes in System.Data.OleDb are for OLE DB .NET
using System.Data.OleDb;
OleDbConnection conn = new
OleDbConnection("provider=sqloledb;server=localhost;dat
abase=pubs;uid=sa;pwd=");
try { conn.Open ();
OleDbCommand cmd =
new OleDbCommand ("select * from titles", conn);
OleDbDataReader reader = cmd.ExecuteReader ();
while (reader.Read ()) Console.WriteLine (reader["title"]);
} catch (OleDbException ex) {
Console.WriteLine (ex.Message);
} finally { conn.Close (); }
Pattern of database programming
Create a connection object.
Open the connection.
Create a command object.
Execute the command.
Access the data.
Close the connection.
Connections, Commands, and DataReaders
Connection objects represent physical connections to a
database.
SqlConnection or OleDbConnection
Command objects represent the commands performed
on a database.
SqlCommand or OleDbCommand
DataReader objects represent the data obtained by the
commands.
SqlDataReader or OleDbDataReader
Connection Objects
The SqlConnection Class
The ConnectionString
SqlConnection conn = new SqlConnection ();
conn.ConnectionString =
"server=localhost;database=pubs;uid=sa;pwd=";
or
SqlConnection conn = new SqlConnection
("server=localhost;database=pubs;uid=sa;pwd=");
Errors in the connection string only throws exceptions at
runtime.
Server
Server
Server=localhost or Server=(local) or Data Source=(local)
SQL Server permits different instances of servers to be
installed on a given machine.
server=db1 (an database server computer named “db1” at
the CS department of UA)
server=hawkeye\wintellect (an instance of SQL Server
named Wintellect on a remote machine named Hawkeye)
Database or Initial Catalog: database name (e.g. Pubs)
UID or User ID, Pwd: tempdb, tempdb
Server
Min Pool Size and Max Pool Size, the size of the
connection pool (the defaults are 0 and 100)
Integrated Security: default to false, otherwise uses
Windows access tokens for authentication.
Connect Timeout: how many seconds to wait for a
connection to open (default=15).
SqlConnection conn = new SqlConnection
("server=hawkeye\wintellect;database=pubs;uid=sa;pwd=;"
+
"min pool size=10;max pool size=50;connect timeout=10");
Exceptions and Closing Open Connections
Exceptions should never go uncaught, and open
connections should always be closed before terminating.
(Calling Close on a connection that’s not open isn’t
harmful.)
SqlConnection conn = new SqlConnection
("server=localhost;database=pubs;uid=sa;pwd="); //before try block
try {conn.Open ();
// TODO: Use the connection
}
catch (SqlException e) {
Console.WriteLine (e.Message);
// TODO: Handle the exception
}
finally { conn.Close ();}
Command Classes: SqlCommand and OleDbCommand.
– Encapsulate SQL commands performed on a database.
– Rely on connections established.
– Include methods to execute the commands
encapsulated inside.
Example, delete a record from the Pubs database’s “Titles”
table using an SQL DELETE command:
SqlCommand cmd = new SqlCommand
("delete from titles where title_id = 'BU1032'", conn);
cmd.CommandTimeout = 10; // Allow 10 seconds, default 30.
cmd.ExecuteNonQuery (); // Execute the command
The ExecuteNonQuery Method
For executing DML and DDL commands: CREATE, INSERT,
UPDATE, DELETE, …
Not getting any data back.
Examples:
SqlCommand cmd = new SqlCommand
("create database MyDatabase", conn);
cmd.ExecuteNonQuery ();
SqlCommand cmd = new SqlCommand
("create table titles …", conn);
cmd.ExecuteNonQuery ();
SqlCommand cmd = new SqlCommand
("insert into titles (title_id, title, type, pubdate) " +
"values ('JP1001', 'Programming Microsoft .NET', " +
"'business', 'May 2002')", conn);
cmd.ExecuteNonQuery ();
The ExecuteNonQuery Method
SqlCommand cmd = new SqlCommand
("update titles set title_id = 'JP2002' " +
"where title_id = 'JP1001'", conn);
cmd.ExecuteNonQuery ();
SqlCommand cmd = new SqlCommand
("delete from titles where title_id = 'JP2002'", conn);
cmd.ExecuteNonQuery ();
The ExecuteScalar Method
Executes a query command and returns a single value in the
result set, such as COUNT, AVG, MIN, MAX, and SUM.
SqlCommand cmd = new SqlCommand
("select min (price) from titles", conn);
decimal amount = (decimal) cmd.ExecuteScalar ();
Console.WriteLine ("ExecuteScalar returned {0:c}", amount);
The ExecuteScalar Method
Another common use for ExecuteScalar is to retrieve
BLOBs (binary large objects) from databases.
For example, retrieving an image from the “Logo” field of the
Pubs database’s “Pub_info” table and encapsulates it in a
bitmap:
use System.IO;
use System.Drawing;
use System.Data.SqlClient;
SqlCommand cmd = new SqlCommand
("select logo from pub_info where pub_id='0736'", conn);
byte[] blob = (byte[]) cmd.ExecuteScalar ();
stream.Write (blob, 0, blob.Length);
Bitmap bitmap = new Bitmap (stream);
stream.Close ();
Write a BLOB to a database.
FileStream stream = new FileStream("Logo.jpg",
FileMode.Open);
byte[] blob = new byte[stream.Length];
stream.Read (blob, 0, (int) stream.Length);
stream.Close ();
SqlCommand cmd = new SqlCommand
("insert into pub_info (pub_id, logo) values ('9937', @logo)",
conn);
cmd.Parameters.Add ("@logo", blob);
cmd.ExecuteNonQuery ();
The ExecuteReader Method
For performing database queries and obtain the results as
quickly and efficiently as possible.
Returns a DataReader object.
Pulls back only the data to be “Read” by the DataReader
not all records satisfying the query condition.
SqlCommand cmd = new SqlCommand ("select * from titles", conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.Read ()) Console.WriteLine (reader["title"]);
Each call to “Read” returns one row from the result set.
It uses a property indexer to extract the value of the
record’s “title” field.
Fields can be referenced by name or by numeric index (0based).
DataReader
Reads data.
Reads schema (meta data) .
Stream-based access to the results of database queries.
Fast and efficient.
Read-only and forward-only.
Closing a DataReader: reader.Close( )
does NOT close the connection, only frees it for others
to use.
D-E-F-E-N-S-I-V-E P-R-O-G-R-A-M-M-I-N-G.
DataSets
Set-based Database Accesses
capture an entire query in memory
support backward and forward traversal
edit data and propagate the changes back to the
database.
DataSet, DataTable and DataAdapter
.NET supports set-based database accesses
through three classes:
DataSet: equivalent of an in-memory database.
It consists of a collection of DataTables.
DataTables are created by a DataAdapter
(SqlDataAdapter and OleDbDataAdapter).
DataSet doesn’t interact with databases directly.
DataAdapter reads the physical data sources and fills
DataTables and DataSets
DataSets vs. DataReaders
To simply query a database and read through the records
one at a time until you find the one you’re looking for, then
DataReader is the right tool. DataReaders (1) retrieve
only the data that you actually use, and (2) they don’t
consume memory by not storing every record that you
read, but (3) they can’t iterate backward.
To use all the query results and to iterate backward and
forward through a result set, or to cache the result set in
memory, use a DataSet.
Many controls that support DataSets are perfectly capable
of binding to DataReaders.
DataGrid (GUI)
• DataGrid is an ASP control for displaying
datasets.
• Database displaying procedure:
– Use DataAdapter to get data from the
database.
– Fill the data into a DataSet
– Bind the DataSet to a DataGrid
– Select the fields (columns) to be displayed and
their header texts.
Example:
DataAdapter, DataSet and DataGrid (GUI)
<asp:DataGrid ID="MyDataGrid"
OnItemCommand="OnItemCommand" RunAt="server">
<Columns>
<asp:BoundColumn HeaderText="Title"
DataField="title" />
<asp:BoundColumn HeaderText="Price"
DataField="price" DataFormatString="{0:c}"/>
<asp:ButtonColumn HeaderText="Action"
Text="Add to Cart" CommandName="AddToCart" />
</Columns>
</asp:DataGrid>
Examples/C9/Congo-MySQL/ViewCart.aspx
Example:
DataAdapter, DataSet and DataGrid (GUI)
void Page_Load (Object sender, EventArgs e)
{
if (!IsPostBack) {
string ConnectString =
ConfigurationSettings.AppSettings["connectString"];
MySqlDataAdapter adapter = new MySqlDataAdapter
("select * from titles where price != 0", ConnectString);
DataSet ds = new DataSet ();
adapter.Fill (ds);
MyDataGrid.DataSource = ds;
MyDataGrid.DataBind ();//Bind data to GUI
}
}
Transaction Commands
A transaction is a logical unit of operations grouped
together.
If one of the operations fails, the others will fail (or be
rolled back).
Distributed transactions — transactions that span two or
more databases.
The .NET Framework supports distributed transactions.
The .NET supports local transactions (one database):
Transacted Commands
// Start a local transaction
trans = conn.BeginTransaction (IsolationLevel.Serializable);
// Create and initialize a SqlCommand object
SqlCommand cmd = new SqlCommand ();
cmd.Connection = conn;
cmd.Transaction = trans;
// Debit $1,000 from account 1111
cmd.CommandText = "update accounts set balance = " +
"balance - 1000 where account_id = '1111'";
cmd.ExecuteNonQuery ();
// Credit $1,000 to account 2222
cmd.CommandText = "update accounts set balance = " +
"balance + 1000 where account_id = '2222'";
cmd.ExecuteNonQuery ();
// Commit the transaction (commit changes)
trans.Commit ();
Transacted Commands
IsolationLevel.Serializable locks down the records while
they’re updated so that they can’t be read or written.
Committing the transaction writes the changes to the
database.
Uses DataGrid to represent a DataSet in XML
DataSet ds = new DataSet ();
ds.ReadXml (Server.MapPath ("Bonuses.xml"));
MyDataGrid.DataSource = ds;
Summary
Database Programming: part of the architecture of a multitier application.
DB Programming API
PHP DB Code
ADO.NET
Data Providers (SQLServer Provider, OLEDB Provider)
Connection
Commands (NonQuery, Scaler)
DataReader
DataSet
DataAdapter
DataGrid
Transaction