طَبِّقْهاتعلّم المبدأ، وطبّقه بيدكمقرر 122 · برمجة قواعد البيانات
الدرس 23 · الوحدة 10: تحسين الاستعلامات باستخدام لغة البرمجة الإجرائية

SQL الديناميكي الآمن: EXECUTE و format

أوامر تُبنى وقت التشغيل: قوة كبيرة، وباب خطير إن لم يُحكم. ⏱ نحو 45 دقيقة.

بعد هذا الدرس ستستطيع أن:
  • تنفّذ أوامر مبنية وقت التشغيل بـ EXECUTE.
  • تبني الأمر بـ format و %I و %L.
  • تشرح حقن SQL وتجربه.
  • تمنع الحقن بـ USING وقوائم المسموح.
جرّب بنفسك أسئلة التقييم

١حين لا تعرف اسم الجدول مسبقًا

الأوامر داخل PL/pgSQL ثابتة: أسماء الجداول والأعمدة مكتوبة في الكود. لكن أحيانًا يأتي الاسم في متغير: «عدّ صفوف كل جدول»، أو «رتّب حسب العمود الذي اختاره المستخدم». هنا نبني الأمر نصًّا وننفّذه بـ EXECUTE:

query.sql
DO $$DECLARE  t text;  n bigint;BEGIN  FOR t IN SELECT table_name FROM information_schema.tables           WHERE table_schema = 'public' ORDER BY table_name LOOP    EXECUTE format('SELECT count(*) FROM %I', t) INTO n;    RAISE NOTICE '%: % صفًّا', t, n;  END LOOP;END $$;
NOTICE: categories: 5 صفًّا
NOTICE: customers: 8 صفًّا
NOTICE: order_items: 21 صفًّا
NOTICE: orders: 12 صفًّا
NOTICE: products: 12 صفًّا
✓ نُفّذت الكتلة

format يبني النص، و%I يضع اسم الجدول بأمان (Identifier)، وINTO n يحفظ النتيجة.

رمز formatيضعمثال الناتج
%Iاسم جدول أو عمود (يُحاط بعلامتي " عند الحاجة)"order items"
%Lقيمة نصية (تُحاط بعلامتي ' وتُهرَّب)'O''Brien'
%sنص كما هو بلا حمايةللأرقام الموثوقة فقط

٢الخطر: حقن SQL

بناء الأمر بلصق النصوص (||) يفتح بابًا خطيرًا: إن كتب المستخدم علامة ' في المدخلات، صار جزءًا من الأمر لا من البيانات:

query.sql
CREATE FUNCTION find_unsafe(who text) RETURNS bigintLANGUAGE plpgsqlAS $$DECLARE  n bigint;BEGIN  EXECUTE 'SELECT count(*) FROM customers WHERE name = ''' || who || ''''  INTO n;  RETURN n;END;$$; SELECT find_unsafe('سارة')            AS بحث_عادي,       find_unsafe($q$' OR '1'='1$q$) AS بحث_محقون;
✓ أُنشئت الدالة
النتيجة · صف واحد
بحث_عاديبحث_محقون
18

المدخل ' OR '1'='1 حوّل الشرط إلى «صحيح دائمًا» فعدّ كل العملاء. في نظام حقيقي يعني هذا تسريب البيانات، أو حذفها.

العلاج: لا تلصق القيم أبدًا. مرّرها بـ USING (أفضل)، أو ضعها بـ %L:

query.sql
CREATE FUNCTION find_safe(who text) RETURNS bigintLANGUAGE plpgsqlAS $$DECLARE  n bigint;BEGIN  EXECUTE 'SELECT count(*) FROM customers WHERE name = $1'  INTO n  USING who;                     -- القيمة تُمرَّر منفصلة عن نص الأمر  RETURN n;END;$$; SELECT find_safe('سارة')            AS بحث_عادي,       find_safe($q$' OR '1'='1$q$) AS بحث_محقون;
✓ أُنشئت الدالة
النتيجة · صف واحد
بحث_عاديبحث_محقون
10

المدخل الخبيث صار مجرد اسم يُبحث عنه، فلم يجد شيئًا.

٣القاعدة الذهبية

ما يتغيركيف تضعه
قيمة (اسم عميل، مدينة، رقم)EXECUTE … $1 … USING v (الأفضل) أو %L
اسم جدول أو عمودformat('… %I …', name)، والأفضل مقارنته بقائمة مسموحة
كلمة SQL (ASC أو DESC)اختر من قائمة ثابتة بـ CASE أو IF، ولا تأخذها من المستخدم مباشرة
query.sql
CREATE FUNCTION products_sorted(col text, dir text DEFAULT 'ASC')RETURNS SETOF productsLANGUAGE plpgsqlAS $$BEGIN  IF col NOT IN ('name', 'price', 'stock') THEN    RAISE EXCEPTION 'عمود ترتيب غير مسموح: %', col;  END IF;  RETURN QUERY EXECUTE format('SELECT * FROM products ORDER BY %I %s LIMIT 3',                              col, CASE WHEN upper(dir) = 'DESC' THEN 'DESC' ELSE 'ASC' END);END;$$; SELECT name, price FROM products_sorted('price', 'desc');
✓ أُنشئت الدالة
النتيجة · 3 صفوف
nameprice
حاسب محمول3200
جهاز لوحي1450
شاشة 27 بوصة980

توقّع وتحقّق

أفضل طريقة لتمرير قيمة إلى أمر ديناميكي:

الرمز المناسب لاسم جدول في format:

حقن SQL يحدث حين:

مقابل EXECUTE في Oracle:

الخلاصة

تحدٍّ برمجي: عدّاد آمن لأي جدول

متقدم

اكتب دالة count_where(tbl text, col text, val text) تُرجع عدد صفوف الجدول tbl التي يساوي فيها العمود col القيمة val، بأمان من الحقن.

المطلوب:
  • ترجع bigint، وتبني الأمر بـ format و %I لاسمي الجدول والعمود.
  • القيمة تُمرَّر بـ USING.
  • مدخل خبيث في val يُرجع 0، واسم جدول خبيث يفشل دون أن يضر.
👀 اعرض الناتج المطلوب
✓ التحقق الآلي من حلّك:
  • الكود يعمل بلا أخطاء
  • عملاء الرياض ← 3
  • الطلبات المدفوعة ← 5
  • قيمة خبيثة ← 0
  • اسم جدول خبيث يفشل
  • جدول البنود ما زال سليمًا
  • format مع %I
  • USING

💡 تلميح 1
النص: format('SELECT count(*) FROM %I WHERE %I = $1', tbl, col).
💡 تلميح 2
ثم INTO n USING val;

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql
CREATE FUNCTION count_where(tbl text, col text, val text) RETURNS bigintLANGUAGE plpgsqlAS $$DECLARE  n bigint;BEGIN  EXECUTE format('SELECT count(*) FROM %I WHERE %I = $1', tbl, col)  INTO n  USING val;  RETURN n;END;$$;
انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس