Data Modeling Seminar

1. Data ModelingSeminar February 18, 2012 Lesson 5 Data Access Layering – Part 1

2. What is this about • Use the data in the database via an objects • Instead of making many queries and looping through SQL results • Layer the data access • Can interchange databases easily

3. Data Access Layering

4. DAO Example

5. Anatomy DAO • BaseDAO • Specific to RDBMS • ModelDAO • Specific to Model • Uses parameters or stored procedured • Model Object • Mirrors database table

6. Standard Approach • Bind a SqlConnection • Bind a Stringwith SQL • Bind a SqlCommand • useSQL String & SqlConnection • Bind reader = command.ExecuteReader(); • Call reader.Read() • Get the value(s) from the reader • Loop until reader.HasRows() is false

7. Code Example System.Configuration.AppSettingsReaderconfigAppSettings = newSystem.Configuration.AppSettingsReader(); stringstrConnection = (String)configAppSettings.GetValue("SqlConnectString", Type.GetType("System.String")); SqlConnectionsessionCon = newSqlConnection(strConnection); SqlCommandcommand = null; SqlDataReaderreader = null; Stringsql; Int32 id; sql = "SELECT id from table_1"; command = newSqlCommand(sql, sessionCon); reader = command.ExecuteReader(); if(reader.Read()) id = reader.GetInt32(0);

8. Issues • Cumbersome • Modifications maybe difficult • SQL Injection • Users may ‘sneak’ extra data into query • i.e. on login.. Users may try to insert “’ OR ‘’ = ‘” • If you insert text result into your query.. may end up with • Where user name = ‘user’ AND password = ‘’ OR ‘’ = ‘’ • The OR ‘’ = ‘’will generate a valid query

9. Little Bobby Tables

10. How to avoid Bobby Tables • There is only one way to avoid Bobby Tables attacks • Do not create SQL statements that include outside data. • Use parameterized SQL calls. • That's it. Don't try to escape invalid characters. Don't try to do it yourself. Learn how to use parameterized statements. Always, every single time. • The strip gets one thing crucially wrong. The answer is not to "sanitize your database inputs" yourself. It is prone to error

11. DAO Example - BaseDAO • Data Access Objects are derived from this abstract class • Provide common functionality • Update • Insert • Delete • Select • Map [Database data to Object data fields] • Parameterized Statements

12. DAO Example – User

13. DAO Example - UserDAO

14. UserDAO

15. UserDAO

17. UserDAO

18. Integrating the DAO BooleanLogin(StringuserName, String password) { UserDAOuserDAO = newUserDAO(); returnuserDAO.isLoginValid(userName, password); }

19. Closer Look at BaseDAO

21. Questions

22. Lab 4 • Class Schedule DAO • Visual Studio 2010 Project