Ранее для отправки запросов мы напрямую добавляли данные в выражение sql. Например, для отправки запроса на добавление данных в БД применялось следующее выражение:
string sqlExpression = $"INSERT INTO Users (Name, Age) VALUES ('{name}', {age})";
В данном случае предполагается, что значения для переменных name и age вводит пользователь.
Что если переменная name получит следующее значение:
name = "Tom',10);INSERT INTO Users (Name, Age) VALUES('Hack";
В этом случае sql-выражение в итоге будет выглядеть следующим образом:
INSERT INTO Users (Name, Age) VALUES ('Tom',10);INSERT INTO Users (Name, Age) VALUES('Hack,{age})";
В итоге в базу данных будет добавлено два объекта. Это относительно безобидный вид подмены sql-выражения, но реальные возможности встраивания зловредных скриптов таковы, что можно вообще потерять данные в БД, если предоставить пользователям подобным образом добавлять данные. Чтобы выйти из этой ситуации, в sql-командах используются параметры.
Для определения параметров используется объект SqlParameter. Этот объект имеет ряд конструкторов, наиболее используемые из них:SqlParameter()
SqlParameter(String, Object): первый параметр конструктора передает имя, а второй - значение параметра
SqlParameter(String, SqlDbType): первый параметр конструктора передает имя параметра, а второй - его тип в виде объекта SqlDbType
SqlParameter(String, SqlDbType, Int32): первый параметр конструктора передает имя параметра, второй - его тип в виде объекта SqlDbType, а третий - размер
SqlParameter(String, SqlDbType, Int32, String): конструктор последовательно принимает значения для имя параметра, его типа, размера и имени стобца в таблице
Для конфигурации параметров можно использовать их свойства, среди которых следует выделить следующие:
SqlDbType: задает или устанавливает тип параметра в виде типа SqlDbType
Direction: хранит направление параметра, которое указывает, будет ли параметр входным или выходным, или он будет сочетать оба варианта, либо он будет возвращать данные из хранимой процедуры
IsNullable: указывает, допускает ли параметр значение null
ParameterName: представляет имя параметра
Size: хранит размер данных параметра
Value: хранит значение параметра
После определения параметров они добавляются в коллекцию Parameters объекта SqlCommand.
Применим параметры для добавления данных:
using Microsoft.Data.SqlClient;
using System;
using System.Threading.Tasks;
namespace HelloApp
{
class Program
{
static async Task Main(string[] args)
{
string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;";
// данные для добавления
int age = 36;
string name = "Tom',10);INSERT INTO Users (Name, Age) VALUES('Hack";
// выражение SQL для добавления данных
string sqlExpression = "INSERT INTO Users (Name, Age) VALUES (@name, @age)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
SqlCommand command = new SqlCommand(sqlExpression, connection);
// создаем параметр для имени
SqlParameter nameParam = new SqlParameter("@name", name);
// добавляем параметр к команде
command.Parameters.Add(nameParam);
// создаем параметр для возраста
SqlParameter ageParam = new SqlParameter("@age", age);
// добавляем параметр к команде
command.Parameters.Add(ageParam);
int number = await command.ExecuteNonQueryAsync();
Console.WriteLine($"Добавлено объектов: {number}");
// вывод данных
command.CommandText = "SELECT * FROM Users";
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows) // если есть данные
{
// выводим названия столбцов
string columnName1 = reader.GetName(0);
string columnName2 = reader.GetName(1);
string columnName3 = reader.GetName(2);
Console.WriteLine($"{columnName1}\t{columnName3}\t{columnName2}");
while (await reader.ReadAsync()) // построчно считываем данные
{
object id = reader.GetValue(0);
object name2 = reader.GetValue(2);
object age2 = reader.GetValue(1);
Console.WriteLine($"{id} \t{name2} \t{age2}");
}
}
}
}
Console.Read();
}
}
}
В данном случае в конструктор передается название параметра и его значение. Причем название параметров начинается со знака @ и должно совпадать с тем названием, которое используется в sql-выражении:
string sqlExpression = "INSERT INTO Users (Name, Age) VALUES (@name, @age)";
После определения параметра он добавляется в коллекцию параметров команды.
SqlParameter nameParam = new SqlParameter("@name", name);
command.Parameters.Add(nameParam);
При выполнении команды на место параметров в sql-выражении подставляются их значения. При этом не важно, что параметр @name в значении определяет еще одну команду INSERT - все его значение будет добавлено в столбец name в таблице Users.
В итоге последняя добавленная строка будет содержать в столбце Name довольно длинное имя:
Добавлено объектов: 1
Id Name Age
1 Alice 32
2 Bob 28
3 Tom 10
4 Hack 36
5 Tom',10);INSERT INTO Users (Name, Age) VALUES('Hack 36
Подобным образом параметры можно включать и в другие типы запросов, а не только при добавлении данных.
С помощью дополнительных конструкторов класса SqlParameter, а также его свойств можно дополнительно определить тип и размер параметра.
Так, в статье Выполнение команд и SqlCommand была создана таблица Users, в которой столбец Name имеет ограничение в 100 символов. И если мы попробует добавить в этот столбец строку, которая имеет больше 100 символов, то мы столнемся с исключением. В этом случае мы можем задать для параметра соответствующий размер.
Для описания типа параметра применяется перечисление SqlDbType из пространства имен System.Data. Его значения сопоставляются с типами T-SQL и C# следующим образом:
Тип sql | Тип .NET | Значение SqlDbType |
bigint | Int64 | BigInt |
binary | Byte[] | Binary |
bit | Boolean | Bit |
char | String и Char[] | Char |
date | DateTime | Date |
datetime | DateTime | DateTime |
datetime2 | DateTime | DateTime2 |
decimal | Decimal | Decimal |
float | Double | Float |
image | Byte[] | Image |
int | Int32 | Int |
money | Decimal | Money |
nchar | String и Char[] | NChar |
ntext | String и Char[] | NText |
nvarchar | String и Char[] | NVarChar |
real | Single (float) | Real |
smalldatetime | DateTime | SmallDateTime |
smallint | Intl6 | SmallInt |
smallmoney | Decimal | SmallMoney |
sql_variant | Object | Variant |
time | TimeSpan | Time |
timestamp | Byte[] | Timestamp |
tinyint | Byte | TinyInt |
uniqueidentifier | Guid | UniqueIdentifier |
varbinary | Byte[] | VarBinary |
varchar | String и Char[] | VarChar |
Стоит учитывать, что для строковых типов, например, для NVarChar или VarChar, размер задается в символах, для остальных типов размер устанавливается в байтах.
Например, зададим для параметра тип и размер:
using Microsoft.Data.SqlClient;
using System;
using System.Threading.Tasks;
namespace HelloApp
{
class Program
{
static async Task Main(string[] args)
{
string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;";
// данные для добавления
int age = 27;
string name = "Sam";
// выражение SQL для добавления данных
string sqlExpression = "INSERT INTO Users (Name, Age) VALUES (@name, @age)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
SqlCommand command = new SqlCommand(sqlExpression, connection);
// создаем параметр для имени
SqlParameter nameParam = new SqlParameter("@name", System.Data.SqlDbType.NVarChar, 100);
// определяем значение
nameParam.Value = name;
// также можно определить тип и размер через свойства
// nameParam.SqlDbType = System.Data.SqlDbType.NVarChar;
// nameParam.Size = 100;
// добавляем параметр к команде
command.Parameters.Add(nameParam);
// создаем параметр для возраста
SqlParameter ageParam = new SqlParameter("@age", age);
// добавляем параметр к команде
command.Parameters.Add(ageParam);
int number = await command.ExecuteNonQueryAsync();
Console.WriteLine($"Добавлено объектов: {number}");
}
Console.Read();
}
}
}
В данном случае параметр nameParam имеет тип SqlDbType.NVarChar и длину в 100 символов.
Если размер параметра меньше, чем формат вводных данных, то данные усекаются до нужного размера. Например, если задан следующий параметр:
SqlParameter nameParam = new SqlParameter("@name", System.Data.SqlDbType.NVarChar, 3);
nameParam.Value = "Donald";
В данном случае в таблицу сохранится строка "Don", так как параметр содержит ограничение - не более 3 символов.