secure pdo mysql statement with more than 1 IN function

Question

Hello my fellow programmers, I am currently trying to create a social networking website and im a little stuck at the part where you select the content for the user to view and scroll.

Lets say they have friends and followers and i want to select content from the database from their friends and their followers IN A SECURE WAY. My current assumption is that i might use code like this.

        $select = "SELECT * FROM tableName WHERE FollowedPersonsID IN (1,2) OR FriendsID IN (9,8)";

    $arrayForSecurity = array( array(1,2), array(9,8) );
              try
                      {
                          // These statements run the query against your database table.
                        $result = $pdo->query($select);
                        $statement = $pdo->prepare("SELECT * FROM tableName WHERE FollowedPersonsID IN (?) OR FriendsID IN (?)");
                        $statement->execute($arrayForSecurity);
                        $content = $statement->fetchAll(PDO::FETCH_ASSOC);

                      }
                      catch(PDOException $e)
                      {
                          // Note: On a production website, you should not output $ex->getMessage().
                          // It may provide an attacker with helpful information about your code.
                          die("Failed to run query: " . $e->getMessage() . "<br><br>" . "$select");
                      }

foreach ($content as $key => $value) {
   HTMLContentFunction($value);
}

Here You can see that i have 2 IN() functions and both of them need to be php arrays because you guys will be able to imagine that the number of people people follow will vary with different people.

How can i create a secure my sql statement using 2 in functions?

More placeholders. One for each value. `(?)` or `(?,?)` and so on. — tadman, Feb 21 '19 at 20:07
@tadman Oh i see, Your suggesting i do a for each loop or something to print question marks "?" and then use statement execute for a merged version of the 2 arrays? Ill try that out. — Jevon, Feb 21 '19 at 20:39

score 1 · Answer 1 · answered Feb 21 '19 at 20:42

1

You need to generate a placeholder for each member of the array, and then also combine and flatten the arrays with all the parameters in the correct place. Eg:

// props to https://stackoverflow.com/a/1320156/1064767
function array_flatten(array $array) {
    $return = [];
    array_walk_recursive($array, function($a) use (&$return) { $return[] = $a; });
    return $return;
}

$arr1 = [1,2,3];
$arr2 = [4,5];

$ph1 = implode(',', array_fill(0, count($arr1), '?'));
$ph2 = implode(',', array_fill(0, count($arr2), '?'));

$query = "SELECT * FROM foo WHERE a = ? AND ( b IN ($ph1) OR c IN ($ph2) ) AND d = ?";

$params = array_flatten([0, $arr1, $arr2, 6]);

var_dump($query, $params);

Output:

string(74) "SELECT * FROM foo WHERE a = ? AND ( b IN (?,?,?) OR c IN (?,?) ) AND d = ?"
array(7) {
  [0]=>
  int(0)
  [1]=>
  int(1)
  [2]=>
  int(2)
  [3]=>
  int(3)
  [4]=>
  int(4)
  [5]=>
  int(5)
  [6]=>
  int(6)
}

and just as a general warning, be careful of letting your IN() clauses get too large [ballpark: 100 or more] as that can lead to performance issues. They're basically just a condensed syntax for a buttload of OR clauses.

answered Feb 21 '19 at 20:42

Sammitch

30,782
7
50
77

People could follow 1000 people or even more in a big social network. Is this the best way for selecting content based on who the person has followed or is friends with? – Jevon Feb 21 '19 at 20:55
1

@Jevon engineers, infrastructure, and boatloads of money. Unfortunately I don't have any articles handy, but they are usually fairly in-depth solutions. When you're first starting out it's not terribly productive to worry about things at this level, and I got a bit inside my own head when I made that recommendation. – Sammitch Feb 21 '19 at 23:25
What I _would_ recommend is designing your application with abstractions in mind so that if you later _do_ need to alter some underlying code and/or infrastructure for scaling purposes you only have to make code changes in a few well-defined places, rather than hunting through your entire codebase for raw queries. – Sammitch Feb 21 '19 at 23:25
At scale you'll be able to optimize this better, but the general principle is the same. Remember a lot of ORM wrappers will do this for you so you're not exposed to the ugly underpinnings. Computers are ridiculously fast these days and composing, transmitting, parsing, executing, and returning results for a query like this happens faster than you can type a single keystroke. – tadman Feb 22 '19 at 01:10

secure pdo mysql statement with more than 1 IN function

1 Answers1