Основы ADO.NET
Применение параметризованных команд
Параметризованная команда, это обычная команда SQL, использующая спецификаторы, обозначающие место, в которое будут динамически подставлены объекту Command определенные параметры-заполнители SQL через коллекцию Parameters. Параметры-заполнители прописываются отдельно и автоматически кодируются.
Синтаксис параметризованных команд слегка отличается у разных поставщиков данных. Так, поставщик SQL Server предусматривает именованные заполнители с уникальными именами. У поставщика OLE DB спецификаторы являются вопросами, а порядок следования заполнителей должен соответствовать контексту следования спецификаторов.
Приведем пример, использующий параметризованную команду, в котором исключена возможность атаки внедрением SQL.
-
Сделайте из страницы UserSqlGridData.aspx копию с именем UserSqlParamCommand.aspx и назначьте ее стартовой -
Откорректируйте страницу UserSqlParamCommand.aspx, чтобы она имела следующий код
<%@ Page Language="C#" %> <script runat="server"> protected void Page_Load(object sender, EventArgs e) { // Извлекли строку соединения из web.config string connectString = System.Web.Configuration.WebConfigurationManager. ConnectionStrings["Northwind"].ConnectionString; // Создали объект соединения System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString); // Создали строку SQL-запроса string sql = "SELECT Orders.CustomerID, Orders.OrderID, COUNT(UnitPrice) AS Items, " + "SUM(UnitPrice * Quantity) AS Total FROM Orders " + "INNER JOIN[Order Details] " + "ON Orders.OrderID = [Order Details].OrderID " + "WHERE Orders.CustomerID = @CustomID " + "GROUP BY Orders.OrderID, Orders.CustomerID"; // Создали объект Command System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con); // Дополнили коллекцию Parameters cmd.Parameters.Add("@CustomID", TextBox1.Text); using (con) { // Открыли соединение con.Open(); // Выполняем команду и получаем результирующий набор данных System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(); // Подключаем элемент управления GridView // к полученному результирующему набору GridView1.DataSource = reader; // Наполняем элемент управления GridView // всеми извлеченными записями DataReader GridView1.DataBind(); // Закрываем набор данных reader.Close(); } } </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> Введите ID пользователя:<br /> <asp:TextBox ID="TextBox1" runat="server" Width="248px" Font-Bold="True">ALFKI</asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Получить" /><br /> <br /> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </div> </form> </body> </html>
Код страницы остался почти таким же, за исключением двух строк, вводящих команду с именованным параметром @CustomID.
Мы видим, что страница не возвращает никаких данных, поскольку ни одна запись таблицы Orders в поле CustomerID не имеет значение, равное введенной строке ALFKI' OR '1' = '1. Таким простым способом мы защитили данные от злоумышленного внедрения SQL.
Вызов хранимых процедур
Хранимые процедуры представляют собой пакет из одного или нескольких операторов SQL, хранимых в базе данных. Это самый действенный способ от внедрения SQL. Хранимые процедуры похожи на функции тем, что они имеют свою отлаженную логику и принимают данные через входные параметры, а возвращают данные через выходные параметры или результирующие наборы.
Хранимые процедуры имеют множество достоинств:
- Их легко сопровождать. Например, можно изменять команды в хранимой процедуре без перекомпиляции приложения, использующего эту процедуру
- Они позволяют реализовать более безопасный доступ к базе данных. Например, можно позволить учетной записи Windows, запускающей наш код ASP.NET, использовать определенные хранимые процедуры, но ограничить доступ к лежащим в их основе таблицам
- Они могут повысить производительность. Поскольку хранимые процедуры упаковывают вместе множество SQL-операторов, можно выполнить огромный объем работы за одно обращение к серверу базы данных, особенно, если база расположена на другом компьютере
Рассмотрим пример SQL-кода, необходимого при создании хранимой процедуры для вставки отдельной записи в таблицу Employees. Этой хранимой процедуры изначально нет в БД Northwind. Хранимая процедура должна реализовывать следующий код
CREATE PROCEDURE InsertEmployee @TitleOfCourtesy varchar(25), @LastName varchar(20), @FirstName varchar(10), @EmployeeID int OUTPUT AS INSERT INTO Employees (TitleOfCourtesy, LastName, FirstName, HireDate) VALUES(@TitleOfCourtesy, @LastName, @FirstName, GETDATE()); SET @EmployeeID = @@IDENTITY
Эту хранимую процедуру можно добавить на этапе проектирования через панель Server Explorer оболочки, предварительно присоединившись к базе данных Nordhwind. Но я не смог это сделать, поскольку оболочка не видит эту базу из-за неправильно установленного пакета SQL Server 2005 (или потому, что у меня руки кривые). Чтобы как-то вывернуться, в приведенном ниже коде мы добавим ее программно.
-
Создайте новую страницу с именем TestStoredProcedure.aspx и назначьте ее стартовой
-
Поместите на страницу из вкладки Standard элементы управления согласно дескрипторному коду, приведенному ниже (устал писать, да и студент Зиборов ругается, но зато все работает!)
-
Настройте код страницы, чтобы в итоге он был таким
<%@ Page Language="C#" %>
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
// Извлекли строку соединения из web.config
string connectString = System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["Northwind"].ConnectionString;
// Создали объект соединения
System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection(connectString);
try
{
string sql =
"CREATE PROCEDURE InsertEmployee "
+ "@TitleOfCourtesy varchar(25),"
+ "@LastName varchar(20),"
+ "@FirstName varchar(10),"
+ "@EmployeeID int OUTPUT "
+ "AS "
+ "INSERT INTO Employees "
+ "(TitleOfCourtesy, LastName, FirstName, HireDate) "
+ "VALUES(@TitleOfCourtesy, @LastName, @FirstName, GETDATE());"
+ "SET @EmployeeID = @@IDENTITY";
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException error)
{
}
finally
{
con.Close();
}
}
protected void AddEmployee_Click(object sender, EventArgs e)
{
// Извлекли строку соединения из web.config
string connectString = System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["Northwind"].ConnectionString;
// Создали объект соединения
System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection(connectString);
// Создать и настроить объект Command
// для вызова хранимой процедуры InsertEmployee
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand("InsertEmployee", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Добавить входные параметры хранимой процедуры
// в коллекцию параметров Command.Parameters
// Добавляем первый входной параметр
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
"@TitleOfCourtesy", System.Data.SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = title.Text;
// Добавляем второй входной параметр
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
"@LastName", System.Data.SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = lastName.Text;
// Добавляем третий входной параметр
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
"@FirstName", System.Data.SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = firstName.Text;
// Добавить выходной параметр хранимой процедуры
// в коллекцию параметров Command.Parameters
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
"@EmployeeID", System.Data.SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Direction = System.Data.ParameterDirection.Output;
using (con)
{
// Открыли соединение
con.Open();
// Выполняем команду
int recCount = cmd.ExecuteNonQuery();
lblInfo.Text = string.Format("<b>Вставлено записей:</b> {0}<br />", recCount);
// Получить вновь сгенерированный идентификатор
int empID = (int)cmd.Parameters["@EmployeeID"].Value;
lblInfo.Text += "Новый идентификатор: " + empID.ToString();
}
// Соединение закрылось автоматически
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
Введите титул:<asp:TextBox ID="title" runat="server">студент</asp:TextBox><br />
Введите имя:<asp:TextBox ID="firstName" runat="server">Иван</asp:TextBox><br />
Введите фамилию:<asp:TextBox ID="lastName" runat="server">Петров</asp:TextBox><br />
<asp:Label ID="lblInfo" runat="server" />
<br />
<br />
<asp:Button ID="AddEmployee" runat="server" Text="Добавить служащего"
OnClick="AddEmployee_Click" />
<asp:LinkButton ID="btnResult" runat="server"
PostBackUrl="~/TestDataReader.aspx">Показать список</asp:LinkButton>
</form>
</body>
</html>Интерфейс страницы будет таким
-
Добавьте служащего щелчком по кнопке, после чего выведите список. Результат будет примерно таким
- Служащий Ms. Davolio, Nancy - работает с 01.05.1992
- Служащий Dr. Fuller, Andrew - работает с 14.08.1992
- Служащий Ms. Leverling, Janet - работает с 01.04.1992
- Служащий Mrs. Peacock, Margaret - работает с 03.05.1993
- Служащий Mr. Buchanan, Steven - работает с 17.10.1993
- Служащий Mr. Suyama, Michael - работает с 17.10.1993
- Служащий Mr. King, Robert - работает с 02.01.1994
- Служащий Ms. Callahan, Laura - работает с 05.03.1994
- Служащий Ms. Dodsworth, Anne - работает с 15.11.1994
- Служащий студент Петров, Иван - работает с 25.04.2007
В обработчике Page_Load() мы намеренно применили пустой блок обработки исключений для того, чтобы подавить сообщение, которое выдается при попытке повторного создания хранимой процедуры с тем же именем.