0

I have multiple conditions in WHERE clause which are inputted by user (call them filters). Currently I am processing them this way (don't worry it isn't deployed):

//$by_nickname etc. are filters from $_GET

$conditions = array();

if($by_nickname !="")
{
    $conditions[] = " players.lastName LIKE ('%" . $by_nickname . "%')";
}

if($by_steamid !="")
{
    $conditions[] = " ids.uniqueId = '$by_steamid'";
}

if($by_ip !="")
{
    $conditions[] = " players.lastAddress = '$by_ip'";
}

if($by_msg !="")
{
    $conditions[] = " chat.message LIKE ('%" . $by_msg . "%')";
}

if (count($conditions) > 0)
{
    $where = implode(' AND ', $conditions);
    $query = "SELECT ... WHERE " . $where;
}
else
{
    $query = "SELECT ... ";
}

Instead of this I would use

$conditions[] = " ids.uniqueId = ?";

and so on. Now I would also obtain $where, but with ? instead of filter values.

Query should be now prepared

$stmt = $mysqli->prepare("SELECT ... WHERE $where");

and parametrized something like this

$stmt->bind_param('ss', $by_nickname, $by_steamid);

But how do I parametrize query if some filters could be empty? Simply, I don't know the bind_param() method arguments in advance.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
jirinovo
  • 2,105
  • 3
  • 26
  • 37
  • @JayBlanchard Well I don't think so. – jirinovo Sep 30 '15 at 16:50
  • You cannot parameterize table or column names @jirinovo You will have to rethink your logic to remove that possibility and allow you to parameterize your filter values. – Jay Blanchard Sep 30 '15 at 16:51
  • 1
    @JayBlanchard I don't understand. I don't want to parametrize table or column names -- I want to protect query from SQL injection in `WHERE` clausule conditions. – jirinovo Sep 30 '15 at 16:53
  • Paramterized queries are *what you use* to protect against SQL injection. – Jay Blanchard Sep 30 '15 at 17:01
  • This would help if you get the idea http://stackoverflow.com/questions/17870999/bind-multiple-parameters-into-mysqli-query/17874410#17874410 – Your Common Sense Sep 30 '15 at 17:40
  • @YourCommonSense Thanks, but I have looked at PDO and it seems to be very easy to solve my problem with it, because unlike mysqli it has named parameters support so parameters can be dynamic. More info at http://stackoverflow.com/a/1457157/1928742 or http://is.php.net/manual/en/pdo.prepare.php – jirinovo Sep 30 '15 at 18:15

1 Answers1

1

I have solved my problem using PDO which has named parameters. So here is my solution, hope it helps somebody.

$by_nickname = $_GET['nickname'];
$by_steamid = $_GET['steamid'];
// integer
$by_serverid = $_GET['serverid'];

$pdo = new PDO("mysql:host=host;port=port;dbname=db;charset=utf8", "user", "password");

$conditions = array();
$parameters = array();
$where = "";

if($by_nickname !="")
{
    $conditions[] = " players.nickname LIKE :nickname";
    $parameters[":nickname"] = "%$by_nickname%";
}

if($by_steamid !="")
{
    $conditions[] = " ids.steamid = :steamid";
    $parameters[":steamid"] = $by_steamid;
}

if($by_serverid !="")
{
    $conditions[] = " servers.serverid = :serverid";
    // Beware of correct parameter type!
    $parameters[":serverid"] = intval($by_serverid);
}

if (count($conditions) > 0)
{
    $where = implode(' AND ', $conditions);
}

// check if $where is empty string or not
$query = "SELECT ... " . ($where != "" ? " WHERE $where" : "");

try
{
    if (empty($parameters))
    {
        $result = $pdo->query($query);
    }
    else
    {
        $statement = $pdo->prepare($query);
        $statement->execute($parameters);
        if (!$statement) throw new Exception("Query execution error.");
        $result = $statement->fetchAll();
    }
}
catch(Exception $ex)
{
    echo $ex->getMessage();
}

foreach($result as $row)
{
  // for example
  echo row["<Column Name>"];
}
jirinovo
  • 2,105
  • 3
  • 26
  • 37