Database ConnectivityRecall Multi-Tier ArchitecturesWhy use stored procedures?Another Problem: Injection AttacksSlide 5GuidelinesDatabase ConnectivityRose-Hulman Institute of TechnologyCurt CliftonRecall Multi-Tier ArchitecturesDBMS ServerWeb ServerWeb BrowserCustom Client(e.g., Java, C#)Why use stored procedures?Could just send individual SQL commands from UIUse stored procedures to:Optimize performance Keep SQL code on the serverImprove security by preventing casual table browsing and modificationsMore easily create atomic transactions (more next week)Prevent SQL injection attacksAnother Problem: Injection AttacksConsider:Interface presents form prompting for usernameString username = userNameField.getText();Interface builds query to get user’s custom picture from database"SELECT IDPicture FROM Users WHERE Username = '" + username + "'"Interface sends query to backend databaseSo what’s the problem?Another Problem: Injection AttacksConsider:Interface presents form prompting for usernameString username = userNameField.getText();Interface builds query to get user’s custom picture from database"SELECT IDPicture FROM Users WHERE Username = '" + username + "'"Interface sends query to backend databaseUser enters: smith'; DELETE FROM Users WHERE true OR Username ='GuidelinesDo not build queries using concatenationDo not use a highly privileged SQL account for access from the applicationEncrypt the DB connection stringCache static information in the applicationAlways explicitly define the table columns you wish to fetchUse parameter validation at all
View Full Document