PDO preparing with subqueries

Question

I am quite new to PDO, and am trying to change my MySQLi procedurally structured php code to an Object Oriented PDO structure. I am just learning about preparing, executing, bindParam/bindValue and the like, to a degree of success.

My question is how do I prepare a query when the user submitted value is in a subquery of that query?

I have a variable used as a subquery in php (where $playerOne, $playerTwo are user submitted values).

$sqlPlayerOne = "(SELECT * FROM players WHERE Player_name = $playerOne)";
$sqlPlayerTwo = "(SELECT * FROM players WHERE Player_name = $playerTwo)";

This it to get all records for these players. I can then, as an example, compare what games they played against each other e.g.

$sqlWith = "SELECT * FROM $sqlPlayerOne s1  
WHERE EXISTS (SELECT * FROM $sqlPlayerTwo s2 WHERE s1.Team_name = s2.Opposing_team)

Note: SELECT * is just used to make it more readable here.

Is it enough to do $pdoWith = $db->prepare($sqlWith) or should I be preparing the $sqlPlayerOne first, as this has the user submitted value?

I realise I could just copy/paste the subquery inside every single main query that needed it, but if I don't have to I'd rather not.

EDIT: Sorry for the lack of clarity. This was a section of my code before I changed it, as i wasn't sure how I would have to change it. It seems I will just have to do it similar to how @J-C FOREST pointed out:

 $dsn = "mysql:host=localhost;dbname=database";
 $username = "user";
 $password = "pass";
 $db = new PDO($dsn, $username, $password);

 $stmt = $db->prepare("SELECT * FROM (SELECT * FROM players WHERE Player_name = :playerone) 
 s1 WHERE EXISTS (SELECT * FROM (SELECT * FROM players WHERE Player_name = :playertwo) s2 
 WHERE s1.Team_name = s2.Opposing_team)");

 $stmt->bindValue(':playerone', $playerOne);
 $stmt->bindValue(':playertwo, $playerTwo);
 $stmt->execute();

Answer 1

The overall mechanism of prepared statements is the same in all database extensions that support it:

• Replace number or string literals (and I mean that, literals, not random pieces of code) inside SQL with place-holders, either position-based ? or named :username (don't mix, pick one)
• Prepare the query by calling the appropriate function that receives SQL as parameter
• Execute the prepared query by calling the appropriate function(s) that receive values as paremeter

So if you're doing it right in mysqli, a switch to PDO will not require a change in your logic. Your code samples, though, suggest you are not using prepared statements at all: no place-holders, no data in a separate channel... I can see variable interpolation in double quoted strings, but that's a PHP feature, not a SQL feature. As such, it's totally useless to separate code and data and prevent SQL injection.

I suspect the root misunderstanding is not being fully sure of how PHP and SQL interact. The answer is that they don't: they are entirely different computer languages and they are executed by entirely different programs. The only relationship is that you use the former to generate the latter. No matter what you do, in the end you'll just submit a string (i.e. plain text) to the database server. How you generate that text is irrelevant because strings have no memory.

Answer 2

You need to bind $playerOne, $playerTwo to your prepared statement as parameters. http://php.net/manual/en/mysqli.prepare.php

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* create a prepared statement */
$stmt = $mysqli->prepare("SELECT * FROM (SELECT * FROM players WHERE Player_name = ?) s1  
WHERE EXISTS (SELECT * FROM (SELECT * FROM players WHERE Player_name = ?) s2 WHERE s1.Team_name = s2.Opposing_team)")

    /* bind parameters for markers */
    $stmt->bind_param("ss", $playerOne, $playerTwo);

    /* execute query */
    $stmt->execute();