Transcript A Databound Combo Box - University of South Florida

Avoiding Hacker Attacks
1
Objectives
You will be able to
 Avoid certain hacker attacks and crashes
due to bad inputs from users.
2
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
3
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.
4
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.
5
Add New Product_Info Form
6
TextBox for Search Term
7
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
8
How to Search with SQL
SELECT * FROM Products
WHERE ProductName LIKE '%Tofu%'

The string '%Tofu%' matches any
ProductName including Tofu.
9
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.
10
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_;
}
11
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 = "";
}
12
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.
13
Implement the Search

Modify Get_Products to produce a new
version that gets products with
ProductName containing a specified
search term.
14
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;
}
15
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();
}
16
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();
}
17
Program Used as Intended
18
An Innocent Error
19
Crash!
20
Program Subverted
21
Another Subversion
Getting All Products
...
22
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.
23
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();
}
24
Attempted Subversion
25
Search Term with Apostrophe
26
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
27
Parameterized Command

A command string that uses placeholders
in the SQL text.


Uses the Parameters collection of the
command object.


Placeholders replaced by dynamically
supplied values at run time.
Specific to ADO.NET.
The command object checks the
parameter value for attempted SQL
injection attacks.
28
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

@CustID will be replaced by a string containing

a real customer ID at run time.
Note: No quotes around @CustID
29
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();
}
30
Attempted Subversion
31
Term with Apostrophe
32
Blank Entry
Everything matches!
33
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;
}
...
34
Blank Entry
End of Section
35
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.
36
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.
37
Northwind Stored Procedures
38
Northwind Stored Procedures
39
Northwind Stored Procedures

We can execute these stored procedures
from the Server Explorer.

Right click on a stored procedure and select
Execute.
40
Executing a Stored Procedure
41
Executing a Stored Procedure
42
Results
43
Viewing a Stored Procedure

To view the stored procedure right click
on the procedure and select Open.
44
Viewing a Stored Procedure
45
Viewing a Stored Procedure
46
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.
47
Adding a Stored Procedure
48
Adding a Stored Procedure
49
Adding a Stored Procedure
50
Saving the New Stored Procedure
Click icon to save the new stored procedure
51
Executing the Stored Procedure
Visual Studio changes "CREATE" to "ALTER".
We can now execute the procedure from the Server Explorer
52
Executing the Stored Procedure
53
Supplying the Parameter Value
54
Results from the Execution
Results
55
Executing a Stored Procedure from C#


We can execute a stored procedure from
within our program.
In Products.cs add
using System.Data;
56
Executing a Stored Procedure Programatically
private static SqlDataReader Get_SqlDataReader(SqlConnection conn,
String Search_Term)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Product_Search";
cmd.Parameters.AddWithValue("@Param1", "%" + Search_Term + "%");
cmd.Connection = conn;
return cmd.ExecuteReader();
}
57
Program in Action
End of Presentation
58