[Перевод] Работа с SQL Server в C# с помощью скриптинга. Часть 120.01.2023 19:31

В этой статье я покажу вам, как работать с Microsoft SQL Server в C# проектах посредством скриптинга. Реализовать это можно как в Windows, так и в macOS. Поскольку Windows, очевидно, является намного более популярной целевой платформой для C# и SQL Server, больше внимания я уделю настройке под macOS. В конце концов, код для этих платформ будет одним и тем же.

Доступ к инстансу SQL Server с помощью скриптинга на самом деле дает множество преимуществ. Вот только несколько, из преимуществ, которые вы получите, настроив среду для выполнения скриптов:

  • Доступ к SQL Server с помощью скриптинга предполагает гораздо меньше кода. Большую часть функций можно факторизировать. Один оператор на скриптовом языке может выполнять под капотом множество C# операторов — язык скриптинга по сути выполняет роль функционального языка.

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

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

Простота не предшествует сложности, а вытекает из нее. — Алан Перлис (Alan Perlis)

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

В качестве языка скриптинга я буду использовать CSCS (Customized Scripting для C#). Я уже рассказывал об этом языке в нескольких мои предыдущих статьях для CODE Magazine (вы можете найти ссылки на них в конце этой статьи). CSCS — это скриптовый язык с открытым исходным кодом, который очень легко интегрировать в ваш C# проект. Открытый исходный код дает вам оговоренное выше преимущество в виде возможности модифицирования его в процессе разработки.

Среди прочего, я собираюсь раскрыть в этой статье следующие темы:

  • Работа с SQL Server в macOS, где будут выполняться все примеры, приведенные в этой статье (код CSCS идентичен в macOS и в Windows).

  • Как избежать SQL-инъекций в CSCS.

  • Я продемонстрирую вам на примере, как можно использовать простые функциональные возможности курсора SQL Server в CSCS. Он допускает более интуитивно понятный синтаксис, чем при непосредственном использовании C#.

  • Как вызывать хранимые процедуры SQL Server из CSCS и как использовать пользовательские типы данных SQL.

Установка Microsoft SQL Server в macOS

Поскольку установка и запуск SQL Server в Windows является вполне рутинной задачей, здесь вы увидите, как установить и запустить его в macOS. Чтобы запустить SQL Server на Mac, вам придется выполнить следующие шаги:

  1. Установите Docker для Mac (отсюда)

  2. Запустите только что установленное приложение Docker Desktop (оно должно быть доступно в папке «Applications»). Обычно оно расположено по следующему пути: /usr/local/bin/docker.

  3. Загрузите и установите SQL Server для Docker-образа Linux. Для запуска из вашего shell используйте следующую команду:

docker pull
mcr.microsoft.com/mssql/server:2019-latest

Если это не сработает из-за проблем с разрешениями, попробуйте запустить ту же команду с sudo (для этого вам нужно будет знать пароль администратора):

sudo docker pull
mcr.microsoft.com/mssql/server:2019-latest

  1. Затем запустите только что установленный Docker-образ следующим образом: 

docker run -d --name sql_server_demo
-e 'ACCEPT_EULA=Y'
-e 'SA_PASSWORD=mySronggPwd123' -p 1433:1433
mcr.microsoft.com/mssql/server:2019-latest

  1. Вы также можете установить консольный клиент SQL Server, запустив в своем shell следующую команду: 

npm install -g sql-cli

Если вы снова столкнетесь с какими-либо проблемами с разрешениями, то добавьте к команде выше sudo. Этот шаг не является обязательным, но настоятельно рекомендуется. Установите Microsoft Azure Data Studio, перейдя по ссылке.

Очевидно, что существует множество других бесплатных GUI-оболочек SQL Server, но я пришел к выводу, что Azure Data Studio для macOS достаточно мощная и составляет достойную конкуренцию Microsoft SQL Server Management Studio для Windows.

  1. Теперь вам осталось только проверить, что все работает так, как надо. Чтобы подключиться к вашему серверу из командной строки, используйте эту команду: 

mssql -u sa -p myStronggPwd123

Чтобы проверить установленную версию Microsoft SQL Server, просто запустите: 

SELECT @@version

Результат выполнения этой команды в моей системе продемонстрирован на рисунке 1.

Рисунок 1: Определение версии SQL ServerРисунок 1: Определение версии SQL Server

В качестве альтернативы, вы можете проверить подключение, используя Azure Data Studio, кликнув на «New Connection», а затем следуя указаниям мастера подключения, как показано на рисунке 2. Введите тот же пароль, который вы использовали при создании Docker-образа Microsoft SQL Server выше на шаге 4.

Рисунок 2: Мастер подключения Azure Data StudioРисунок 2: Мастер подключения Azure Data Studio

Настройка скриптинговой среды CSCS в C# проекте

Чтобы настроить CSCS-скриптинг в вашем проекте, для начала вам нужно будет загрузить его исходный код с https://github.com/vassilych/cscs и включить его в свой проект. Это типичный проект, который будет работать со всеми консольными или фоновыми проектами (как в Windows, так и в macOS).

Одним из способов включения CSCS Scripting Engine в Windows GUI проект является WPF (Windows Presentation Foundation) проект, доступный здесь. Еще один способ — мобильный проект Xamarin iOS / Android, который можно скачать отсюда. В конце этой статьи вы можете найти ссылки, которые описывают, как использовать оба этих проекта.

После того, как вы все настроите, проект Xamarin должен выглядеть как показано на рисунке 3. Исходный код Scripting Engine находится в разделе scripting.Shared. В случае с WPF-проектом скрипты будут находиться в разделе CSCS.

Рисунок 3: Проект Xamarin с CSCS сериптами в Visual Studio на macOSРисунок 3: Проект Xamarin с CSCS сериптами в Visual Studio на macOS

Точка входа CSCS Scripting Engine зависит от контекста проекта. Для простого консольного проекта точкой входа является статический метод файла Main.cs Main.ProcessScript(string script, string filename = ""). Для WPF-проекта точкой входа является статический метод CSCS_GUI.RunScript(string filename) в файле CSCS_GUI.cs. А для Xamarin проектов iOS и Android точкой входа служит статический метод CustomInit.InitAndRunScript(string fileName = "start.cscs") файла CustomInit.cs. Если вы решите использовать примеры проектов из упомянутых выше GitHub-репозиториев, методы, которые запускают CSCS Scripting Engine, будут запускаться автоматически на старте проекта.

Я также рекомендую установить CSCS Debugger и REPL-расширение для Visual Studio Code. Он позволяет запускать любой CSCS-скрипт во время выполнения (смотрите ссылки для скачивания и использования в конце статьи). Чтобы иметь возможность подключиться к вашему CSCS-проекту из Visual Studio Code, в стартовый CSCS-скрипт должна быть включена следующая строка (по умолчанию это будет start.cscs): 

StartDebugger();

Создание базы данных и таблиц в SQL Server

После установки SQL Server и интеграции CSCS Scripting Engine в ваш проект вы можете приступать к созданию базы данных SQL Server, которую мы будем использовать в последующих примерах.

Это можно сделать в самом CSCS. Все функции SQL Server описаны в таблице 1. Сначала вам нужно создать новую базу данных: 

SQLConnectionString("Data Source=localhost,1433;
    Initial Catalog=master;Database=master;
    User Id=sa;Password=myStronggPwd123");

SQLCreateDB("CSCS");

Первый оператор инициализирует строку подключения к SQL Server (с базой данных master), а второй — создает новую базу данных. Также, так как вы можете запустить любой SQL-оператор с помощью функции SQLNonQuery(), в вашем арсенале есть еще один простой способ создания базы данных: 

SQLNonQuery("CREATE DATABASE CSCS");

Далее вам нужно создать таблицу с пользователями. Обратите внимание, что в строке подключения к SQL Server вам теперь нужно указать новую базу данных CSCS, чтобы новая таблица была создана там: 

SQLConnectionString(
    "Data Source=localhost,1433;
    Initial Catalog=CSCS;Database=CSCS;
    User Id=sa; Password=myStronggPwd123");

SQLNonQuery("CREATE TABLE Users (
    ID int NOT NULL IDENTITY PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Email varchar(255),
    Salary real,
    Timestamp datetime not null default current_timestamp)");

Обратите внимание на то, как мы создали таблицу Users: нет необходимости указывать ID пользователя и таймстемп: первому будет присвоено следующее по порядку целое число, а в качестве второго будет использоваться текущий таймстемп.

Давайте заполним только что созданную таблицу Users некоторыми тестовыми данными: 

columns = "FirstName,LastName,Email,Salary";
SQLInsert("Users", columns, ["John", "Johnson", "john@gmail.com", 45000]);
SQLInsert("Users", columns, ["Juan", "Perez", "juan@gmail.mx", 75000]);
SQLInsert("Users", columns, ["Johannes", "Dei", "dei@my.ch", 65000]);
SQLInsert("Users", columns, ["Jean", "Cap", "jean@my.fr", 49000]);
SQLInsert("Users", columns, ["Giovanni", "Vasco", "gv@my.it", 34000]);
SQLInsert("Users", columns, ["Joan", "Lee", "joan@my.es", 74000]);

После что таблица будет выглядеть, как показано на рисунке 4:

Рисунок 4: Таблица Users после заполнения тестовыми даннымиРисунок 4: Таблица Users после заполнения тестовыми данными

Список реализованных в настоящее время CSCS-функций, связанных с SQL Server, вы можете найти в таблице 1.

Теперь мы можем начать нашу работу с SQL через CSCS-скриптинг.

Базовые функции SQL Server

Самый простой способ использовать скрипты во время выполнения после запуска основной программы — использовать  REPL-расширение CSCS для Visual Studio Code. С ним вы сможете запустить новый скрипт после запуска программы. Этот REPL-функционал можно использовать следующим образом: выберите код, который вы хотите запустить, а затем нажмите Ctrl + 8 в Windows или Command + 8 в macOS.

На рисунке 5 показано, как можно запросить информацию о столбцах созданной вами таблицы Users.

Рисунок 5: Запуск CSCS из Visual Studio Code с помощью REPL-расширения CSCSРисунок 5: Запуск CSCS из Visual Studio Code с помощью REPL-расширения CSCS

Команда SQLTableColumn(tableName) возвращает список имен столбцов вместе с их типами. Если вам нужны только имена столбцов, укажите второй аргумент («onlyNames» должен иметь значение true). Подробности смотрите в таблице 1.

Функция SQLQuery() используется для запуска Select -операторов и получения списка результатов. Рассмотрим следующий оператор SQLQuery(): 

results = SQLQuery("SELECT FirstName,Salary
    FROM Users WHERE Salary > 50000
    ORDER BY Salary");

В результате выполнения этого оператора переменной будет присвоен список, содержащий четыре элемента: 

[[FirstName, Salary], [Johannes, 65000], [Joan, 74000] [Juan, 75000]]

Каждый элемент в возвращенном списке также является списком, которых содержит два элемента для каждого из столбцов в операторе Select. Первый элемент возвращаемого списка всегда содержит имена столбцов, а остальные содержат сами строки. Таким образом, возвращаемый список по факту представляет из себя двумерный массив: results[0][0] = «FirstName», results[1][1] = 65000 и т. д.

Функция SQLNonQuery() позволяет выполнять все остальные операторы SQL Server. Она может обновлять таблицы, удалять строки, создавать новые таблицы или хранимые процедуры и т. д. Эта функция возвращает количество затронутых строк в случае операторов UPDATE, DELETE или INSERT. В остальных случаях она всегда возвращает -1.

Вот один из примеров использования этой функции: 

updated = SQLNonQuery("UPDATE Users SET Salary = 48500 WHERE ID = 4"); // returns 1

После выполнения приведенного выше оператора переменная updated будет равна 1 (затронута всего одна строка). Ниже вас ждет еще пара примеров использования функции SQLNonQuery().

Скриптинг и SQL-инъекции

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

SQL-инъекция — это атака с внедрением кода, в рамках которой злоумышленники имеют возможность выполнять вредоносные SQL-операторы, чтобы либо повредить сервер, либо извлечь данные, к которым у них не должно быть доступа.

Например, с помощью атаки с внедрением кода через поле для ввода данных вы можете получить адрес электронной почты пользователя, а затем извлечь информацию о пользователе следующим образом: 

SQLQuery("SELECT * FROM Users WHERE Email='" + email + "'");

Затем злоумышленник может ввести этот текст вместо электронной почты: 

"x@x.com' or 1=1 --";

Далее этот аргумент может попасть в SQLQuery, что будет выглядеть следующим образом: 

SQLQuery("SELECT * FROM Users WHERE Email='x@x.com' or 1=1 --'");

Этот запрос возвращает всех пользователей, поскольку условие 1=1 всегда истина! Чтобы избежать такого рода хакерских атак, можно использовать следующие средства защиты:

  • Используйте хранимые процедуры (им посвящен следующий раздел).

  • Используйте параметризованные параметры (это мы тоже разберем чуть дальше).

Чтобы исправить приведенный выше Select-оператор, можно использовать параметризованный вызов, в котором вы дополнительно предоставляете список параметров для запроса. Этот список в свою очередь будет состоять из списков из двух элементов, где первый элемент — имя параметризованного параметра (такое же, как используется в запросе), а второй элемент — фактическое значение. Вот так будет выглядеть «безопасный» вызов SQL-запроса: 

email = "juan@gmail.mx";
SQLQuery("SELECT * FROM Users WHERE Email=@0", [["@0", email]]);

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

updated = SQLNonQuery("UPDATE Users SET Salary = @0 WHERE ID = @1", [["@0", 48500], ["@1", 4]]);

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

А пока приглашаем всех желающих на открытое занятие, на котором поговорим о модульном тестировании кода SQL Server и использовании для этого tSqlt. Занятие состоится уже сегодня вечером, записаться можно по ссылке.

Также приходите на бесплатный урок «Статический анализатор кода на Roslyn своими руками». На этом занятии разберем по шагам создание собственного статического анализатора кода на базе Roslyn API. Регистрация доступна на странице курса «C# Developer. Professional».

© Habrahabr.ru