مدیریت کاراکترهای خاص در SQL Server TSQL

مدیریت کاراکترهای خاص در SQL Server: راهنمای جامع String Escape برای T-SQL

گاهی اوقات در هنگام کار با SQL Server، با مشکلاتی در مدیریت کاراکترهای خاص در رشته‌ها، به‌ویژه نقل قول تکی (Single Quote یا Apostrophe) مواجه می‌شویم. این کاراکترها می‌توانند باعث خطاهای سینتکسی شوند، به‌خصوص زمانی که با رشته‌های پویا (Dynamic SQL) یا داده‌های ورودی کاربر سروکار داریم. درک چگونگی فرار دادن (Escaping) این کاراکترها برای جلوگیری از خطاها و حفظ یکپارچگی داده‌ها ضروری است.

فرض کنید می‌خواهید نام “O’Malley” را در یک کوئری استفاده کنید. اگر مستقیماً آن را وارد کنید، SQL Server آن را به‌عنوان یک رشته نامعتبر تشخیص می‌دهد:

SELECT 'O'Malley'

اجرای دستور بالا منجر به خطایی شبیه به “Unclosed quotation mark after the character string ‘Malley’.” (نقل قول بسته نشده پس از رشته کاراکتری ‘Malley’.) می‌شود. این خطا نشان می‌دهد که SQL Server ‘O’ را به‌عنوان یک رشته مجزا می‌بیند و باقیمانده ‘Malley’ را به‌عنوان ادامه دستور، که منطقی نیست.

**روش اول: تکرار نقل قول تکی (Double Single Quotes)**

رایج‌ترین و ساده‌ترین راه برای فرار دادن یک نقل قول تکی در SQL Server، تکرار آن است. به این معنی که هرجا نقل قول تکی نیاز دارید، دو بار آن را بنویسید. این روش به SQL Server می‌گوید که نقل قول دوم، بخشی از رشته است و نه پایان آن.

SELECT 'O''Malley'

با این تغییر، SQL Server اکنون “O’Malley” را به‌درستی به‌عنوان یک رشته واحد تفسیر می‌کند و کوئری بدون خطا اجرا می‌شود. این تکنیک به‌طور گسترده در رویه‌های ذخیره‌شده (Stored Procedures) و کوئری‌های پویا مورد استفاده قرار می‌گیرد.

**روش دوم: استفاده از تابع QUOTENAME()**

تابع داخلی `QUOTENAME()` در SQL Server یک راه حل عالی برای افزودن محدودکننده‌ها (delimiters) به یک رشته است، به‌ویژه برای نام‌های آبجکت‌ها مانند ستون‌ها یا جداول. اما می‌توان از آن برای فرار دادن کاراکترهای خاص در رشته‌ها نیز بهره برد، اگرچه بیشتر برای سناریوهای SQL پویا استفاده می‌شود که نام آبجکت‌ها را شامل می‌شوند.

سینتکس این تابع به‌صورت زیر است:

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

* `character_string`: رشته‌ای که می‌خواهید آن را نقل قول کنید.
* `quote_character`: کاراکتری که می‌خواهید رشته با آن محدود شود (اختیاری). مقادیر رایج شامل براکت (`[]`)، پرانتز (`()`)، بریس (`{}`)، نقل قول دوتایی (`””`) یا نقل قول تکی (`”`) هستند.

اگر `quote_character` را مشخص نکنید، پیش‌فرض آن براکت مربعی (`[]`) است. برای مثال، برای قرار دادن ‘O’Malley’ در نقل قول تکی:

SELECT QUOTENAME('O''Malley', '''')

نتیجه این دستور `”’O”Malley”’` خواهد بود. توجه داشته باشید که این تابع رشته را با دو نقل قول تکی در ابتدا و انتها برمی‌گرداند تا آماده استفاده در یک دستور SQL باشد. همچنین، اگر بخواهید رشته با نقل قول تکی محصور شود، باید کاراکتر نقل قول را به شکل `””` (چهار نقل قول تکی) پاس دهید. این چهار نقل قول به این معنی است که دو نقل قول برای خود کاراکتر نقل قول تکی و دو نقل قول دیگر برای فرار دادن آن‌ها استفاده شده است.

**روش سوم: ایجاد یک تابع سفارشی با REPLACE()**

برای کنترل بیشتر یا در سناریوهایی که نیاز به منطق پیچیده‌تری دارید، می‌توانید یک تابع سفارشی (User-Defined Function – UDF) با استفاده از تابع `REPLACE()` ایجاد کنید. این روش به شما امکان می‌دهد تا هر بار که نیاز به فرار دادن رشته‌ای دارید، از تابع خودتان استفاده کنید.

نمونه‌ای از یک تابع سفارشی که نقل قول تکی را دو بار تکرار می‌کند:

CREATE FUNCTION dbo.EscapeString (@InputString NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN REPLACE(@InputString, '''', '''''');
END;

در این تابع، ما هر وقوع از `”’` (یک نقل قول تکی) را با `””’` (دو نقل قول تکی) جایگزین می‌کنیم. سپس می‌توانید این تابع را مانند هر تابع دیگری در SQL Server فراخوانی کنید:

SELECT dbo.EscapeString('This is O''Malley''s string with an apostrophe.') AS EscapedString;

این روش به شما یک تابع قابل استفاده مجدد می‌دهد که می‌تواند در هر جای کوئری‌های شما برای فرار دادن رشته‌ها به کار رود و به جلوگیری از حملات تزریق SQL (SQL Injection) در کنار پارامترسازی کوئری‌ها کمک می‌کند.

**روش چهارم: استفاده از تابع STRING_ESCAPE() (فقط SQL Server 2012 به بعد و برای خروجی JSON)**

SQL Server 2012 و نسخه‌های جدیدتر، تابع `STRING_ESCAPE()` را معرفی کرده‌اند. این تابع برای فرار دادن کاراکترهای خاص، اما *فقط برای خروجی JSON* طراحی شده است. این بدان معنی است که این تابع کاراکترها را به گونه‌ای تبدیل می‌کند که رشته برای استفاده در یک سند JSON معتبر باشد.

سینتکس این تابع به‌صورت زیر است:

STRING_ESCAPE ( character_string , type )

* `character_string`: رشته‌ای که می‌خواهید فرار دهید.
* `type`: نوع فرار دادن. در حال حاضر، فقط `json` پشتیبانی می‌شود.

برای مثال، فرار دادن یک رشته JSON:

SELECT STRING_ESCAPE('{"key": "value"}', 'json') AS JsonEscaped;

این تابع کاراکترهای خاص JSON مانند نقل قول دوتایی، بک‌اسلش، اسلش، بک‌اسپیس، فرم فید، خط جدید و تب را با پیشوند بک‌اسلش فرار می‌دهد. اگرچه هدف اصلی آن برای JSON است، اما می‌تواند نقل قول تکی را نیز به عنوان بخشی از فرار JSON مدیریت کند.

برای فرار دادن یک رشته حاوی نقل قول تکی با استفاده از `STRING_ESCAPE()`:

SELECT STRING_ESCAPE('O''Malley''s string', 'json') AS EscapedString;

نتیجه این دستور، رشته‌ای با نقل قول تکی‌های فرار داده شده به روش JSON خواهد بود (یعنی تبدیل به `\’` نخواهند شد بلکه معمولاً فقط در یک context بزرگتر JSON به درستی قرار می‌گیرند یا بسته به محتوا خود نقل قول تکی نیاز به فرار به روش JSON ندارد، مگر اینکه بخشی از یک رشته JSON literal باشد). این تابع برای سناریوهایی که داده‌های SQL را به فرمت JSON خروجی می‌دهید، بسیار مفید است.

مدیریت صحیح کاراکترهای خاص در T-SQL برای جلوگیری از خطاها، حفظ امنیت و اطمینان از صحت داده‌ها حیاتی است. با استفاده از تکنیک‌های ذکر شده، می‌توانید با اطمینان بیشتری با رشته‌ها و کوئری‌های پویا در SQL Server کار کنید.