Приклад 9. Видалення записів з таблиці БД.
Метод ExecuteNonQuery() використовується для виконання команд, що не повертають результуючих наборів даних. До таких команд відносяться команди вставки, видалення і відновлення даних. Результатом роботи методу ExecuteNonQuery() є кількість оброблених записів. У наступному прикладі демонструється можливість видалення товару з таблиці товари.
Оскільки користувачеві необхідно надавати можливість побачити дані таблиці БД до видалення і після, то необхідно використовувати програмний код відображення даних БД неодноразово. Повторюваний фрагмент коду відображення даних БД оформимо у вигляді функції:
public void Bind()
{
string connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd_SQL = new SqlCommand("Select * From tovar ", con);
cmd_SQL.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr_SQL = cmd_SQL.ExecuteReader();
StringBuilder strResult = new StringBuilder("");
while (rdr_SQL.Read())
{
strResult.Append("<li>");
strResult.Append("Код товара <b>");
strResult.Append(rdr_SQL["kod_tov"]);
strResult.Append("</b>, Наименование товара <b>");
strResult.Append(rdr_SQL.GetString(1));
strResult.Append("</b>");
strResult.Append(", ГОСТ <b>");
strResult.Append(rdr_SQL.GetString(2));
strResult.Append("</b></li>");
}
rdr_SQL.Close();
Label2.Text = strResult.ToString();
}
Для того, щоб після видалення даних користувач міг одразу ж отримати оновлений набір даних, то необхідно функцію Bind() викликати в блоку коду
if (!IsPostBack)
{ Bind(); }
В обробник події натискання на кнопку „Видалити” необхідно записати код:
protected void Button1_Click(object sender, EventArgs e)
{ string connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmdDelete = new SqlCommand("DELETE FROM tovar WHERE kod_tov=15", con);
try
{ con.Open();
int n = cmdDelete.ExecuteNonQuery();
Label3.Text += String.Format("Удалено {0} записей</br>", n);
}
catch (SqlException ex)
{ Label3.Text += String.Format("Ошибка: {0}</br>", ex.Message); }
finally
{ con.Close(); }
Bind();
}
В цьому прикладі в SQL-запит видалення вноситься параметр за який буде здійснюватися видалення, а саме – значення первинного ключа запису таблиці (наприклад значення 18). На рис. 3.18 наведено екранна форма до видалення, а на рис. 3.19 – після натискання кнопку „Видалити запис” та здійснення видалення запису з таблиці БД. Оновлення значень на формі здійснюється автоматично.
Рис. 3.18. Вигляд веб-додатку в браузері до видалення
Рис. 3.19. Вигляд веб-додатку в браузері після видалення
Приклад 10. Видалення запису з БД по значенню, ввденному до TextBox.У реальних ситуаціях практично завжди користувачеві потрібно ввести деяку умову, відповідно до якої буде виконуватися запит SQL. Такою умовою, наприклад, може бути введення найменування товару, що підлягає видаленню. Для реалізації такої можливості додамо елемент керування TextBox, призначений для введення найменування товару, який необхідно видалити, а також змінимо текст рядка, що формує команду в такий спосіб.
SqlCommand cmdDelete = new SqlCommand("DELETE FROM tovar WHERE nazv='"++"'",sqlCon);
Як видно, у даному випадку, рядок SQL запиту видалення запису з таблиці Товари формується динамічно в залежності від введеного в елемент TextBox1 значення. Така практика динамічного формування запитів у реальних додатках припустима, однак, при цьому можуть виникати проблеми, зв'язані з безпекою Web додатка, наприклад атаки впровадженням SQL. Суть цього виду порушення безпеки додатка полягає в тому, що користувач може ввести в поле введення параметра текст, відмінний від того, чого від нього очікує додаток. Це може приводити до того, що текст SQL запиту фактично змінюється й у результаті виконує не ту дію, на яке розраховував програміст при його реалізації. Наприклад, якщо в попередньому прикладі в елемент TextBox1 увести наступний текст „Назва товару” OR '1'='1", то в результаті будуть вилучені всі записи з таблиці Товари, тому що текст SQL запиту в цьому випадку, після підстановки значення введеного в елемент TextBox1 буде
"DELETE FROM tovar WHERE nazv_tov= ”Назва товару” OR '1'='1'"
Як видно з цього приклада, найменування введеного товару стає неважливим, тому що умова завжди буде вірною за рахунок додавання оператора OR зі свідомо вірною умовою. Можна привести приклади і більш складних атак упровадженням SQL, що приводять до ще більш серйозних і масштабних наслідків у функціонуванні, що створюють реальну погрозу, усього Web додатка.
Рішенням даної проблеми може бути кілька. По-перше, можна постаратися проаналізувати текст, уведений користувачем до того, як він буде підставлений у текст SQL запиту. При цьому можна аналізувати довжину введеного тексту (якщо довжина перевищує стандартну довжину даних, що вводиться, очікуваних від користувача, варто почати визначені дії і можливо заблокувати виконання запиту), можна замінити всі одиночні лапки двома одиночними лапками. Гарною практикою в цьому випадку вважається обробка виняткових ситуацій, що виникають при роботі з базою даних і видача відповідних повідомлень про помилку. При цьому текст повідомлення про помилку повинний містити тільки загальне формулювання виниклої помилки, тобто не варто виводити текст помилки, переданий в об'єкті Exceptіon.
Можна придумати і масу інших алгоритмів аналізу вводи даних, але все рівно вони не можуть гарантувати сто процентної безпеки Вашого додатка. Кращою практикою захисту від атаки впровадженням SQL є використання параметризованих команд. Крім того, параметризовані команди SQL більш прості при формуванні і застосуванні. Результати здійснення видалення по введеному значенню можна побачити на рис. 3.20 – до видалення та рис. 3.21 – після видалення.
Рис. 3.20. Вигляд веб-додатку в браузері до видалення
Рис. 3.21. Вигляд веб-додатку в браузері після видалення
Приклад 11. Видалення запису з БД при використанні параметризованих команд.Параметризована команда – це звичайна SQL команда, у тексті якої використовуються спеціальні символи, що вказують місце для динамічної підстановки значень, переданих об'єктові Command через колекцію Parameters. Наприклад, команда видалення запису з таблиці Товари, використана вище, буде виглядати в такий спосіб при використанні параметра.
DELETE FROM tovar WHERE nazv=@Name
Тут @Name є параметром, значення якого повинно бути встановлене до того, як буде запущене виконання запиту. Синтаксис параметризованих команд відрізняється в різних постачальниках даних. Приведений вище приклад справедливий для взаємодії з SQL Server. Для використання тієї ж команди при взаємодії з Access той же запит повинний виглядати в такий спосіб.
DELETE FROM tovar WHERE nazv =?
Текст програми, що реалізує підключення до наборів даних SQL Server, установку значень параметрів запитів і їхнє виконання приведене нижче.
protected void Button2_Click(object sender, EventArgs e)
{ string connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string strSQLServer = "DELETE FROM tovar WHERE nazv=@Name";
SqlCommand cmdDeleteSQLServer = new SqlCommand(strSQLServer, con);
try
{ cmdDeleteSQLServer.Parameters.AddWithValue("@Name", TextBox1.Text);
con.Open();
int n = cmdDeleteSQLServer.ExecuteNonQuery();
Label3.Text += String.Format("Из базы данных SQL Server удалено {0} записей</br>", n);
}
catch (Exception ex)
{ Label3.Text += String.Format("Ошибка: {0}</br>", ex.Message);
}
finally
{ con.Close();
}
Bind();
}
Результат видалення наведено на рис. 3.22 – до видалення та рис. 3.23 – після видалення.
Рис. 3.22. Вигляд веб-додатку в браузері до видалення
Рис. 3.23. Вигляд веб-додатку в браузері після видалення
Приклад 12. Застосування збережених процедур при роботі з даними таблиці БД.Збережена процедура є набором операторів SQL, збереженим у базі даних (на стороні сервера), що має ім'я, а також набір параметрів, за допомогою яких можуть бути передані значення в збережену процедуру. Вони подібні процедурам, використовуваних у мовах програмування з тією лише різницею, що в даному випадку, призначені для обробки даних, що утримуються в базі даних.
Збережені процедури володіють рядом переваг, головними з яких є такі, як підвищення швидкодії додатка при використанні збережених процедур для обробки даних, підвищення безпеки додатка при роботі з даними. У реальних додатках, якщо використовується СУБД, що підтримує можливості використання збережених процедур, рекомендується скористатися цим і створювати додаток з їх використанням. Крім того, рекомендуються усі найбільш типові і часто виникаючі дії, зв'язані з обробкою даних, що утримуються в базі даних, а також операції вставки, видалення і зміни робити реалізовувати за допомогою виклику попередньо створених збережених процедур.
Розглянемо приклад створення і використання простої збереженої процедури, що дозволяє додати новий товар у таблицю Товари бази даних. Текст збереженої процедури, реалізованої в СУБД SQL Server виглядає в такий спосіб:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddProduct]
(
@nazv_t varchar(50),
@gost_t varchar(50)
)
AS
SET NOCOUNT OFF;
INSERT INTO [tovar] ([nazv], [gost]) VALUES (@nazv_t, @gost_t);
SELECT kod_tov, nazv, gost FROM tovar WHERE (kod_tov = SCOPE_IDENTITY())
Первинний ключ таблиці буде заповнюватися за рахунок механізму автоинкремента. Після розташування елементів управління для введення значень та кнопки „Додати”, необхідно створити обробник події натискання на цю кнопку, а саме:
protected void Button3_Click(object sender, EventArgs e)
{string connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd_SQL = new SqlCommand("AddProduct", con);
cmd_SQL.CommandType = CommandType.StoredProcedure;
cmd_SQL.Parameters.Add(new SqlParameter("@nazv_t", SqlDbType.VarChar,50));
cmd_SQL.Parameters["@nazv_t"].Value = TextBox2.Text;
cmd_SQL.Parameters.Add(new SqlParameter("@gost_t", SqlDbType.VarChar, 50));
cmd_SQL.Parameters["@gost_t"].Value = TextBox3.Text;
try
{
con.Open();
int k = cmd_SQL.ExecuteNonQuery();
}
finally
{
con.Close();
}
Bind();
}
Результат виконання збереженої процедури додавання наведено на рис. 3.24 – до вставки, рис. 3.25 – після вставки.
Рис. 3.24. Вигляд веб-додатку в браузері до вставки
Рис. 3.25. Вигляд веб-додатку в браузері після вставки