Использование баз данных в приложениях ASP.NET

Выполнение команд над наборами данных

Установление соединения является необходимым условием подключения к базе данных. Однако подключение необходимо для того, чтобы выполнять определенные команды над элементами базы. Рассмотрим возможности выполнения команд над источниками данных.

Одним из основных элементов из набора классов ADO.NET, способным выполнять любой SQL-оператор, является класс Command. Для того, чтобы использовать класс Command, необходимо установить тип команды, установить текст запроса SQL и привязать ее к соединению с БД.

Существует 3 типа команд класса Command:

Пример создания объекта Command для выполнения SQL-запроса и хранимой процедуры представлен ниже.

SqlCommand cmd_SQL = new SqlCommand("Select * From Товары",
 sqlCon);
cmd_SQL.CommandType = CommandType.Text;

SqlCommand cmd_Proc=new SqlCommand("GetGoods",sqlCon);
        cmd_Proc.CommandType = CommandType.StoredProcedure;

Для выполнения созданной команды необходимо использовать один из следующих методов.

При использовании метода ExecuteReader() создается объект DataReader, с помощью которого можно организовать перебор всех строк возвращенного набора данных. Для этого необходимо реализовать цикл по строкам результирующего набора данных. Объект DataReader представляет собой один из самых простых и быстрых способов получения доступа к данным БД. В следующем примере демонстрируется способ отображения содержимого таблицы "Товары" в виде списка.

SqlDataReader rdr_SQL = cmd_SQL.ExecuteReader();
  StringBuilder strResult=new StringBuilder("");
  while (rdr_SQL.Read())
  {
    strResult.Append("<li>");
    strResult.Append("Код товара <b>");
    strResult.Append(rdr_SQL["КодТовара"]);
    strResult.Append("</b>, Наименование товара <b>");
    strResult.Append(rdr_SQL.GetString(1));
    strResult.Append("</b>");
    strResult.Append(", Цена <b>");
    strResult.Append(rdr_SQL.GetDouble(2));
    strResult.Append("</b></li>");
  }
  rdr_SQL.Close();
lbl_Result.Text = strResult.ToString();

Обращение к полям таблицы может происходить как по имени, так и с помощью индекса. В случае с индексом необходимо применять метод, соответствующий типу данных того поля, к которому происходит обращение. После завершения обращения к данным с помощью объекта DataReader необходимо закрыть модуль чтения.

В данном примере используется класс StringBuilder, который, как уже демонстрировалось ранее, более эффективно работает со строками. Те же самые операции со строками возможны и с применением стандартной операции конкатенации. Результат работы программы представлен на рис. 10.7.

Рис. 10.7. Построение списка на основе содержимого таблицы "Товары"

Использованный подход достаточно трудоемок для того, чтобы его применять на практике для вывода информации, содержащейся в базе данных. В ASP.NET существует несколько более мощных классов, способных производить данную операцию. Одним из таких классов является класс GridView. Применение данного класса позволяет осуществлять вывод информации на экран очень простым способом. В следующем примере показан способ вывода информации, получаемой из БД посредством объекта DataReader.

GridView1.DataSource = rdr_SQL;
GridView1.DataBind();

Результат работы данного кода представлен на рис. 10.8.

Рис. 10.8. Результат работы программы, отображающей информацию с помощью GridView

Метод ExecuteNonQuery() используется для выполнения команд, которые не возвращают результирующих наборов данных. К таким командам относятся команды вставки, удаления и обновления данных. Результатом работы метода ExecuteNonQuery() является количество обработанных записей. В следующем примере демонстрируется возможность удаления товара из таблицы "Товары".

string strSqlConnection = "Data Source=localhost\\
 sqlexpress;Initial Catalog=TEST_DB;Integrated Security=SSPI";
sqlCon = new SqlConnection(strSqlConnection);
SqlCommand cmdDelete = new SqlCommand("DELETE FROM Товары
 WHERE КодТовара=5",sqlCon);
try
{
  sqlCon.Open();
  int n = cmdDelete.ExecuteNonQuery();
  lbl_Delete.Text += String.Format("Удалено {0}
   записей</br>",n);
}
catch (SqlException ex)
{
  lbl_Delete.Text += String.Format("Ошибка: {0}</br>",
   ex.Message);
}
finally
{
  sqlCon.Close();
}

В результате работы данного примера в случае успешного выполнения запроса будет сформировано следующее сообщение.

Рис. 10.9. Результат работы программы, удаляющей запись из таблицы "Товары"

В реальных ситуациях практически всегда пользователю требуется ввести некоторое условие, в соответствии с которым будет выполняться запрос SQL. Таким условием, например, может быть ввод наименования товара, подлежащего удалению. Для реализации такой возможности в предыдущем примере добавим элемент управления TextBox, предназначенный для ввода наименования товара, который необходимо удалить, а также изменим текст строки, формирующей команду, следующим образом:

SqlCommand cmdDelete = new SqlCommand("DELETE FROM Товары
 WHERE НаименованиеТовара='"+tb_Delete.Text+"'",sqlCon);

Как видно, в данном случае, строка SQL-запроса удаления записи из таблицы "Товары" формируется динамически в зависимости от введенного в элемент tb_Delete значения. Такая практика динамического формирования запросов в реальных приложениях допустима, однако при этом могут возникать проблемы, связанные с безопасностью Web-приложения, например атаки внедрением SQL. Подробнее о такого рода атаках можно прочитать в [ 1 ] . Суть этого вида нарушения безопасности приложения состоит в том, что пользователь может ввести в поле ввода параметра текст, отличный от того, чего от него ожидает приложение. Это может приводить к тому, что текст SQL-запроса фактически изменяется и выполняет не то действие, на которое рассчитывал программист при его реализации. Например, если в предыдущем примере в элемент tb_Delete ввести следующий текст "Какой-то товар' OR '1'='1", то в результате будут удалены все записи из таблицы "Товары", т. к. текст SQL-запроса в этом случае, после подстановки значения введенного в элемент tb_Delete, будет

"DELETE FROM Товары WHERE НаименованиеТовара=
  'Какой-то товар' OR '1'='1'"

Как видно из этого примера, наименование введенного товара становится неважным, т. к. условие всегда будет истинным за счет добавления оператора OR с заведомо верным условием. Можно привести примеры и более сложных атак внедрением SQL, приводящих к еще более серьезным и масштабным последствиям и создающих реальную угрозу функционирования всего Web-приложения.

Решений данной проблемы может быть несколько. Во-первых, можно постараться проанализировать текст, введенный пользователем, до того как он будет подставлен в текст SQL-запроса. При этом можно анализировать длину введенного текста (если длина превышает стандартную длину вводимых данных, ожидаемых от пользователя, следует предпринять определенные действия и, возможно, заблокировать выполнение запроса), можно заменить все одиночные кавычки двумя одиночными кавычками. Хорошей практикой в этом случае считается обработка исключительных ситуаций, возникающих при работе с базой данных, и выдача соответствующих сообщений об ошибке. При этом текст сообщения об ошибке должен содержать только общую формулировку возникшей ошибки, т. е. не стоит выводить текст ошибки, передаваемый в объекте Exception.

Можно придумать и массу других алгоритмов анализа вводимых данных, но все равно они не могут гарантировать стопроцентной безопасности вашего приложения.

Лучшей практикой защиты от атаки внедрением SQL является использование параметризованных команд. Кроме того, параметризованные команды SQL более просты при формировании и применении.

Использование параметризованных команд

Параметризованная команда - это обычная SQL-команда, в тексте которой используются специальные символы, которые указывают место для динамической подстановки значений, передаваемых объекту Command через коллекцию Parameters. Например, команда удаления записи из таблицы "Товары", использованная выше, будет выглядеть следующим образом при использовании параметра:

DELETE FROM Товары WHERE НаименованиеТовара=@ProductName

Здесь @ProductName представляет собой параметр, значение которого должно быть установлено до того, как будет запущено выполнение запроса. Синтаксис параметризованных команд отличается в разных поставщиках данных. Приведенный выше пример справедлив для взаимодействия с SQL Server. Для использования той же команды при взаимодействии с Access тот же запрос должен выглядеть следующим образом:

DELETE FROM Товары WHERE НаименованиеТовара=?

Текст программы, реализующей подключение к наборам данных SQL Server и Access, установку значений параметров запросов и их исполнение, приведен ниже.

OleDbConnection AccessCon=new
 OleDbConnection(strOleDbConnection);
string strSQLServer = "DELETE FROM Товары WHERE Наименование
 Товара=@ProductName";
string strAccess = "DELETE FROM Товары WHERE Наименование
 Товара=?";
SqlCommand cmdDeleteSQLServer = new SqlCommand
 (strSQLServer,sqlCon);
OleDbCommand cmdDeleteAccess=new OleDbCommand
 (strAccess,AccessCon);
try
{
  cmdDeleteSQLServer.Parameters.Add("@ProductName",
  tb_Delete.Text);
  sqlCon.Open();
  int n = cmdDeleteSQLServer.ExecuteNonQuery();
  lbl_Delete.Text += String.Format("Из базы данных SQL Server
   удалено {0} записей</br>",n);

  cmdDeleteAccess.Parameters.Add("ProductName",
   tb_Delete.Text);
  AccessCon.Open();
  int k = cmdDeleteAccess.ExecuteNonQuery();
  lbl_Delete.Text += String.Format("Из базы данных Access
   удалено {0} записей</br>", k);

}
catch (Exception ex)
{
  lbl_Delete.Text += String.Format("Ошибка: {0}</br>",
   ex.Message);
}
finally
{
  sqlCon.Close();
  AccessCon.Close();
}

Результат работы программы изображен на рис. 10.10.

Рис. 10.10. Результат работы программы удаления записи из таблицы "Товары" баз данных SQL Server и Access

Кроме того, что работа с параметризованными запросами проще с точки зрения динамического формирования текста самого запроса, она еще обеспечивает защиту от атаки внедрением SQL. Так, предыдущий пример использования такой атаки в данном случае не работает.

Использование хранимых процедур

При использовании сложной СУБД, реализующей полный набор функций клиент-серверной СУБД, разработчик приложения, взаимодействующего с базой данных, получает в свое распоряжение несколько дополнительных очень мощных инструментов, позволяющих облегчить, ускорить и обезопасить процесс взаимодействия с базой данных по сравнению с локальными базами данных. Одними из основных таких возможностей являются способы организации и использования хранимых процедур и триггеров. Вопросы создания и программирования хранимых процедур и триггеров, а также всевозможные вопросы, связанные с особенностями их реализации в конкретных СУБД, выходят за рамки данного курса. Желающим более подробно ознакомиться с особенностями реализации такого рода элементов рекомендуется ознакомиться с [ 5 ] , [ 6 ] . Здесь же будут в основном рассмотрены вопросы использования хранимых процедур при работе с SQL Server без объяснения того, как именно создавалась та или иная хранимая процедура.

Хранимая процедура представляет собой набор операторов SQL, сохраненный в базе данных (на стороне сервера), имеющий имя, а также набор параметров, посредством которых могут быть переданы значения в хранимую процедуру. Они подобны процедурам, используемым в языках программирования, с той лишь разницей, что в данном случае предназначены для обработки данных, содержащихся в базе данных.

Хранимые процедуры обладают рядом преимуществ, главными из которых являются такие, как повышение быстродействия приложения при использовании хранимых процедур для обработки данных, повышение безопасности приложения при работе с данными.

В реальных приложениях, если используется СУБД, поддерживающая возможности использования хранимых процедур, рекомендуется воспользоваться этим и создавать приложение с их применением. Кроме того, рекомендуется все наиболее типичные и часто возникающие действия, которые связаны с обработкой данных, содержащихся в базе данных, а также операции вставки, удаления и изменения производить с помощью вызова предварительно созданных хранимых процедур.

Рассмотрим пример создания и использования простой хранимой процедуры, позволяющей добавить новый товар в таблицу "Товары" базы данных. Текст хранимой процедуры, реализованной в СУБД SQL Server 2005, выглядит следующим образом:

CREATE PROCEDURE AddProduct
  @ProductID int,
  @ProductName varchar(100),
  @ProductPrice float
AS
  INSERT INTO Товары
VALUES(@ProductID,@ProductName,@ProductPrice)

Данная хранимая процедура использует три значения переменных для формирования запроса на добавление данных в таблицу "Товары".

Для ее вызова можно воспользоваться объектом SqlCommand. Текст программы, реализующей вызов данной хранимой процедуры, приведен ниже.

SqlCommand cmd_SQL=new SqlCommand("AddProduct",sqlCon);
cmd_SQL.CommandType = CommandType.StoredProcedure;
string[] strProduct = tb_AddProduct.Text.Split(new char[]
 {','});
cmd_SQL.Parameters.Add(new SqlParameter("@ProductID",
 SqlDbType.Int, 4));
cmd_SQL.Parameters["@ProductID"].Value =
 Convert.ToInt32(strProduct[0]);
cmd_SQL.Parameters.Add(new SqlParameter("@ProductName",
 SqlDbType.NVarChar, 100));
cmd_SQL.Parameters["@ProductName"].Value = strProduct[1];
cmd_SQL.Parameters.Add(new SqlParameter("@ProductPrice",
 SqlDbType.Float, 8));
cmd_SQL.Parameters["@ProductPrice"].Value =
 Convert.ToDouble(strProduct[2]);

try
{
  sqlCon.Open();
  int k = cmd_SQL.ExecuteNonQuery();
}
finally
{
  sqlCon.Close();

}

В процессе работы данной программы текст, введенный пользователем в элемент TextBox, преобразуется в массив строк. В коллекцию Parameters объекта SqlConnection добавляется три параметра, соответствующих параметрам хранимой процедуры. Для них устанавливаются значения, ранее помещенные в массив строк. После чего открывается соединение и посылается запрос на исполнение хранимой процедуры посредством команды ExecuteNonQuery().

Из данного примера видно, что при добавлении нового параметра, выступающего в роли параметра хранимой процедуры, необходимо обязательно указывать его тип, а также длину (для строковых типов это количество символов, для числовых - количество байт).