Libraries, pitfalls, stored procedures , triggers

1. Libraries, pitfalls, stored procedures, triggers CMSC 461 Michael Wilson

2. 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

3. Let’s start with C • Why? • MySQL and PostgreSQL both come with native C libraries • They’re pretty raw • Regardless, good to go over

4. 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

5. libpq - Connecting const char *conninfo;PGconn *conn;conn = Pqconnectdb(conninfo);if (Pqstatus(conn) != CONNECTION_OK){ /* Error connecting. */PQfinish(conn);}

6. libpq - Querying PGresult *result = PQexec(conn, “SELECT * FROM table”); intnumFields = PQnfields(result); intnumRows = PQntuples(result);/* Get values */PQclear(result);

7. libpq – Getting values for(inti=0; i<numRows; i++) { for(int j=0; j<numFields; j++) { char *field = PQgetvalue(result, i, j); } }

8. 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

9. 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

10. Hibernate – create class • Create a class that reflects your database class AddressBook{ private Integer id; private String phoneNumber; …. public intgetId() { return id; } public void setId(int id) { this.id = id; } }

11. 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

12. 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

13. 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>

14. 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 – how to generate new id values • “native” means let the database handle it

15. 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);

16. 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();

17. Hibernate - Thoughts • A bit more up front pain, but afterwards DB operations are much simpler • Don’t have to worry about getting column 5 in row 2 • Gets an entire object out of the database one at a time • If you can help it, a library like this is more along the lines of what you’d want to use

18. 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

19. Bobby tables From XKCD: http://xkcd.com/327/ Licensed under a Creative Commons Attribution NonCommercial license

20. 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

21. SQL injection • Bobby tables’ query issued to the database: • INSERT INTO Students (Name) VALUES (‘Robert’); DROP TABLE Students; --’) • Any guess as to what -- is?

22. How to prevent this? • There are a number of ways: • Prepared statements • Server side/client side • Escaping parameters given to SQL queries • Can be problematic • Data validation (regex)

23. 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);

24. Prepared statements (server side) • Parameters are not recognized as SQL code fragments • Therefore, problems will not occur

25. 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

26. Hibernate’s parameterization String query = “from AddressBook a where a.id = :id”; List result = session.createQuery(hql) .setParameter(“id”, 5) .list();

27. 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

28. Dangers of SQL injection • Hackers erase debt • Mysql.com compromised by SQL injection • Malware installed onto thousands of Chinese websites through SQL injection

29. Stored procedures • Stored procedures are a really deep, involved topic • We can only get so far into this • PostgreSQLgot it’s own language for stored procedures • PL/pgSQL • http://www.postgresql.org/docs/9.3/static/plpgsql.html

30. Stored procedures • Functions can • Return values • Issue SQL queries • Take arguments • Store things into variables (including query results) • Branch, loop

31. Defining a stored procedure CREATE FUNCTION <function-name>([args]) RETURNS <data-type> AS $$ DECLARE <variables>BEGIN <code> END; $$ LANGUAGE plpgsql

32. Declaration block • Here you can declare variables to be used in your stored procedure • user_id integer; • quantity numeric(5); • urlvarchar; • Same types of data types that are used in SQL • Defaults are used unless the variable is assigned

33. Declaration block assignment • Assignment • urlvarchar := ‘http://slashdot.org’; • user_id integer := 5; • Can make variables read only with CONSTANT • user_id CONSTANT integer := 5;

34. 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

35. Code block • Assignment • y := user_id; • SQL statements • SELECT * FROM AddressBook INTO results; • Selecting INTO a variable • If statements • IF var > 5 THEN RETURN FALSE;END IF;

36. Code block • Looping • FOR var IN varsLOOP count := count + 1;END LOOP; • WHILE true i < 5LOOPi := i + 1;END LOOP;

37. Code block • Raising exceptions • RAISE EXCEPTION ‘Message’; • Catching exceptions • BEGIN y := 1 / 0;EXCEPTION WHEN DIVISION_BY_ZERO THEN RAISE EXCPETION ‘Cannot divide by zero!’;END; • Returning values • BEGIN RETURN 5;END;

38. 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

39. 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

40. Triggers • BEFORE • The action will trigger BEFORE a statement is executed • AFTER • 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

41. Triggers • Triggers in PostgreSQL can only execute stored procedures • Need to store a procedure first, then configure a trigger to execute it

42. Example trigger • Say we want to automatically populate our AddressBook with numbers of people who call us • CREATE TRIGGER populateAddressBook BEFORE INSERT ON CallListFOR EACH ROWWHEN NOT EXISTS(SELECT phoneNumber from AddressBook)EXECUTE AddToAddressBook(NEW.phoneNumber);