PHP prepared statements -- CSVs

[arenaninja]

I'm trying to figure out the right way to bind this statement. I have a SQL query that takes as input a comma-separated-value (CSV) generated by the server according to user input. I'm paranoid about security so I use prepared statements on everything. The query is more or less:

$sql=SELECT * FROM someTable WHERE tablePK IN(3,6,7)

However, assuming this can be tampered with I want to do:

$sql=SELECT * FROM someTable WHERE tablePK IN(:csv)
$stmt=$this->db->prepare($sql)
$stmt->bindParam(":csv",$some_var,PDO::PARAM_????)

As you can see I have no idea what PARAM I'm supposed to select from the PDO constants -- PARAM_INT is for the SQL Integer type, and PARAM_STRING will return the wrong results [because the SQL string is changed to IN('3,6,7') which seems to always match only the first parameter, in this case 3].

 

Any help is appreciated. (MySQL 5.5, PHP 5.3)

[xyph]

Well, in this case they're INTs right?

 

Or are you looking for a catch-all?

[scootstah]

You'll have to do that manually. You can do something like:

$csv = array(3,6,7);
$csv = implode(',', $csv);

$sql="SELECT * FROM someTable WHERE tablePK IN($csv)";

[arenaninja]

I should apologize to you both, I realize that there's something I didn't include.

 

Typically I have this all declared inside a function with the following parameters:

public function _loadTableData($id,$isCsv=false)
{
  $sql = "SELECT * FROM someTable WHERE ";
  if($isCsv==true)
    $sql.="tablePK IN($id)";
  else
    $sql.="tablePK=:id";
  $stmt = $this->db->prepare($sql);
  if($isCsv==false)
    $stmt->bindParam(":id",$id,PDO::PARAM_INT);
}

As you can see, my current solution for CSVs is pretty much equivalent to scootstah's, whereas for non-CSVs I use a prepared statement. But I know that the security from prepared statements comes from the bindParam method, which is skipped entirely when I use CSVs. I'm wondering if this opens my application to vulnerabilities (I'm assuming yes), how badly, and what I can do about it.

[kicken]

Assuming they are integers, there is nothing wrong with just embedding them in the query.  There is no way to bind a "list" value so to speak. What I typically do is just run them through intval and then implode them to a string and call it a day, eg:

if (is_array($id)){
   $id=array_map('intval', $id);
   $sql .= 'tablePK IN ('.implode(',', $id).')';
}

 

The only way to do it with actual parameter binding would be to add a separate bind for each element, as in tablePK IN (:id1, :id2, :id3) then bind each param separately.  You could do it with a couple foreach loops but it's not necessary for INT values.  I will do that if the value is a string, but that is fairly rare.

 

[scootstah]

I'm wondering if this opens my application to vulnerabilities (I'm assuming yes)

 

Nope, not if the data is integers (which it appears to be). You can typecast the data to integers with intval or (int), which will eliminate all non-integer characters. The query will be totally safe this way.

 

You can use array_map like kicken said, but you have to make sure it is a one-dimensional array or you won't get the expected result.

[arenaninja]

I'm wondering if this opens my application to vulnerabilities (I'm assuming yes)

 

Nope, not if the data is integers (which it appears to be). You can typecast the data to integers with intval or (int), which will eliminate all non-integer characters. The query will be totally safe this way.

 

You can use array_map like kicken said, but you have to make sure it is a one-dimensional array or you won't get the expected result.

Ahhh excellent then. I'll just make sure to typecast (int) when I add them to the CSV list. Many thanks!