Ранее для отправки запросов мы напрямую добавляли данные в выражение 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 символов.