CSE 3241: Database Systems I Database Application Programming (Ch. 13)

(1)

CSE 3241: Database Systems I

Database Application Programming (Ch. 13)

1

(2)

Outline

 Database programming

 Database programming via API calls

 Embedding SQL in applications

 Stored Procedures

2

(3)

Database Programming

 Most database access is not via raw SQL queries

◦ Nobody expects users to write queries to place an order at Amazon, or to check their class schedule

 Most database access is via database applications

◦ Dedicated front-ends developed in a general purpose language

 C, C++, Java, etc.

◦ Or web-based applications written in an appropriate language

 PHP, JavaScript, VBScript, Java, etc.

3

(4)

Database Programming

 Approaches to application programming

◦ Use a library of functions to read/write to the database

 Uses a published API (application programming interface)

 Common approach for general purpose languages

◦ Use SQL directly in your application code

 May require special pre-processing (language dependent)

◦ Design a new language

 Oracle’s PL/SQL language

 Once a popular option, these days not so popular

 API approach is the most used approach these

days

⁴

(5)

Database Programming

 Differences between database model and programming

language model can cause problems

◦ Known as impedance mismatch

 Problem 1 – Data types

◦ Data types available to the

programming language may not match data types of data model

◦ Must have a binding between database data types and

programming language data types

5

(6)

Database Programming

 Problem 2 – Queries return multiple rows

◦ Must have a binding to map query result to a multi-valued data

structure in the programming language

◦ Must have a method of iterating over results in the programming language

 Commonly known as a cursor

 Iterates over a dynamic result from the database

6

(7)

Database Programming

 Typical operation – client/server model

◦ Client application communicates with one or more database servers

◦ Client opens a connection to the database

 Connects via the network, negotiates authentication

◦ Client submits one or more queries, updates, etc.

◦ Client closes the connection when

interaction is finished

⁷

(8)

Database Programming (API)

 Using an application

programming interface (API) is the most common approach to database programming

◦ API provides functionality for:

 Connecting/disconnecting databases

 Performing queries/updates

 Iterating over results

 Binding query results to language data types

8

(9)

API Example – Java JDBC

 Java Database Connectivity (JDBC) library

◦ Provides a standard library for

interacting with any RDBMS (known as a data source)

 Different RDBMS servers, different RDBMS vendors

◦ Each vendor writes a driver to act as an interface between the Java

language and the database

9

(10)

Java JDBC – Basic Usage (Query)

1. Import the JDBC SQL library

2. Load the JDBC driver

3. Create a Connection object

◦ Using the DriverManager, a

connection is established with the database

10

(11)

Java JDBC – Basic Usage (Query)

4. Create a PreparedStatement object

◦ PreparedStatement object created using the Connection

◦ Used to execute queries/updates

 Pass a string of SQL code to the

PreparedStatement object to execute a query/update

5. Create a ResultSet object

◦ Use the PreparedStatement object to execute a query

◦ Results of query stored in ResultSet

¹¹

(12)

Java JDBC – Basic Usage

6. Iterate over ResultSet

◦ Each element of ResultSet is a tuple from the database

◦ Individual elements of tuple accessed via

“get” methods

7. When finished, close the

PreparedStatement and the Connection

◦ Always close all ResultSets, Statements and the Connection!

 Not closing ResultSets and Statements

immediately after use is a common problem that leads to memory leaks and application

performance degredation.

¹²

(13)

Java JDBC – Example

import java.sql.*;

public class DBExample {

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

static final String DB_URL = "jdbc:mysql://localhost/EMP";

static final String USER = "username";

static final String PASS = "password";

public static void main(String[] args) { Connection conn = null;

PreparedStatement stmt = null;

ResultSet rSet = null;

try

{

Class.forName(JDBC_DRIVER);

System.out.println("Connecting to database...");

conn = DriverManager.getConnection(DB_URL,USER,PASS);

13

1. Import the JDBC SQL library

2. Load the JDBC driver

3. Create a

Connection

object

(14)

Java JDBC – Example

String sql = “SELECT first, last FROM Employees ” + “WHERE id = ?”;

stmt = conn.prepareStatement(sql);

stmt.setInt(1, 1235550987);

rSet = stmt.executeQuery();

while (rSet.next()) {

String first = rSet.getString("first");

String last = rSet.getString("last");

System.out.println(”Name: ”+ last +”,”+ first);

} }

catch (Exception ex) {

// handle errors ...

}

14

4. Create a Prepared Statement object

5. Create a ResultSet object

6. Iterate over

ResultSet

(15)

Java JDBC – Example

finally {

if(rSet != null) { rSet.close(); } if(stmt != null) { stmt.close(); } if(conn != null) { conn.close(); } }

} }

15

 Exception handling

 try, catch and finally

 finally block always gets executed, whether there’s an Exception or not

 Great place to make sure all ResultSets, PreparedStatements and Connections are closed



But remember to not throw an Exception when closing them!

7. When

finished, close the Prepared Statement, ResultSet and the

Connection

(16)

Database Programming (APIs)

 Java not the only language with standard SQL API

◦ Most high-level languages provide some kind of database API

 C – ODBC

 Python – DB-API

 Perl – DBI

◦ For your language, consult your language documentation

16

(17)

Alternative Approaches

 API framework not the only way to connect databases and

applications

◦ Embedded SQL – SQL queries embedded right into the code

◦ Not as popular these days as API framework

◦ But popular at the application level

 Spreadsheets and other data manipulation programs

17

(18)

Embedding SQL in Excel

 Spreadsheet applications very

common in business applications

◦ Data analysis

◦ Reporting

◦ Data collection

 Often need to transfer data from databases into spreadsheets

◦ Or from spreadsheets into databases

 Database connectivity built into all modern spreadsheet

applications

¹⁸

(19)

Embedding SQL in Excel

 Excel uses the Windows Data Sources Manager to manage connections

◦ Each database connection you use must be set up as a data source

◦ Can then pull data from the

database into the spreadsheet as a query from a data source

 Data can then be manipulated using any spreadsheet operations

 Data can be kept automatically refreshed – as the database is updated, the

spreadsheet is updated

19

(20)

Stored Procedures

 Program modules, usually written in SQL

◦ stored in the database

◦ Executed on the database server

◦ Stored procedures – aka persistent stored modules

20

(21)

Stored Procedures

 Useful in a number of circumstances:

◦ When a piece of code is used by several applications (possibly in multiple languages)

 Useful for keeping business logic consistent across applications

◦ When executing code on the server will reduce unnecessary data

transfer over the network

 Useful for speeding up application processing

21

(22)

Stored Procedures

 In databases that implement

stored procedures, created using CREATE PROCEDURE

 Procedure then defined using the language supported by the

RDBMS

 Generally a procedural language that allows embedded SQL

statements

22

(23)

Stored Procedures

 Once created, stored procedures can be called using a CALL

statement

◦ CALL [procedure name] (argument list)

◦ Procedure then executes

 Most RDBMSs that implement stored procedures also

implement stored functions

◦ As a stored procedure, but return a value instead of just executing a

series of statements

23

(24)

Stored Procedures – Example

PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL;

comm_missing EXCEPTION;

BEGIN -- executable part starts here

SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

IF bonus IS NULL THEN RAISE comm_missing;

ELSE

UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;

END IF;

EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN

...

END award_bonus;

 Execute the procedure with a CALL

CALL award_bonus(1235550987);

24

(25)

DBMS SECURITY AND SQL INJECTION

CHAPTER 24.4

25

(26)

Outline

 SQL Injection statistics

 Ethical Hacking

 HOWTO

 Tools and Best Practices

26

(27)

SQL Injection

 On April 19, 2011, Sony learned its networks had been compromised by hackers

 Services were disrupted for several weeks while Sony tried to piece together the

damage and patch the vulnerabilities.

 Overall, Sony estimated damages caused by the attack at around $170 million.

 The main attack vector used: SQL Injection

27

(28)

SQL Injection

 Around June, 2011, just 2 months later,

Sony was again the target of a SQL Injection attack

 This time, over 1 million more accounts were compromised

 The hacker group LulzSec claimed responsibility, saying:

◦ “SonyPictures.com was owned by a very simple SQL injection, … What’s worse is that every bit of data we took wasn’t

encrypted. Sony stored over 1,000,000 passwords of its customers in plaintext…

they were asking for it.”

₂₈

(29)

SQL Injection

 In July, 2012, over 400,000 user accounts and passwords to yahoo.com, gmail.com, aol.com via Yahoo! Voices

 The method for the compromise was apparently a SQL Injection

 The hacker group D33D claimed responsibility, saying:

◦ “We hope that the parties responsible for managing the security of this subdomain will take this as a wake-up call, and not as a threat… There have been many security holes exploited in webservers belonging to Yahoo! Inc. that have caused far greater damage than our disclosure. Please do not take them lightly. ”

29

(30)

SQL Injection



July 25, 2013, U.S. federal prosecutors indicted 5 people in a data- breach scheme affecting the computer networks of major retailers, financial institutions and payment processors including VISA,

NASDAQ, J.C. Penney, JetBlue and 7-Eleven.



Over 160 million credit and debit card numbers stolen and resold around the world.



SQL Injection formed a major part of the strategies of the hackers.



World News, in reporting the event, said in their article:

◦ “The companies named in the indictment are believed to have lost hundreds of millions of dollars because of this ring's actions, with three companies incurring losses in excess of $300 million alone, according to the indictment.

Losses to identity theft victims, who must deal with costs associated with the crimes, are considered

immeasurable.”

30

(31)

SQL Injection

 According to the Open Web

Application Security Project (OWASP), Injection attacks rank #1 on their

Vulnerability Top Ten 2013.

◦ Moved into #1 spot in 2010, depite having been “invented” back in the 1990s.

◦ Reoccuring theme:

 SQL Injection isn’t new. It isn’t hard. It hasn’t changed fundamentally.

 And yet, it still isn’t being defended effectively!!

31

https://en.wikipedia.org/wiki/SQL_injection#Examples

(32)

SQLi remains top web attack

 More than half (51.29%) of

web application attacks in the 4 ^th quarter of

2016 made use of SQL Injection (SQLi)

32

44% increase in SQLi attacks in 2016 (vs 2015)

(33)

Ethical Hacking

 What is SQL Injection?

◦ First a word about Ethical Hacking…

 Responsibilities of knowing right from wrong.

◦ If you don’t know what’s wrong, how do you keep from doing it?

◦ How do you know if others are doing it wrong?

◦ “White Hat” activities vs. “Black Hat”

33

(34)

Ethical Hacking

 The Computer Fraud and Abuse Act (1984)

◦ Revised in1986, 88, 89, 90, 94, 96

◦ Amended October 26, 2001 by USA PATRIOT legislation

“(a)(2) intentionally accesses a computer without authorization or exceeds

authorized access, and thereby obtains--”

“(b) Whoever attempts to commit an offense under subsection (a) of this

section shall be punished as provided in subsection (c) of this section.”

34

(35)

Ethical Hacking

 Bottom Line:

If you don’t have explicit, written permission to access a system

from the owners…

THEN DON’T

 It’s illegal, and will most likely end an otherwise promising career

35

(36)

Ethical Hacking

 Bottom Line:

◦ There are plenty of open source, freeware projects that you can run on your own

computer that will let you practice secure coding in a legal, safe manner.

36

(37)

SQL Injection

 So what is SQL Injection?

◦ When an attacker injects a string input through an application, which changes or manipulates the SQL statement to the

attacker’s advantage.

◦ Typically involves manipulation through

set operators (UNION, INTERSECT, MINUS,) logic operators (AND, OR, NOT) and SQL comments.

37

(38)

SQL Injection

 So what is SQL Injection?

http://example.com/show.php?userid=1234

SELECT * FROM User

WHERE user_id = $_GET[‘userid’]

38

user input

(39)

SQL Injection

 So what is SQL Injection?

http://example.com/show.php?userid=1234 OR TRUE

SELECT * FROM User

WHERE user_id = 1234 OR TRUE

39

unintended logic!

(40)

SQL Injection

 So what is SQL Injection?

// returns user id of logged in user, or -1 if bad password

public int doLogin(Connection conn, String user, String password) {

Statement stmt = conn.createStatement();

String sql = “SELECT id FROM User WHERE user_id = ‘“ + user +

“’ AND passwd = ‘“ + password + “’;”;

Result Set rs = stmt.executeQuery(sql);

if(rs.next()) {

return rs.getInt(1);

}

return -1;

}

40

(41)

SQL Injection

 So what is SQL Injection?

◦ If user = “johnbrown” and password =

“ezpassword”:

◦ Becomes:

SELECT id FROM User

WHERE user_id = ‘johnbrown’ AND passwd = ‘ezpassword’;

◦ Exactly what the programmer intended

41

(42)

SQL Injection

 So what is SQL Injection?

◦ BUT, if user = “admin’;-- ” and password =

“whatever”

◦ Becomes:

SELECT id FROM User

WHERE user_id = ‘admin’;-- ’ AND passwd = ‘whatever’;

◦ Always logs in as “admin” without checking password at all!

 “-- ” is the symbol for comments in MySQL database.

Everything after it is ignored.

⁴²

(43)

Injection Mitigation

 TRUST NO ONE!

◦ Any input coming from outside the server can be tainted.

 It’s easier to just be paranoid and check all input, but in reality, budget and time constraints may not allow full sanitizing of inputs.

 Prioritize sensitive data, rank accordingly

◦ JavaScript is running in a client browser and can be changed/manipulated, or discarded entirely!

◦ Even data coming from mobile platforms can be faked

 Remember: just because you wrote the client code doesn’t mean the attacker can’t break it

purposefully!

⁴³

(44)

Injection Mitigation

 Always use a PreparedStatement properly

◦ Never concatenate Strings in SQL statements

◦ Unsafe! Instead, use:

String sql = “SELECT id FROM User WHERE user_id=? AND passwd=?”;

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, user);

ps.setString(2, password);

◦ Although it is a few more lines, it prevents SQL Injection from happening for those parameters by using bind variables

44

(45)

Injection Mitigation

 Always use a

PreparedStatement properly

◦ PreparedStatements also allow reuse of cached statements in the DBMS

 Much faster, especially in loops of repeated operations with different parameters

 Can just reset the

PreparedStatement, supply new values and run again, without

causing the DBMS to recompile the SQL.

45

(46)

Injection Mitigation

 Always use a PreparedStatement properly

◦ PreparedStatements also allow reuse of cached statements in the DBMS

46

PreparedStatements can

reuse all of these steps

(47)

Injection Mitigation

 Whitelist all SQL inputs

◦ Only allow a certain set of characters as input, reject all else

◦ Versus Blacklisting

 Too easy to slip past the few blacklisted

characters, and easy to miss all possible cases of misused characters

◦ Example of whitelist rules in regular expressions:

[^a-z0-9]

 Only allows characters a-z and digits 0-9

47

(48)