1 / 42

Libraries, pitfalls, stored procedures , triggers

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

lexi
Download Presentation

Libraries, pitfalls, stored procedures , triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

More Related