MySQLi optional WHERE clause depending on if condition

Question

While trying to optimize some code I came across another question.. Using usergroups and permissions, I sometimes need to add a WHERE clause when a user has limited permissions.. I could put the whole query inside the if {...}, but that would mean to repeat the same code except one line.. So I changed the code that only the statement is built depending on the if condition, instead of repeating the whole part (statement, prepare, execute, fetch..):

if (in_array('Edit own', $_SESSION['user_r']))
{
    $sql = 'SELECT
                `news_id`
            FROM
                `news`
            WHERE
                `news_user_id` = "' .$_SESSION['user_id']. '"';
}
else if (in_array('Edit all', $_SESSION['user_r']))
{
    $sql = 'SELECT
                `news_id`
            FROM
                `news`';
}

I know it's also possible to "split" the statement, using the if condition within the statement like this :

$sql = 'SELECT
            `news_id`
        FROM
            `news`';

if (in_array('Edit own', $_SESSION['user_r']))
{
     $sql .= ' WHERE
                 `news_user_id` = "' .$_SESSION['user_id']. '"';
}

At first glance the 2. example could be "better" since there is no repeat and the code is short, but when it comes to prepared statements and bind_param(), the first example might be better, because I can add

$stmt = $db->prepare($sql);
$stmt->bind_param('ss', $what, $ever);

directly after the statement inside the if {...} ... and somehow it seems more secure to me...

So which solution should I prefer?

FWIW, PDO lets you do `$stmt->execute($params_array)`: http://php.net/manual/en/pdostatement.execute.php ... but it's probably not worth switching over just for that. — EthanB, Aug 30 '12 at 00:06
@EthanB I agree, switching to PDO just for that could be too much, but maybe I'll consider learning PDO some day.. but I'd still have to use `if {...}` to check whether I need the WHERE clause or not, right? — qlt, Aug 30 '12 at 00:20

score -1 · Accepted Answer · answered Aug 31 '12 at 01:23

Since there is no other answer, I want to share my "solution":

if (in_array('Edit own', $_SESSION['user_r']))
{
    $sql = 'SELECT
                `news_id`
            FROM
                `news`
            WHERE
                `news_user_id` = ?';

    $stmt = $db->prepare($sql);
    $stmt->bind_param('i', $_SESSION['user_id']);

}
else if (in_array('Edit all', $_SESSION['user_r']))
{
    $sql = 'SELECT
                `news_id`
            FROM
                `news`';

    $stmt = $db->prepare($sql);
}

$stmt->execute();
$stmt->bind_result($news_id);
...

As already mentioned in my question, I'm using the if statement to build the whole statement now. That way, I can use bind_param inside the if statement (if needed), but don't need to repeat the execute/fetch part.

MySQLi optional WHERE clause depending on if condition

1 Answers1