Transcript 11-Libraries, pitfalls, stored procedures, triggersx

Libraries, pitfalls,
stored
procedures,
triggers
CMSC 461
Michael Wilson
Libraries to use?
 Now
that you’ve got a coding assignment
and you’ve had some time to do your
own research, I’m going to draw attention
to some libraries

From very raw to very fully featured
 Won’t

go in total depth
I’d be spending hours on pure syntax
 Get
an idea of what kind of approaches
are available
Let’s start with C
 Why?

MySQL and PostgreSQL both come with
native C libraries
 They’re

pretty raw
Regardless, good to go over
libpq - PostgreSQL
 libpq
is PostgreSQL’s built in C library
 A lot of other libraries use this under the
covers
 Not necessarily something you want to
use if you can avoid it

There are much nicer libraries around
libpq - Connecting
const char *conninfo;
PGconn *conn;
conn = Pqconnectdb(conninfo);
if (Pqstatus(conn) != CONNECTION_OK)
{
/* Error connecting. */
PQfinish(conn);
}
libpq - Querying
PGresult *result = PQexec(conn,
“SELECT * FROM table”);
int numFields = PQnfields(result);
int numRows = PQntuples(result);
/* Get values */
PQclear(result);
libpq – Getting values
for(int i=0; i<numRows; i++)
{
for(int j=0; j<numFields; j++)
{
char *field = PQgetvalue(result, i, j);
}
}
libpq - Thoughts
 libpq
is not the kind of library that you’d
want to use if you could help it


C is not exactly the most well suited
language for this kind of task
A lot of plugins and applications use this
under the covers
 Good

to know about
At least so you know what a very “basic”
database library looks like
Hibernate





Java
More or less the industry standard for Java DB
interaction
Requires a good bit of up front work, but it
saves us a ton of pain at the end
The following examples assume you’ve
already got database tables
Also, a note: because Hibernate can operate
on multiple table types, it has its own query
language variant called HQL
Hibernate – create class

Create a class that reflects your database
class AddressBook {
private Integer id;
private String phoneNumber;
….
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
}
Hibernate – create class
 Class


must be JavaBeans compliant
http://en.wikipedia.org/wiki/JavaBeans
Must
 Have
getters/setters for each private variable
 Have a default constructor with no arguments
 Be serializable
Hibernate – create mapping
 The
mapping is an XML file while describes
how to map database tables to your class
 Create one mapping file per class


<classname>.hbm.xml
So: AddressBook.hbm.xml
Hibernate – create mapping
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name=“AddressBook” table=“AddressBook”>
<id name=“id” type=“int” column=“id”>
<generator class=“native” />
</id>
<property name=“phoneNumber” column=“phoneNumber”
type=“string” />
….
</class>
</hibernate-mapping>
Hibernate – create mapping
 Your
column names need not match up
exactly with the model class you’ve
created

Can name them how you want, as long as
the mapping is appropriate
 Generator
values

– how to generate new id
“native” means let the database handle it
Hibernate – getting an
AddressBook entry
SessionFactory factory = new
Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
AddressBook ab =
(AddressBook)session.get(AddressBook.class, 5);
Hibernate – update an
AddressBookEntry
SessionFactory factory = new
Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
AddressBook ab =
(AddressBook)session.get(AddressBook.class, 5);
ab.setPhoneNumber(“1234567”);
session.update(ab);
tx.commit();
Hibernate - Thoughts
A
bit more up front pain, but afterwards
DB operations are much simpler


 If
Don’t have to worry about getting column
5 in row 2
Gets an entire object out of the database
one at a time
you can help it, a library like this is more
along the lines of what you’d want to use
Other libraries?
 All

libraries are fair game in this course
There are libraries as nice as Hibernate, as
raw as libpq, and anywhere in between
 Depends
on the language you want to
use
 There is one pretty common pitfall in
utilizing these libraries in code
Bobby tables
From XKCD: http://xkcd.com/327/
Licensed under a Creative Commons Attribution NonCommercial license
What’s happening here?
 So
what really happened with this?
 Imagine the query being issued to the
database by a program looked
something like this:


“INSERT INTO Students (Name) VALUES (‘“ +
studentName + “’);
Code is concatenating the string directly
into the query
SQL injection
 Bobby
tables’ query issued to the
database:


INSERT INTO Students (Name) VALUES
(‘Robert’); DROP TABLE Students; --’)
Any guess as to what -- is?
How to prevent this?
 There

are a number of ways:
Prepared statements
 Server

Escaping parameters given to SQL queries
 Can

side/client side
be problematic
Data validation (regex)
Prepared statements (server
side)

PostgreSQL supports these server side

Kind of like creating a SQL function
PREPARE addressQuery (int) AS
SELECT * FROM AddressBook where id = $1

Afterwards, to execute the prepared
statement
EXECUTE addressQuery(5);
Prepared statements (server
side)
 Parameters
are not recognized as SQL
code fragments

Therefore, problems will not occur
Prepared statements (client
side)
 Many
database libraries emulate this
client side


You may not have permission or access to
prepare statements server side
Can do it in the code
Hibernate’s parameterization
String query = “from AddressBook a where
a.id = :id”;
List result = session.createQuery(hql)
.setParameter(“id”, 5)
.list();
Hibernate’s parameterization
 Similar
effect without the need for
PREPARE statements DB side
 You can use whatever feels more
comfortable for you
 I highly recommend using these in your
code, however
Dangers of SQL injection
 Hackers
erase debt
 Mysql.com compromised by SQL injection
 Malware installed onto thousands of
Chinese websites through SQL injection
Stored procedures
 Stored
procedures are a really deep,
involved topic

We can only get so far into this
 PostgreSQL
got it’s own language for
stored procedures


PL/pgSQL
http://www.postgresql.org/docs/9.3/static/
plpgsql.html
Stored procedures
 Functions





can
Return values
Issue SQL queries
Take arguments
Store things into variables (including query
results)
Branch, loop
Defining a stored procedure
CREATE FUNCTION <function-name>([args])
RETURNS <data-type> AS $$
DECLARE
<variables>
BEGIN
<code>
END;
$$ LANGUAGE plpgsql
Declaration block
 Here
you can declare variables to be
used in your stored procedure



user_id integer;
quantity numeric(5);
url varchar;
 Same
types of data types that are used in
SQL
 Defaults are used unless the variable is
assigned
Declaration block assignment
 Assignment


url varchar := ‘http://slashdot.org’;
user_id integer := 5;
 Can
make variables read only with
CONSTANT

user_id CONSTANT integer := 5;
Code block
 This
is where you put your actual code
statements
 Regular SQL will work here




You can use variables from the declaration
block here
You can also store things into variables
Arithmetic
Basically like any other language
Code block
 Assignment

y := user_id;
 SQL

statements
SELECT * FROM AddressBook INTO results;
 Selecting
 If

INTO a variable
statements
IF var > 5 THEN
RETURN FALSE;
END IF;
Code block
 Looping


FOR var IN vars
LOOP
count := count + 1;
END LOOP;
WHILE true i < 5
LOOP
i := i + 1;
END LOOP;
Code block

Raising exceptions


Catching exceptions


RAISE EXCEPTION ‘Message’;
BEGIN
y := 1 / 0;
EXCEPTION
WHEN DIVISION_BY_ZERO THEN
RAISE EXCPETION ‘Cannot divide by zero!’;
END;
Returning values

BEGIN
RETURN 5;
END;
Triggers
 What
if we want to specify some sort of
automated behavior when certain
conditions are met?

This is what triggers are for
 Triggers
can occur on any sort of data
modification in a table

Insert, update, delete
Triggers per statement or per
row
 Triggers
can be marked to occur FOR
EACH ROW

If this is the case, the trigger is called for
each row that a statement modifies
 Triggers
can also be marked to occur FOR
EACH STATEMENT

If a statement is executed that affects N
rows, the trigger will only execute once
Triggers

BEFORE


AFTER


The action will trigger BEFORE a statement is
executed
The action will trigger AFTER a statement is
executed
INSTEAD OF

Will occur INSTEAD OF the statement supplied

Can only operate on views, and must be
defined FOR EACH ROW
Triggers
 Triggers
in PostgreSQL can only execute
stored procedures

Need to store a procedure first, then
configure a trigger to execute it
Example trigger
 Say
we want to automatically populate
our AddressBook with numbers of people
who call us

CREATE TRIGGER populateAddressBook
BEFORE INSERT ON CallList
FOR EACH ROW
WHEN NOT EXISTS(SELECT phoneNumber
from AddressBook)
EXECUTE
AddToAddressBook(NEW.phoneNumber);