CODEWITHSUNDEEP
phpsqldatabasepdoprepared-statement
Mitchell Layzell
Mitchell Layzell

Reputation: 3058

PHP PDO statement with varying parameter counts

I've recently started using php PDO to prevent from SQL injection, I'm having a problem with the statement though as it will change depending on the parameters I'm passing through the URL to change the pages results. The part that I'm struggling on is how can I add the $_GET['cat_id'] for when the URL has a cat_id to my execute array. Here's a working version but obviously is prone to SQL injection, thanks in advance for any help!

PHP

$and = '';
if (isset($_GET['cat_id'])) {
    $and = "AND art_cat_id = ".$_GET['cat_id'];
}   

$statement_article = $db->prepare("SELECT * FROM app_articles WHERE art_sta_id = :art_sta_id $and ORDER BY art_date DESC");

$statement_article->setFetchMode(PDO::FETCH_ASSOC); 

$statement_article->execute(array(':art_sta_id' => "1"));

Here's what I tried but it fails if there is no cat_id in the url

PHP

$and = '';
if (isset($_GET['cat_id'])) {
    $and = "AND art_cat_id = :cat_id";
}


$statement_article = $db->prepare("SELECT * FROM app_articles WHERE art_sta_id = :art_sta_id $and ORDER BY art_date DESC");

$statement_article->setFetchMode(PDO::FETCH_ASSOC); 

$statement_article->execute(array(':art_sta_id' => "1",':cat_id' => $_GET['cat_id']));

Upvotes: 0

Views: 106

Answers (1)

Paul Roub
Paul Roub

Reputation: 36438

Adjust the parameter array when there's a cat_id:

$and = '';
$params = array(':art_sta_id' => "1");

if (isset($_GET['cat_id'])) 
{
  $and = "AND art_cat_id = :cat_id"
  $params[':cat_id'] = $_GET['cat_id'];
}   

$statement_article = $db->prepare("SELECT * FROM app_articles WHERE art_sta_id = :art_sta_id $and ORDER BY art_date DESC");

$statement_article->setFetchMode(PDO::FETCH_ASSOC); 

$statement_article->execute($params);

Upvotes: 2

Related Questions