PDO Array to String Conversion - Parameter

Question

Pretty sure there's an easy fix for this, but I'm inexperienced in array handling when it comes to PDO statements

Purpose: To retrieve a set of id's from the database, then use those ids to compare and retrieve

$SAList = getSecurityActionsForQuestion("$QID");
$con = database::getInstance()->getConnection();
$ParentList = $con->prepare('SELECT SQ.SurveyQuestionID, SQ.SurveyQuestionDesc 
FROM SurveyQuestion SQ
JOIN  SurveyQuestionLookup SQLP ON SQLP.SurveyQuestionID = SQ.SurveyQuestionID
WHERE SQLP.SecurityActionID IN (:SAList)
   AND SQLP.SurveyQuestionID != :QID');
$ParentList->bindValue(':QID',$QID, PDO::PARAM_STR);
$ParentList->bindValue(':SAList',$SAList, PDO::PARAM_STR);
$ParentList->execute();
$Results = $ParentList->fetchAll();

Now, individually, both the 'getSecurityActionForQuestion' function and the inserted select statement work and retrieve what I want. The issue is that I cannot send an array into the PDO::PARAM_STR and I have not found a fix for this.

I have seen some options to implode and prepare the string, but I'm not entirely sure how to incorporate this into my current design.

There is a possibility of using a foreach loop that creates a new array, holds a count variable and then within the SQL statement, I could insert a bunch of named parameters, then use another for loop to bind each parameter to their respective places, but I'm pretty sure someone out there has a far more elegant process than that hackjob.

EDIT:

Addition of the getSecurityActionsForQuestion(ID) function for clarity

function getSecurityActionsForQuestion($QID)
{
    $con = database::getInstance()->getConnection();
    $SAList = $con->prepare("SELECT DISTINCT SecurityActionID FROM SurveyQuestionLookup
    WHERE SurveyQuestionID = :QID");
    $SAList->bindValue(':QID',$QID,PDO::PARAM_INT);
    $SAList->execute();
    $Results = $SAList->fetchAll();
    $SAList=null;
    $con=null;  
    return $Results;
}

Don't Panic · Accepted Answer · 2017-01-12 17:19:00Z

1

You can use ? placeholders instead of named placeholders, and execute the query using an array of values rather than binding each value individually. (See example #5 in the PDOStatement::execute manual.)

$SAList = getSecurityActionsForQuestion("$QID");
$con = database::getInstance()->getConnection();

// create placeholders for each item in your array
$placeholders = rtrim(str_repeat('?,', count($SAList)),',');

$ParentList = $con->prepare("SELECT SQ.SurveyQuestionID, SQ.SurveyQuestionDesc 
FROM SurveyQuestion SQ
JOIN  SurveyQuestionLookup SQLP ON SQLP.SurveyQuestionID = SQ.SurveyQuestionID
WHERE SQLP.SecurityActionID IN ($placeholders)
   AND SQLP.SurveyQuestionID != ?");

// add the $QID into the array
$SAList[] = $QID;

// execute the query with the array of values
$ParentList->execute($SAList);

$Results = $ParentList->fetchAll();

PDO::PARAM_STR is the default binding type, so it will be used for this without specifying it.

edited Jan 12, 2017 at 17:19

answered Jan 12, 2017 at 17:09

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Jim Evil Chippy Perry Over a year ago

I like this solution, but is there anyway to sanitize the information to try and prevent SQL injection? I know that it's an internal function, so it shouldn't be readily interceptable, but it would be still good to know

Don't Panic Over a year ago

This has the same effect as binding the values individually, and using this method shouldn't negate any positive effects of using prepared statements. Because the only thing you're directly inserting into your SQL is the placeholders, and the parameters are still passed and bound separately, this way shouldn't increase your SQL injection vulnerability. It's just a shorter way to do the same thing.

Jim Evil Chippy Perry Over a year ago

That's odd. I am still receiving the array to string conversion error with your code. EDIT: Also, can $placeholders be accessed while within the "" tags, or should it the "" be replaced with '' and have the concatenation of the $placeholders variable? I've never not separated variables from strings, so I'm not sure if that's something that can be done, stupid me, I know.

Don't Panic Over a year ago

on which statement?

Jim Evil Chippy Perry Over a year ago

$ParentList->execute($SAList);

|

Collectives™ on Stack Overflow

PDO Array to String Conversion - Parameter

1 Answer 1

12 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Related