Россия |
Основы 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() мы намеренно применили пустой блок обработки исключений для того, чтобы подавить сообщение, которое выдается при попытке повторного создания хранимой процедуры с тем же именем.