[SOLVED] CS JDBC Java database interpreter case study file system SQL python DB Connectivity Web Technology

DB Connectivity Web Technology

Where Are We
Through this unit we have looked at:
The fundamental principles on which relational databases are built
Designing a relational database and
Implementing a relational database and manipulating its data via
SQL
In practice the database you create & populate will be used by normal users not database professionals
set of tables/views created under one account control access to this accounts objects
2

Database connectivity
4

5
Q1. The interface between an application program and the database, is known as
a. SQL
b. Database Middleware
c. The Data Layer
d. A Client Side Extension
e. Data Access Objects

Database Connectivity
The DATA LAYER your data management application (DBMS)

The DATABASE MIDDLEWARE manages connectivity and data transformation issues. Many options available such as:
Native SQL Connectivity
Vendor provided eg. Oracle SQL*Net
Microsoft ODBC, DAO, RDO; OLE-DB and ADO.NET
Java Database Connectivity (JDBC)
The APPLICATION the external interface, mostly in the form of an Application Programming Interface (API)

6

Coronel & Morris Fig 15.2 Ed 13
7

Coronel & Morris Fig 15.5 Ed 13
8

Coronel & Morris Fig 15.7 Ed 13
9

SQLDeveloper JDBC
10

Sample JDBC code snippet
Oracle JDBC Tutorial
https://goo.gl/p1bl2b
Oracle Python Tutorial
https://www.oracletutorial.com /python-oracle/
11

Placing application logic in the backend
In this approach we code database objects which black box the logic and store them in the database
Procedures and Packages
written using PL/SQL a mixture of
a procedural language and SQL
called by invoking package name
and handing parameters add_booking (.)
12

Database connectivity web technology
13

14
Q2. A server-side extension is
a. part of web server which allows it to be used across many hosts
b. is necessary to access a web server from a mobile device
c. a program that interacts directly with the web server to handle specific types of requests
d. interacts directly with a client-side extension
e. a vendor specific approach to accessing a database across the internet

Coronel & Morris Fig 15.8 Ed 13
15

Web Database Development

Creating web pages which access data in a database. Many options available, including
ColdFusion Uses CFML https://goo.gl/7FnYgi or http://openbd.org/ PHP http://php.net/
Oracle Application Express (Apex)
https://apex.oracle.com/en/
16

TIOBE Index for May 2020
https://www.tiobe.com/tiobe-index/
17

PHP Basic
18

PHP Basic Case Study
PHP language server-side
PHP-enabled web pages https://www.php.net/manual/en/tutorial.php Commonly used in combination / part of frameworks (more later)
PHP software needs to be alongside web server software
e.g. besides Apache in LAMP stacks https://en.wikipedia.org/wiki/LAMP_(software_bundle); or PHP on IIS https://php.iis.net/
Further reading on PHP What can PHP do?
https://www.php.net/manual/en/intro-whatcando.php
19

PHP Basic Case Study
Quick synopsis
When a PHP page is accessed, PHP interpreter living in the server produces output, which is served to the user (commonly interpreted in the users browser as HTML). Users dont see the raw PHP code.
when PHP is installed, the web server is configured to expect certain file extensions to contain PHP language statements. When the web server gets a request for a file with the designated extension, it sends the HTML statements as is, but PHP statements are processed by the PHP software before theyre sent to the requester When PHP language statements are processed, only the output, or anything printed to the screen is sent by the web server to the web browser.
Source: Suehring & Valade. Read the full article:
https://www.dummies.com/programming/php/how-php-works/
20

21
Q3. PHP is
a. a piece of software which lives on the server
b. an RDBMS library itself
c. a programming/scripting language
d. owned by Oracle
e. all of (a, b, c)
f. both (a, c)
g. both (c, d)

Example: Web Server and PHP
22

PHP Database Access
PHP interacts with Oracle.
Interaction via Oracle OCI 8 functions
Recommended reading: https://php.net/manual/en/book.oci8.php Other RDBMS examples: PHP interacts with
MySQL/MariaDB with mysql_connect() https://www.tutorialspoint.com/mariadb/mariadb_connection.htm
Definition: OCI8 is the PHP extension for connecting to Oracle Database. OCI8 is open source and included with PHP. The name is derived from Oracles C call interface API first introduced in version 8 of Oracle Database. OCI8 links with Oracle client libraries, such as Oracle Instant Client.
23

Practical considerations and security
25

Use of Frameworks
Earlier we discussed the fact that PHP is used within many frameworks So what are frameworks?
A web framework (WF) is a software framework that is designed to support the development of web applications
[they] provide a standard way to build and deploy web applications on the World Wide Web automate the overhead associated with common activities performed in web development.
[e.g.] provide libraries for database access
https://en.wikipedia.org/wiki/Web_framework
Trends in 2020 see e.g.
https://hackr.io/blog/top-10-web-development-frameworks-in-2020
26

Frameworks, Oracle Support, ORM
Many frameworks support Oracle connectivity. Examples:
Django https://docs.djangoproject.com/en/2.2/ref/databases/
Node.js https://www.oracle.com/au/database/technologies/appdev/nodejs.html
CakePHP https://github.com/CakeDC/cakephp-oracle-driver
Symfony https://symfony.com/doc/current/doctrine.html
Object-Relational Mapping (ORM) helps make it easy to write code
A short definition: Object-Relational Mapping is a technique that lets you query
and manipulate data from a database using an object-oriented paradigm.
Reference: https://blog.yellowant.com/orm-rethinking-data-as-objects-8ddaa43b1410
Shorter example: CakePHPs ORM maps a DB row to an object in your
programming language of choice (e.g. $article in CakePHP)
so you can use the object directly to access its attributes e.g.
$article->title
27

SQL Injection Example
SQL Injection demo
28

Security Considerations
Databases, especially when they are user-facing (web apps etc), are at risk of attacks over the web
OWASPs Top 10 list since 2010 to 2017 #1 is Injection
Read https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project
SQL injection is very common! Definition: quoted verbatim (OWASP)
A SQL injection attack consists of insertion or injection of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands. https://www.owasp.org/index.php/SQL_Injection
(OWASP: Open Web Application Security Project)
29

Security Considerations
Examples
simple ones illustrated in https://www.w3schools.com/sql/sql_injection.asp
Lessons:
Sanitise and check your input!
Configure your database to minimise the damage
restricted user least privileges
using views (Workshop 10)
Follow security best practices
e.g. OWASP
https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.md
e.g. for Oracle
Oracle Blog https://blogs.oracle.com/sql/what-is-sql-injection-and-how-to-stop-it
67-page whitepaper
https://www.oracle.com/assets/how-to-write-injection-proof-plsql-1-129572.pdf
30

31
Q4. Given the following SQL statement in the back-end:
SELECT name, company, phone FROM vendors
WHERE name = $variable;
What can go wrong if SQL is injected via $variable e.g. on a web form?
a. tables can be DROPped
b. ALTERations can be done to tables
c. vendor names can be UPDATED
d. potentially sensitive data e.g. logins in a secret table can be UNIONed
e. All of the above
f. None of the above