SQL prepared statement to search songs by various text fields As we know, using PDO and prepared statements, binding a table or column using bindParam is not possible. So for this, I've figured out to use function parameters to do it by replacing the column names with the parameters, as shown in the example bellow.

function search($filter,$input,$type,$order,$sort,$limit){
    $st=$this-&gt;conn-&gt;prepare("SELECT Title,Type,Youtube,Score,Ratings,Singer,ID
    FROM song WHERE $filter LIKE CONCAT('%',?,'%')
    AND Type=? ORDER BY $order $sort LIMIT $limit");
    $st-&gt;bindparam(1,$input);
    $st-&gt;bindparam(2,$type);
    $st-&gt;execute();
    return $st-&gt;fetchall();
}


And I call this function here:

$result=$search-&gt;search($_POST['filter'],$_POST['input'],$_POST['type'],$_POST['order'],$_POST['sort'],$_POST['limit']);


Where the values come from:


$filter----select
$input----input
$type----radio
$order----select
$sort----radio
$limit----input


As you see the table I'm selecting from is fix, that's not optional, but I have the option to choose which column I want the result to be ordered by($order), which column I'd like to search($filter), I want it to be ascending or descending($sort), and how many I'd like to see($limit).

And this code works fine, but I'm not sure if it's a safe way to do this. Are there some kind of risks of this solution, or it's safe to leave it like this?
Are there any risks of using function parameters for column names in prepared statement?


Well, of course you shouldn't. "Function parameters" is not a protection measure in any sensible way. It's just a transport, to deliver some value into a function. As is.


  Are there some kind of risks of this solution,


ANY of them. All kinds of SQL injection are welcome in this code. 

Sorry for the harsh preface but I wanted to make it clear and make sure there are no such illusions left.

So you have to change the approach entirely. Luckily, it is not hard to implement.


For the field name and  order by, you will need a white list filtering.
For the limit, as it's not a column name, but a data literal, simply add it through a placeholder.


I've got an article that explains the white list approach in detail, Adding a field name in the ORDER BY clause based on the user's choice

Using a helper function from this article, 

function white_list(&amp;$value, $default, $allowed, $message) {
    if (empty($value)) {
        return $default;
    }
    $key = array_search($value, $allowed, true);
    if ($key === false) { 
        throw new InvalidArgumentException($message); 
    }
    return $value;
}


we can make your function 100% safe 

function search($filter,$input,$type,$order,$sort,$limit)
{
    $fields = ['name', 'text', 'whatever'];
    $filter = white_list($filter, $fields[0], $fields, "Incorrect filter name");
    $order  = white_list($order, $fields[0], $fields, "Incorrect order name");
    $sort   = white_list($sort, "ASC", ["ASC","DESC"], "Invalid ORDER BY direction");

    $sql = "SELECT Title,Type,Youtube,Score,Ratings,Singer,ID
    FROM song WHERE $filter LIKE CONCAT('%',?,'%')
    AND Type=? ORDER BY $order $sort LIMIT ?"
    $st = $this-&gt;conn-&gt;prepare($sql);
    $st-&gt;execute([$input, $type, $limit]);
    return $st-&gt;fetchall();
}


Just edit the list of fields allowed (or make two lists if they have to be different for the filter and order) and you're set.