دستورات LIKE و IN در prepared statement ها

امروز قصد داریم در رابطه با دستورات LIKE و IN و رابطه ی آن ها با prepared statement ها صحبت کنیم. در واقع با اینکه استفاده از PDO بسیار آسان است اما برخی اوقات نکات ریزی نیز دارد که ممکن است شما را گمراه کند. امروز میخواهیم در همین رابطه صحبت کنیم.

دستور LIKE و prepared statement ها

استفاده از placeholders ها با دستور LIKE یکی از این نکات ریز است. در ابتدا ممکن است فکر کنید کوئری زیر بی عیب است و کار میکند:

$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE '%?%'");

اما این کوئری به خطا برخواهد خورد. قبل از توضیح چرایی این مسئله باید یادآوری کوتاهی از جلسات قبل داشته باشیم.

یادآوری:

سوال: چه پارامتر هایی را می توان bind کرد؟

پاسخ: تنها رشته ها و اعداد (فقط به صورت literal) می توانند bind شوند. مقادیر literal مقادیری هستند که مستقیما تعیین می شوند نه از طریق محاسبات و توابع دیگر. به طور مثال، عدد 1 در عبارت x = 1 از نوع literal است (مستقیما وارد سورس کد شده است) اما با اینکه عبارت (x = cos(0 (یعنی کوسینوسِ صفر) برابر با 1 است اما دیگر این 1 از نوع literal نیست، چرا که مستقیما در سورس کد تعریف نشده بلکه طی یک عملیات محاسباتی یا توسط توابع دیگر به عنوان خروجی برگردانده شده است (به عبارت دیگر، در سورس کد وارد نشده، بلکه به وجود آمده). در موارد دیگر اصلا نمی توانید از prepared statement ها در PDO استفاده کنید.

در صورتیکه با اجرای کوئری ها آشنا نیستید لطفا لینک زیر را حتما مطالعه کنید:

آیا فهمیدید مشکل از کجاست؟ ما در یادآوری بالا و در جلسات قبل گفته بودیم که placeholder فقط می توانند نماینده ی یک داده ی literal کامل باشند؛ یعنی یک رشته یا عدد.

placeholder ها در هیچ حالتی نمی توانند نماینده ی قسمتی از یک literal و یا قسمت دلخواهی از یک کوئری SQL باشد. بنابراین هنگامی که با LIKE کار می کنیم باید ابتدا literal خود را به صورت کامل آماده کرده و سپس آن را به کوئری ارسال کنیم:

$search = "%$search%";
$stmt  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$data = $stmt->fetchAll();

دستور IN و prepared statement ها

همان طور که بالاتر هم گفتیم، تعویض قسمت خاصی از کوئری با یک placeholder غیر ممکن است و هر رشته ای که داخل placeholder بگذارید به عنوان یک رشته ی literal واحد به کوئری پاس داده می شود. به طور مثال '1,2,3' به عنوان یک رشته bind خواهد شد:

SELECT * FROM table WHERE column IN ('1,2,3')

کد بالا تنها به دنبال یک مقدار خواهد گشت؛ همان مقداری که داخل رشته است.

اگر دنبال چند مقدار مختلف هستید باید آن را به صورت رشته های مختلف درآورید:

SELECT * FROM table WHERE column IN ('1','2','3')

بنابراین برای مقادیری که با ویرگول انگلیسی جدا می شوند (مثل دستور ()IN) باید از چندین ? استفاده کرده و آن ها را به صورت دستی وارد کوئری کنید:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();

شاید به راحتی mysqli نباشد اما بسیار دقیق است.

اگر placeholder های دیگر نیز در کوئری وجود دارند، می توانید از تابع ()array_merge استفاده کنید تا تمام متغیر ها را به یک آرایه ی واحد تبدیل کنید تا دیگر متغیر ها به شکل یک آرایه وارد کوئری شود:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();

یادآوری از placeholder های اسمی و موقعیتی:

$sql = 'SELECT * FROM users WHERE email = :email AND status=:status';

اگر به مثال توجه کرده باشید، فهمیده اید که PDO از placeholder های موقعیتی یا positional (در مثال -> ?) و اسمی یا named (در مثال -> email:) پشتیبانی می کند. برای استفاده از placeholder های اسمی از دو نقطه استفاده می کنید (مانند مثال). نامی که برای آن انتخاب می کنید باید از بین حروف، اعداد و آندرلاین انتخاب شده باشد. همچنین توجه داشته باشد که هیچ نوع quotation ای این نوع از placeholder ها را احاطه نکرده است.

نکته ی اول: شما نمی توانید از placeholder های موقعیتی و اسمی به طور همزمان در یک کوئری استفاده کنید.

نکته ی دوم: توجه داشته باشید که placeholder های موقعیتی به شما کمک می کنند کد های کوتاه تری بنویسید اما نسبت به ترتیب آرگومان های پاس داده شده به ()execute حساس هستند؛ یعنی دقیقا به ترتیب placeholder ها در کوئری اصلی هستند (به طور مثال علامت سوال اول، اولین آرگومان را می گیرد، دومی نیز آرگومان دومی را می گیرد و الی آخر).

از طرف دیگر placeholder های اسمی کد را طولانی تر می کنند اما نسبت به موقعیت حساس نیستند (چرا که در آرایه ی متناظر با جفت های key و value تنظیم می شوند – ر.ک مثال بالا).

نکته ی سوم: در placeholder های اسمی، زمانی که بخواهید آرایه را به ()execute تحویل دهید نیازی به گذاشتن علامت دو نقطه نیست (ر.ک مثال بالا).

حالا اگر از placeholder های اسمی استفاده کنید کد کمی پیچیده تر می شود چرا که باید یک سری از آن ها را مانند id0,:id1,:id2: درست کنید:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
foreach ($ids as $i => $item)
{
    $key = ":id".$i;
    $in .= "$key,";
    $in_params[$key] = $item; // collecting values into key-value array
}
$in = rtrim($in,","); // :id0,:id1,:id2

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

خوشبختانه برای placeholder های اسمی نیازی به پیروی از ترتیب نداریم، بنابراین می توانیم توابع خود را با هر ترتیبی ادغام کنیم.

خلاصه ی مقاله

در این قسمت با دستورات LIKE و IN و رابطه ی آن ها با prepared statement ها آشنا شدیم و دیدیم که جزئیات ریزی در رابطه با این دستورات موجود است که ممکن است کد ما را با خطا مواجه کند. ما راه حل دور زدن این مشکل را معرفی کردیم و از اینجا به بعد مسئولیت رعایت این نکات روی دوش شماست!