SQL Server Prepared Statements: راهنمای sp_prepare و sp_execute برای کارایی و امنیت

افزایش کارایی و امنیت SQL با Prepared Statements: راهنمای sp_prepare و sp_execute در SQL Server

عبارات آماده (Prepared Statements) در SQL Server ابزاری قدرتمند برای افزایش کارایی و امنیت هستند، به‌ویژه هنگام کار با SQL پویا. این قابلیت به سرور اجازه می‌دهد تا یک عبارت SQL را یک بار تجزیه، کامپایل و بهینه‌سازی کند و سپس آن را چندین بار با مقادیر پارامتر مختلف اجرا کند. این رویکرد به کاهش سربار پردازشی کمک کرده و خطر حملات تزریق SQL را نیز به حداقل می‌رساند. تمرکز اصلی ما بر رویه‌های سیستمی sp_prepare و sp_execute است که هسته اصلی پیاده‌سازی این الگو را تشکیل می‌دهند.

برای استفاده از این قابلیت، از رویه سیستمی sp_prepare استفاده می‌کنیم که یک عبارت SQL را برای اجرای بعدی آماده می‌کند. نحو کلی آن به شکل زیر است:

sp_prepare
    @handle
OUTPUT,
    @params,
    @stmt,
    @options

در این نحو:

• @handle: یک پارامتر خروجی (integer) است که یک شناسه برای عبارت آماده شده برمی‌گرداند. این شناسه برای ارجاع به عبارت در آینده استفاده می‌شود.
• @params: یک رشته (NVARCHAR) حاوی تعاریف پارامترها برای عبارت آماده شده است (مانند ‘NVARCHAR(10)’). این پارامترها به صورت جداگانه از عبارت اصلی تعریف می‌شوند.
• @stmt: رشته‌ای حاوی عبارت T-SQL است که قرار است آماده شود. این عبارت می‌تواند شامل placeholder برای پارامترها باشد.
• @options: یک پارامتر اختیاری (integer) است که برای گزینه‌های خاص استفاده می‌شود، اما معمولاً 0 یا NULL است.

پس از آماده‌سازی عبارت با sp_prepare، می‌توان آن را با استفاده از رویه سیستمی sp_execute اجرا کرد. نحو کلی sp_execute به شکل زیر است:

sp_execute
    @handle,
    @param1,
    @param2,
    ...

در این نحو:

• @handle: شناسه (integer) عبارت آماده شده است که توسط sp_prepare بازگردانده شده بود.
• @param1, @param2, …: مقادیر پارامترهایی هستند که به ترتیب در تعریف @params برای sp_prepare مشخص شده‌اند.

هنگامی که کار با یک عبارت آماده شده به پایان می‌رسد، ضروری است که منابع اختصاص یافته به آن را آزاد کنید. این کار با استفاده از رویه سیستمی sp_unprepare انجام می‌شود. نحو آن به شکل زیر است:

sp_unprepare
    @handle

در اینجا @handle همان شناسه عبارت آماده شده‌ای است که می‌خواهید منابع آن را آزاد کنید.

مثال 1: یک عبارت SELECT ساده

در این مثال، یک عبارت SELECT ساده را آماده و اجرا می‌کنیم. ابتدا یک شناسه (handle) اعلام می‌کنیم، سپس عبارت SQL را با sp_prepare آماده کرده و بلافاصله با sp_execute اجرا می‌کنیم. در نهایت، منابع را با sp_unprepare آزاد می‌کنیم.

DECLARE @handle int;
EXEC sp_prepare @handle OUTPUT,
    N'',
    N'SELECT ProductID, Name FROM Production.Product WHERE ProductID < 10;',
    0;
EXEC sp_execute @handle;
EXEC sp_unprepare @handle;

مثال 2: استفاده از پارامترها

این مثال نشان می‌دهد که چگونه می‌توانیم یک عبارت SELECT را با یک پارامتر برای فیلتر کردن نتایج آماده و اجرا کنیم. تعریف پارامتر ‘NVARCHAR(255)’ به sp_prepare داده می‌شود و سپس مقدار واقعی پارامتر ‘Blade’ به sp_execute ارسال می‌گردد.

DECLARE @handle int;
DECLARE @param1 NVARCHAR(255);
SET @param1 = N'Blade';
EXEC sp_prepare @handle OUTPUT,
    N'@param1 NVARCHAR(255)',
    N'SELECT ProductID, Name FROM Production.Product WHERE Name = @param1;',
    0;
EXEC sp_execute @handle, @param1;
EXEC sp_unprepare @handle;

مثال 3: اجرای چندین باره یک عبارت آماده شده

یکی از مزایای اصلی Prepared Statements، قابلیت اجرای چندین باره یک عبارت با مقادیر پارامتر مختلف است. در این مثال، عبارت SELECT با دو نام محصول متفاوت اجرا می‌شود.

DECLARE @handle int;
DECLARE @param1 NVARCHAR(255);
EXEC sp_prepare @handle OUTPUT,
    N'@param1 NVARCHAR(255)',
    N'SELECT ProductID, Name FROM Production.Product WHERE Name = @param1;',
    0;

SET @param1 = N'Blade';
EXEC sp_execute @handle, @param1;

SET @param1 = N'Road-650';
EXEC sp_execute @handle, @param1;

EXEC sp_unprepare @handle;

مثال 4: کار با پارامترهای خروجی (OUTPUT Parameters)

Prepared Statements می‌توانند با پارامترهای خروجی نیز کار کنند. در این مثال، تعداد کل محصولات با استفاده از تابع COUNT(*) شمارش شده و نتیجه در یک پارامتر خروجی بازگردانده می‌شود.

DECLARE @handle int;
DECLARE @param1 int;
EXEC sp_prepare @handle OUTPUT,
    N'@param1 int OUTPUT',
    N'SELECT @param1 = COUNT(*) FROM Production.Product;',
    0;
EXEC sp_execute @handle, @param1 OUTPUT;
SELECT @param1 AS TotalProducts;
EXEC sp_unprepare @handle;

مثال 5: استفاده از sp_unprepare برای آزادسازی منابع

این مثال بر اهمیت فراخوانی sp_unprepare تاکید می‌کند تا مطمئن شوید منابع اختصاص داده شده به عبارت آماده شده پس از اتمام کار، آزاد شده‌اند. عدم آزادسازی منابع می‌تواند منجر به نشت حافظه و کاهش کارایی سرور شود.

DECLARE @handle int;
EXEC sp_prepare @handle OUTPUT,
    N'',
    N'SELECT GETDATE() AS CurrentDateTime;',
    0;
-- اجرای عبارت...
EXEC sp_execute @handle;
-- اکنون منابع را آزاد کنید
EXEC sp_unprepare @handle;
-- اگر دوباره تلاش کنید شناسه را اجرا کنید، با خطا مواجه خواهید شد زیرا دیگر معتبر نیست.
-- EXEC sp_execute @handle;

مزایای استفاده از Prepared Statements

• افزایش کارایی: عبارت SQL تنها یک بار تجزیه و کامپایل می‌شود. در صورتی که نیاز باشد همان عبارت چندین بار با پارامترهای مختلف اجرا شود، این کار می‌تواند سربار پردازشی را به طور قابل توجهی کاهش دهد و عملکرد کلی برنامه‌های SQL را بهبود بخشد.
• امنیت بالاتر: با جدا کردن عبارت SQL از مقادیر پارامترها، خطر حملات تزریق SQL به شدت کاهش می‌یابد. پارامترها به عنوان داده تلقی می‌شوند و نه بخشی از کد SQL، که این امر از دستکاری غیرمجاز کوئری جلوگیری می‌کند.
• قابلیت استفاده مجدد کد: یک عبارت آماده شده را می‌توان بارها و بارها بدون نیاز به بازنویسی کل کوئری استفاده کرد، که منجر به کدی تمیزتر و قابل نگهداری‌تر می‌شود. این مزیت به خصوص در برنامه‌هایی که عملیات CRUD (ایجاد، خواندن، به‌روزرسانی، حذف) تکراری دارند، مفید است.

ملاحظات مهم در استفاده از Prepared Statements

• مدیریت Handle: اطمینان حاصل کنید که شناسه (handle) دریافتی از sp_prepare را به درستی مدیریت می‌کنید. این handle برای ارجاع به عبارت آماده شده در sp_execute و sp_unprepare ضروری است.
• آزادسازی منابع: همیشه پس از اتمام کار با یک عبارت آماده شده، از sp_unprepare برای آزادسازی منابع سیستم استفاده کنید. عدم انجام این کار می‌تواند منجر به نشت حافظه و کاهش پایداری و کارایی SQL Server شود.
• پیچیدگی: برای عبارات SQL بسیار ساده‌ای که فقط یک بار اجرا می‌شوند، سربار آماده‌سازی ممکن است از مزایای آن بیشتر باشد. استفاده از Prepared Statements بیشتر برای عبارات پیچیده یا آن‌هایی که چندین بار با پارامترهای مختلف اجرا می‌شوند، منطقی است.
• تطابق نوع داده: اطمینان حاصل کنید که نوع داده پارامترها در sp_prepare با مقادیر ارسال شده به sp_execute مطابقت دارد. عدم تطابق می‌تواند منجر به خطاهای زمان اجرا یا تبدیل‌های ضمنی ناخواسته شود.