1

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

Improve this question
5

1 Answer 1

Reset to default
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.

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

5 Comments

Thank you very much - this helps a lot !
Update: I had a look at your example and updated my code accordingly but it looks like then I can't use the rest of my code (i.e. everythng after $stmt->execute();) since this is throwing the error "Call to a member function fetch_assoc() on a non-object...". Can you let me know how I would have to adjust this ? Also, I am not sure but it looks to me like you might have to replace ($place_holders) in your example with (" . $place_holders . ") ?
That would indicate that $result is not an object, further inferring that your call to $stmt->get_result() failed. See what MySQLiStatement::getResult returns on failure. Notice the example I'm pointing out is for PDO, whereas you're using MySQLi here. To adapt this code to MySLQi you'll need to call MySQLiStatement::bindParam() with call_user_func_array . And no, the example is correct, as the SQL string contains the braces in the call to prepare.
Thanks a lot for the updates and your explanations. I applied this as suggested but it doesn't work. If I use it as above then Dreamweaver marks the call_user_func_array line as invalid (perhaps due to the square brackets) and if I use array($stmt, "bind_param") instead then Dreamweaver accepts it but I get again the following error: "Call to a member function fetch_assoc() on a non-object...".
Same logic applies. That still means that $result is not an object. And if $result is not an object that tells you that $stmt->get_result() failed. And if $stmt->get_result() failed that tells you that it returned false. Consequently, if var_dump($result) is false that supports that $stmt->get_result() failed. Now, I'll leave up to the reader as an exercise to dig in the manual and find out how to get useful error information from mysql on why the statement failed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.