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 user’s browser as HTML). Users don’t 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 they’re 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 Oracle’s 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: CakePHP’s 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…
– OWASP’s 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