Avoiding Hacker Attacks

1. Avoiding Hacker Attacks

2. Objectives You will be able to • Avoid certain hacker attacks and crashes due to bad inputs from users.

3. Getting Started • http://www.cse.usf.edu/~turnerr/Software_Systems_Development/Downloads/2011_04_14_More_Hacker_Attacks/ • File Alt_Databound_Combo_Box_for_Hacker_Attacks.zip

4. SQL Injection Attacks • An Even More Insidious Threat • Potentially lets the hacker execute any SQL command. • Can take over your database. • Destroy your data. • Worse, steal it without your knowing.

5. How to Invite SQL Injection Attacks • Accept text input from the user and make it a part of a SQL command. • Suppose we provide a TextBox for the user to enter a search term. • Program retrieves information about all products with that search term in their ProductName.

6. Add New Product_Info Form

7. TextBox for Search Term

8. How to Search with SQL • The SQL "LIKE" operator permits us to search for a text string containing a specified search target. • Two wildcard characters • Percent sign (%) • Underscore (_) • % matches any number of characters in a string, including none. • _ matches exactly one character

9. How to Search with SQL SELECT * FROM Products WHERE ProductName LIKE '%Tofu%' • The string '%Tofu%' matches any ProductName including Tofu.

10. Copy Product_Info.cs • http://www.cse.usf.edu/~turnerr/Software_Systems_Development/Downloads/2011_04_11_Hacker_Attacks/Product_Info.cs • Replace stub created by Visual Studio.

11. Product_Info.cs using System; using System.Collections.Generic; using System.Windows.Forms; namespace Alt_Databound_Combo_Box { public partial class Product_Info : Form { String Username; String Password; List<Product> product_list; public Product_Info(String Username_, String Password_) { InitializeComponent(); Username = Username_; Password = Password_; }

12. Product_Info.cs private void btnGetProductInfo_Click(object sender, EventArgs e) { String Search_Term = tbSearchTerm.Text; product_list = Products.Get_Products(Username, Password, Search_Term); if (product_list.Count > 0) { foreach (Product p in product_list) { MessageBox.Show(p.Product_name); } } else { MessageBox.Show("No product found"); } tbSearchTerm.Text = ""; }

13. Reuse Some Code • http://www.cse.usf.edu/~turnerr/Software_Systems_Development/Downloads/2010_10_26_Product_Browser/ • Copy Product.cs and Products.cs into project folder. • Add to project.

14. Implement the Search • Modify Get_Products to produce a new version that gets products with ProductName containing a specified search term.

15. Products.cs public static List<Product> Get_Products(String Username, String Password, String Search_Term) { SqlDataReader rdr; SqlConnection cn; List<Product> Product_List = new List<Product>(); cn = Setup_Connection(Username, Password); rdr = Get_SqlDataReader(cn, Search_Term); while (rdr.Read()) { Product p = new Product(rdr); Product_List.Add(p); } rdr.Close(); cn.Close(); return Product_List; }

16. Products.cs private static SqlDataReader Get_SqlDataReader(SqlConnection conn, String Search_Term) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Products " + " WHERE ProductName LIKE '%" + Search_Term + "%'"; cmd.Connection = conn; return cmd.ExecuteReader(); }

17. Update Login Form private void btnLogIn_Click(object sender, EventArgs e) { if ((tbUserName.Text.IndexOf(';') >= 0) || (tbPassword.Text.IndexOf(';') >= 0)) { MessageBox.Show("Invalid input"); return; } Product_Info pi = new Product_Info(tbUserName.Text, tbPassword.Text); this.Hide(); pi.ShowDialog(); this.Close(); }

18. Program Used as Intended

19. An Innocent Error

20. Crash!

21. Program Subverted

22. ... Another Subversion Getting All Products

23. Defense • To foil this attack, and prevent crashes from bad inputs, replace each single quote with a pair of single quotes. • The server replaces pairs of single quotes with one single quote. • Treats that single quote as part of the string rather than as a delimiter. • Only way to include a single quote character in a text string in a SQL query.

24. Escape Single Quotes In Products.cs: private static SqlDataReader Get_SqlDataReader(SqlConnection conn, String Search_Term) { SqlCommand cmd = new SqlCommand(); Search_Term = Search_Term.Replace("'", "''"); cmd.CommandText = "SELECT * FROM Products " + " WHERE ProductName LIKE '%" + Search_Term + "%'"; cmd.Connection = conn; return cmd.ExecuteReader(); }

25. Attempted Subversion

26. Search Term with Apostrophe

27. Other Defensive Measures • Use the MaxLength property of TextBox to limit how many characters a user can enter. • For numeric input, parse the input and convert the resulting numeric value back into a string to splice into the command. • On exceptions, provide only a generic error message. • The actual error message from the exception might provide useful information to a hacker. • Use parameterized commands or stored procedures. End of Section

28. Parameterized Command • A command string that uses placeholders in the SQL text. • Placeholders replaced by dynamically supplied values at run time. • Uses the Parameters collection of the command object. • Specific to ADO.NET. • The command object checks the parameter value for attempted SQL injection attacks.

29. Parameterized Command Example • Rather than SELECT * FROM Customers WHERE CustomerID = 'ALFKI' where ALFKI was read from a TextBox • write SELECT * FROM Customers WHERE CustomerID = @CustID • @CustIDwill be replaced by a string containing a real customer ID at run time. • Note: No quotes around @CustID

30. Using a Parameterized Command private static SqlDataReader Get_SqlDataReader(SqlConnection conn, String Search_Term) { SqlCommand cmd = new SqlCommand(); //Search_Term = Search_Term.Replace("'", "''"); cmd.CommandText = "SELECT * FROM Products" + " WHERE ProductName LIKE @Parm1"; cmd.Parameters.AddWithValue("@Parm1", "%" + Search_Term + "%"); cmd.Connection = conn; return cmd.ExecuteReader(); }

31. Attempted Subversion

32. Term with Apostrophe

33. Blank Entry Everything matches!

34. Blank Entry • If we don't want the user to be able to ask for all products, we have to check for a zero length string in the TextBox. private void btnGetProductInfo_Click(object sender, EventArgs e) { String Search_Term = tbSearchTerm.Text; if (Search_Term.Length == 0) { MessageBox.Show("No search term entered"); return; } ...

35. Blank Entry End of Section

36. Stored Procedures • We can store SQL commands in the database and executed them from there. • A safer alternative to constructing SQL commands and executing them. • Visual Studio and ADO.NET provide support for this.

37. Stored Procedures • The Northwind Traders database has a lot of stored procedures. • Click on the + beside Stored Procedures in Server Explorer to expand the section.

38. Northwind Stored Procedures

39. Northwind Stored Procedures

40. Northwind Stored Procedures • We can execute these stored procedures from the Server Explorer. • Right click on a stored procedure and select Execute.

41. Executing a Stored Procedure

42. Executing a Stored Procedure

43. Results

44. Viewing a Stored Procedure • To view the stored procedure right click on the procedure and select Open.

45. Viewing a Stored Procedure

46. Viewing a Stored Procedure

47. Adding a Stored Procedure • To add a new stored procedure from the Server Explorer, right click on Stored Procedures and select Add New Stored Procedure. • Note that the new stored procedure will be a part of the database. • Stays there until you delete it.

48. Adding a Stored Procedure

49. Adding a Stored Procedure

50. Adding a Stored Procedure