Chapter 9 Application Design and Development n Application

Application Programs and User Interfaces n Most database users do not use a query

Application Architecture Evolution n Three distinct era’s of application architecture l mainframe (1960’s and

Web Interface n Web browsers have become the de-facto standard user interface to databases

The World Wide Web n The Web is a distributed information system based on

HTML n Most Web documents are hypertext documents formatted via the Hyper. Text Markup

Sample HTML Source Text <html> <body> <table border> <th>ID</th> <th>Name</th> <th>Department</th> </tr> <td>00128</td> <td>Zhang</td>

Display of Sample HTML Source Database System Concepts - 6 th Edition 9. 8

Web Servers n A Web server accepts requests from a Web browser and sends

Mostly-used Web Architecture n Application program runs within the Web server. Database System Concepts

HTTP and Sessions n The HTTP protocol is connectionless l That is, once the

Sessions and Cookies n A cookie is a small piece of text containing identifying

※ Servlets n Java Servlet specification defines an API for communication between the Web/application

※ Servlet Sessions n Servlet API supports handling of sessions l Sets a cookie

Server-Side Scripting n Server-side scripting simplifies the task of connecting a database to the

Java Server Pages (JSP) n A JSP page with embedded Java code n Example:

PHP n PHP is widely used for Web server scripting n Extensive libaries including

Client Side Scripting n Browsers can fetch certain scripts (client-side scripts) or programs along

Javascript n Javascript very widely used n Javascript functions can l check input for

Javascript n Example of Javascript used to validate form input <html> <head> <script type="text/javascript">

Client Side Scripting and Security n Security mechanisms needed to ensure that malicious scripts

Rapid Application Development n A lot of effort is required to develop Web application

ASP. NET and Visual Studio n ASP. NET provides a variety of controls that

Report Generators n Report generators are tools to generate human-readable summary reports from a

Improving Web Server Performance n Performance is an issue for popular Web sites l

Application Security-SQL Injection n SQL injection l The attacker manages to get an application

SQL Injection (cont) n As another example, suppose the user types: l X’; update

Password Leakage n Never store passwords, such as database passwords, in clear text in

Application-Level Authorization n Current SQL standard does not allow fine-grained authorization such as “students

Application-Level Authorization (Cont. ) n Currently, authorization is done entirely in application n Entire

Audit Trails n Applications must log actions to an audit trail, to detect who

Encryption in Databases n Database widely support encryption database authorization provisions do not offer

Slides: 32

Download presentation

Chapter 9: Application Design and Development n Application Programs and User Interfaces n Web Fundamentals n Servlets and JSP n Rapid Application Development n Application Performance n Application Security Database System Concepts - 6 th Edition 9. 1

Application Programs and User Interfaces n Most database users do not use a query language like SQL n An application program acts as the intermediary between users and the database l Applications split into 4 front-end 4 middle layer: containing “business logic”, which execute specific requests, enforcing rules, etc. – Example: 選課不能衝堂, 領錢後帳戶餘額不能少於 1000元 4 backend: communicating with a database n Front-end: user interface l Forms l Graphical user interfaces l Many interfaces are Web-based Database System Concepts - 6 th Edition 9. 2

Application Architecture Evolution n Three distinct era’s of application architecture l mainframe (1960’s and 70’s) l personal computer era (1980’s) l We era (1990’s onwards) Database System Concepts - 6 th Edition 9. 3

Web Interface n Web browsers have become the de-facto standard user interface to databases l Enable large numbers of users to access databases from anywhere l Avoid the need for downloading/installing specialized code, while providing a good graphical user interface 4 l Javascript, Flash and other scripting languages run in browser, but are downloaded transparently Examples: banks, airline and rental car reservations, university course registration and grading, an so on. Database System Concepts - 6 th Edition 9. 4

The World Wide Web n The Web is a distributed information system based on hypertext. n In the Web, functionality of pointers is provided by Uniform Resource Locators (URLs). n URL example: http: //www. acm. org/sigmod l The first part indicates how the document is to be accessed 4 “http” indicates that the document is to be accessed using the Hyper Text Transfer Protocol. l The second part gives the unique name of a machine on the Internet. l The rest of the URL identifies the document within the machine. n The local identification can be: 4 The path name of a file on the machine, or 4 An identifier (path name) of a program, plus arguments to be passed to the program – E. g. , http: //www. google. com/search? q=silberschatz Database System Concepts - 6 th Edition 9. 5

HTML n Most Web documents are hypertext documents formatted via the Hyper. Text Markup Language (HTML). n HTML documents contain l formatting: text along with font specifications, and other instructions including tables, stylesheets (to alter default formatting), and image display features etc. l hypertext links to other documents, which can be associated with regions of the text. l forms, enabling users to enter data which can then be sent back to the Web server, to be acted upon by an executable at the server, 4 which can generate Web documents dynamically based on user information n Input features provided by HTML 4 Select from a set of options – Pop-up menus, radio buttons, check lists 4 Enter values – Text boxes Database System Concepts - 6 th Edition 9. 6

Sample HTML Source Text <html> <body> <table border> <th>ID</th> <th>Name</th> <th>Department</th> </tr> <td>00128</td> <td>Zhang</td> <td>Comp. Sci. </td> </tr> …. </table> <form action="Person. Query" method=get> Search for: <select name="persontype"> <option value="student" selected>Student </option> <option value="instructor"> Instructor </option> </select> Name: <input type=text size=20 name="name"> <input type=submit value="submit"> </form> </body> </html> Database System Concepts - 6 th Edition 9. 7

Display of Sample HTML Source Database System Concepts - 6 th Edition 9. 8

Web Servers n A Web server accepts requests from a Web browser and sends back results in the form of HTML documents. n The document name in a URL may identify an executable program, that, when run, generates a HTML document. l The Web client can pass extra arguments with the name of the document. n A Web server can act as an intermediary to provide access to a variety of information services. l To install a new service on the Web, one simply needs to create and install an executable that provides that service. l The Web browser provides a graphical user interface to the information service. n Common Gateway Interface (CGI): a standard defines how the Web server communicates with application programs. Database System Concepts - 6 th Edition 9. 9

Mostly-used Web Architecture n Application program runs within the Web server. Database System Concepts - 6 th Edition 9. 10

HTTP and Sessions n The HTTP protocol is connectionless l That is, once the server replies to a request, the server closes the connection with the client, and forgets all about the request 4 Motivation: reduces load on server 4 operating systems have tight limits on number of open connections on a machine l In contrast, Unix logins, and JDBC/ODBC connections stay connected until the client disconnects 4 retaining user authentication and other information n Information services need session information l E. g. , user authentication should be done only once per session n Solution: use a cookie Database System Concepts - 6 th Edition 9. 11

Sessions and Cookies n A cookie is a small piece of text containing identifying information l Sent by server to browser 4 Sent l Sent by browser to the server that created the cookie on further interactions 4 part l on first interaction, to identify session of the HTTP protocol Server saves information about cookies it issued, and can use it when serving a request 4 E. g. , authentication information, and user preferences n To track a user session, an application may generate a session identifier, and send a cookie containing the session identifier. n Cookies can be stored permanently or for a limited time. Database System Concepts - 6 th Edition 9. 12

※ Servlets n Java Servlet specification defines an API for communication between the Web/application server and application program running in the server l E. g. , methods to get parameter values from Web forms, and to send HTML text back to client n Application program (also called a servlet) is loaded into the server (see text for the sample code) n Servlets run inside application servers such as l Apache Tomcat, IBM Web. Sphere and Oracle Application Servers n Application servers support l deployment and monitoring of servlets l Java 2 Enterprise Edition (J 2 EE) platform supporting objects, parallel processing across multiple application servers, etc Database System Concepts - 6 th Edition 9. 13

※ Servlet Sessions n Servlet API supports handling of sessions l Sets a cookie on first interaction with browser, and uses it to identify session on further interactions n To check if session is already active: l if (request. get. Session(false) == true) 4. . then existing session 4 else l . . redirect to authentication page 4 check login/password 4 request. get. Session(true): creates new session n Store/retrieve attribute value pairs for a particular session l session. set. Attribute(“userid”, userid) l session. get. Attribute(“userid”) Database System Concepts - 6 th Edition 9. 14

Server-Side Scripting n Server-side scripting simplifies the task of connecting a database to the Web l Define an HTML document with embedded executable code/SQL queries. l Input values from HTML forms can be used directly in the embedded code/SQL queries. l When the document is requested, the Web server executes the embedded code/SQL queries to generate the actual HTML document. n Numerous server-side scripting languages l JSP, PHP, ASP (ASP. net) l General purpose scripting languages: VBScript, Perl, Python Database System Concepts - 6 th Edition 9. 15

Java Server Pages (JSP) n A JSP page with embedded Java code n Example: <html> <head> <title> Hello </title> </head> <body> <% if (request. get. Parameter(“name”) == null) { out. println(“Hello World”); } else { out. println(“Hello, ” + request. get. Parameter(“name”)); } %> </body> </html> n JSP scripts are translated into servlets that are then compiled. Database System Concepts - 6 th Edition 9. 16

PHP n PHP is widely used for Web server scripting n Extensive libaries including for database access using ODBC n Example: <html> <head> <title> Hello </title> </head> <body> <? php if (!isset($_REQUEST[‘name’])) { echo “Hello World”; } else { echo “Hello, ” + $_REQUEST[‘name’]; } ? > </body> </html> Database System Concepts - 6 th Edition 9. 17

Client Side Scripting n Browsers can fetch certain scripts (client-side scripts) or programs along with documents, and execute them in “safe mode” at the client site l Javascript l Macromedia Flash and Shockwave for animation/games l VRML l Applets (Java programs) n Client-side scripts/programs allow documents to be active l E. g. , animation by executing programs at the local site l E. g. , ensure that values entered by users satisfy some correctness checks l Permit flexible interaction with the user. 4 Executing programs at the client site speeds up interaction by avoiding many round trips to server Database System Concepts - 6 th Edition 9. 18

Javascript n Javascript very widely used n Javascript functions can l check input for validity l modify the displayed Web page, by altering the underling document object model (DOM) tree representation of the displayed HTML text l communicate with a Web server to fetch data and modify the current page using fetched data, without needing to reload/refresh the page 4 forms basis of AJAX technology used widely in Web 2. 0 applications 4 E. g. on selecting a country in a drop-down menu, the list of states in that country is automatically populated in a linked drop-down menu Database System Concepts - 6 th Edition 9. 19

Javascript n Example of Javascript used to validate form input <html> <head> <script type="text/javascript"> function validate() { var credits=document. get. Element. By. Id("credits"). value; if (is. Na. N(credits)|| credits<=0 || credits>=16) { alert("Credits must be a number greater than 0 and less than 16"); return false } } </script> </head> <body> <form action="create. Course" onsubmit="return validate()"> Title: <input type="text" id="title" size="20"> Credits: <input type="text" id="credits" size="2"> <Input type="submit" value="Submit"> </form> </body> </html> Database System Concepts - 6 th Edition 9. 20

Client Side Scripting and Security n Security mechanisms needed to ensure that malicious scripts do not cause damage to the client machine l Easy for limited capability scripting languages, harder for general purpose programming languages like Java n E. g. , Java’s security system ensures that the Java applet code does not make any system calls directly l Disallows dangerous actions such as file writes l Notifies the user about potentially dangerous actions, and allows the option to abort the program or to continue execution. Database System Concepts - 6 th Edition 9. 21

Rapid Application Development n A lot of effort is required to develop Web application interfaces more so, to support rich interaction functionality associated with Web 2. 0 applications n Several approaches to speed up application development l Function library to generate user-interface elements l Drag-and-drop features in an IDE to create user-interface elements l Automatically generate code for user interface from a declarative specification n Above features have been used as part of rapid application development (RAD) tools even before advent of Web l n Web application development frameworks Java Server Faces (JSF) includes JSP tag library l Ruby on Rails 4 Allows easy creation of simple CRUD (create, read, update and delete) interfaces by code generation from database schema or object model l Database System Concepts - 6 th Edition 9. 22

ASP. NET and Visual Studio n ASP. NET provides a variety of controls that are interpreted at server, and generate HTML code n Visual Studio provides drag-and-drop development using these controls l E. g. menus and list boxes can be associated with Data. Set object l Validator controls (constraints) can be added to form input fields 4 Server creates HTML code combined with Java. Script to perform the validation at the user’s browser. l User actions such as selecting a value from a menu can be associated with actions at server l Data. Grid provides convenient way of displaying SQL query results in tabular format Database System Concepts - 6 th Edition 9. 23

Report Generators n Report generators are tools to generate human-readable summary reports from a database. They integrate querying the database with the creation of formatted text and summary charts. n Tools l Crystal Reports, Microsoft (SQL Server Reporting Services) Database System Concepts - 6 th Edition 9. 24

Improving Web Server Performance n Performance is an issue for popular Web sites l May be accessed by millions of users every day, thousands of requests per second at peak time n Caching techniques used to reduce cost of serving pages by exploiting commonalities between requests l At the server site: 4 Caching of JDBC connections between servlet requests – E. g. , connection pooling: » 4 Caching The server creates a pool of open ODBC/JDBC connection results of database queries – Cached results must be updated if underlying database changes 4 Caching l of generated HTML At the client’s network 4 Caching Database System Concepts - 6 th Edition of pages by Web proxy 9. 25

Application Security-SQL Injection n SQL injection l The attacker manages to get an application to execute an SQL query created by the attacker. l It can steal data or damage the database. n Suppose a query is constructed using l "select * from instructor where name = ’" + name + "’" n Suppose the user, instead of entering a name, enters: l X’ or ’Y’ = ’Y n the resulting statement becomes: l "select * from instructor where name = ’" + "X’ or ’Y’ = ’Y" + "’" l which is: 4 select l * from instructor where name = ’X’ or ’Y’ = ’Y’ The where clause is always true and the entire instructor relation is returned. Database System Concepts - 6 th Edition 9. 26

SQL Injection (cont) n As another example, suppose the user types: l X’; update instructor set salary = salary + 10000; -- n It causes the following effects: l The quote inserted by the attacker closes the string l The following semicolon terminates the query. l The following text inserted by the attacker gets interpreted as a second query, l The closing quote has been commented out. n To avoid such attacks, it is best to use prepared statements to execute SQL queries. (see Ch 5) n When setting a parameter of a prepared query, JDBC (ODBC) automatically adds escape characters so that the user-supplied quote would no longer be able to terminate the string. n For the example in the previous page, prepared statement internally uses: "select * from instructor where name = ’X’ or ’Y’ = ’Y’ n Always use prepared statements, with user inputs as parameters Database System Concepts - 6 th Edition 9. 27

Password Leakage n Never store passwords, such as database passwords, in clear text in scripts that may be accessible to users l E. g. in files in a directory accessible to a web server 4 Normally, web server will execute, but not provide source of script files such as file. jsp or file. php, but source of editor backup files such as file. jsp~, or. file. jsp. swp may be served n Restrict access to database server from IPs of machines running application servers l Most databases allow restriction of access by source IP address Database System Concepts - 6 th Edition 9. 28

Application-Level Authorization n Current SQL standard does not allow fine-grained authorization such as “students can see their own grades, but not other’s grades” l Problem 1: Database has no idea who are application users l Problem 2: SQL authorization is at the level of tables, or columns of tables, but not to specific rows of a table n One workaround: use views such as create view student. Takes as select * from takes where takes. ID = syscontext. user_id() l where syscontext. user_id() provides end user identity 4 end user identity must be provided to the database by the application l Having multiple such views is cumbersome Database System Concepts - 6 th Edition 9. 29

Application-Level Authorization (Cont. ) n Currently, authorization is done entirely in application n Entire application code has access to entire database l large surface area, making protection harder n Alternative: fine-grained (row-level) authorization schemes l extensions to SQL authorization proposed but not currently implemented l Oracle Virtual Private Database (VPD) allows predicates to be added transparently to all SQL queries, to enforce fine-grained authorization 4 e. g. add ID= sys_context. user_id() to all queries on student relation if user is a student Database System Concepts - 6 th Edition 9. 30

Audit Trails n Applications must log actions to an audit trail, to detect who carried out an update, or accessed some sensitive data n Audit trails used after-the-fact to l detect security breaches l repair damage caused by security breach l trace who carried out the breach n Audit trails needed at l Database level, and at l Application level Database System Concepts - 6 th Edition 9. 31

Encryption in Databases n Database widely support encryption database authorization provisions do not offer sufficient protection. n Different levels of encryption: l disk block 4 every disk block encrypted using key available in databasesystem software. 4 Even if attacker gets access to database data, decryption cannot be done without access to the key. l Entire relations, or specific attributes of relations 4 non-sensitive relations, or non-sensitive attributes of relations need not be encrypted 4 however, attributes involved in primary/foreign key constraints cannot be encrypted. n Storage of encryption or decryption keys l typically, single master key used to protect multiple encryption/decryption keys stored in database n Alternative: encryption/decryption is done in application, before sending values to the database l Database System Concepts - 6 th Edition 9. 32