CSE 3241: Database Systems I Database Application
Programming (Ch. 13)
Outline
⚫ Database programming
⚫ Database programming via API calls
⚫ Embedding SQL in applications
⚫ Stored Procedures
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.
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
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
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
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
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
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
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
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
6. Create a ResultSet object
◦ Use the PreparedStatement object to execute a query
◦ Results of query stored in ResultSet
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.
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);
1. Import the JDBC SQL library
2. Load the JDBC driver 3. Create a Connection
object
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
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
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
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
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
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
Stored Procedures
⚫ Program modules, usually written in SQL
◦ stored in the database
◦ Executed on the database server
◦ Stored procedures – aka persistent stored
modules
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
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
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
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);
DBMS SECURITY AND SQL INJECTION
CHAPTER 24.4
Outline
⚫ SQL Injection statistics
⚫ Ethical Hacking
⚫ HOWTO
⚫ Tools and Best Practices
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
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.”
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. ”
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.”
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!!
https://en.wikipedia.org/wiki/SQL_injection#Examples
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)
44% increase in SQLi attacks in 2016 (vs 2015)
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”
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.”
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
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.
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.
SQL Injection
⚫ So what is SQL Injection?
http://example.com/show.php?userid=1234
SELECT * FROM User
WHERE user_id = $_GET[‘userid’]
user input
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
unintended logic!
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;
}
SQL Injection
⚫ So what is SQL Injection?
◦ If user = “johnbrown” and password = “ezpassword”:
String sql = “SELECT id FROM User WHERE user_id = ‘“ + user +
“’ AND passwd = ‘“ + password + “’;”;
◦ Becomes:
SELECT id FROM User
WHERE user_id = ‘johnbrown’ AND passwd = ‘ezpassword’;
◦ Exactly what the programmer intended
SQL Injection
⚫ So what is SQL Injection?
◦ BUT, if user = “admin’;-- ” and password =
“whatever”
String sql = “SELECT id FROM User WHERE user_id = ‘“ + user +
“’ AND passwd = ‘“ + password + “’;”;
◦ 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
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!
Injection Mitigation
⚫ Always use a PreparedStatement properly
◦ Never concatenate Strings in SQL statements
String sql = “SELECT id FROM User WHERE user_id = ‘“ + user +
“’ AND passwd = ‘“ + password + “’;”;
◦ 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);
