PHP: Error when using IN statement in query with SQL injection prevention

Question

I am new to PHP and hope someone can help me with this.

I currently have the following PHP which is part of an Ajax call in jQuery. When entering IDs manually instead of ? (e.g. 1,2,3,4,5) then this works as intended but when I use the query as below it only returns one item as shown below so I believe the combination of IN(?) and my attempt to prevent SQL injection don't work here.

Can someone tell me what I am doing wrong here ?
Also, this creates a multi-dimensional array and I was wondering if this could be simplified since I only need the ID (tID) and value (content) for each item.

My PHP:

$content = implode(",", $_POST["content"]);  // an array containing IDs retrieved from Ajax
$languageFrm = $_POST["languageFrm"];

$stmt = $conn->prepare("SELECT tID, " . $languageFrm . " FROM TranslationsMain WHERE tID IN(?) ORDER BY tID");
$stmt->bind_param("s", $content);
$stmt->execute();
$result = $stmt->get_result();
while($arrTranslations = $result->fetch_assoc()){
    $translations[] = array("tID" => $arrTranslations["tID"], "content" => $arrTranslations[$languageFrm]);
}
var_dump($translations);

Current result in Ajax:

array(1) {
  [0]=>
  array(2) {
    ["tID"]=>
    int(1)
    ["content"]=>
    string(6) "Value1"
  }
}

Update:
My issue is that even though the posted links and current answer seem to refer to the proper solutions I am not able to get the rest of the PHP code working since whenever I use one of the suggested solutions I get the error "Call to a member function fetch_assoc() on a non-object...".

Many thanks for any help with this,
Mike

Answer 1

You're binding a string to a single parameter in your SQL code. Meaning your SQL translates to SELECT something FROM table WHERE attribute IN ("1,2,3,4,5") and that's clearly not what you want.

What you're trying to do is bind multiple parameters to your SQL dynamically. This can be achieved by dynamically setting the number of parameters in the prepared statement as demonstrated in the PHP manual under Example #5 of PDOStatement::execute(). I've added this as a general example in the manual since it's a pretty common use case.

---

Since you're using MySQLi and not PDO here I'll provide the MySQLi equivelant example...

$params = $_POST["content"];

$place_holders = implode(',', array_fill(0, count($params), '?'));


$stmt = $conn->prepare("SELECT tID, " . /* no way on earth will I ever do
                                           this $languageFrm */ null .
                       " FROM TranslationsMain WHERE tID IN($place_holders) ORDER BY tID");
// substitute PDO::execute() for call_user_func_array to bind_params because MySQLi sucks
call_user_func_array([$stmt, 'bind_param'], $params);
$stmt->execute();
$result = $stmt->get_result();
while($arrTranslations = $result->fetch_assoc()){
    $translations[] = array(
                            "tID" => $arrTranslations["tID"],
                            "content" => $arrTranslations[$languageFrm],
                           );
}
var_dump($translations);

---

You are still vulnerable to SQL Injection

Please also consider that your prepared statement is vulnerable to SQL injection since you are unsafely concatenating user-supplied input directly in to your SQL statement from $_POST["languageFrm"] in the line

$stmt = $conn->prepare("SELECT tID, " . $languageFrm .
        " FROM TranslationsMain WHERE tID IN(?) ORDER BY tID");

That big red glaring $languageFRM in your prepared statement is from user input and is a part of your SQL code, i.e. rendering all your efforts to use parameters in your query quite useless here.