phpmyadmin Question posted in
The official documentation can be found here.

mysqli_stmt_bind_param() data not supplied when a question mark is a part of the query – Phpmyadmin

Similar questions have been asked many times, but after reading almost every of these for over 5 hours, i have not found a suitable reply for my problem.

Im not an experience php / mysql developer, but i ve managed similar situations with the use of mysqli_stmt_bind_param() func.

Here is the query:

$query = 'SELECT Recipes.* , Categories.* FROM `Recipes` JOIN `Categories` ON JSON_EXTRACT(Recipes.category, '$.category') = 'Category ?' WHERE Categories.category = ?';

I use this php code:

if ($stmt = mysqli_prepare($dbManager->getDBInstance(), $query)){

        mysqli_stmt_bind_param($stmt,"ii", $id, $id);

    }

Because i have a model on the client side like:

{
    "category" : "...",
    "recipes" : [{...},{...}]
}

The error is: Fatal error: Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement

I have already made similar queries with many more parameters, without any error: however, this is the first time i use the JSON_EXTRACT func from mysql.

I believe the error is caused by the $. which is not escaped correctly. The parameters to be replaced reference to the same variable, $id, which is an integer, and gets used for string interpolation in the first case (‘Category 1’) and as number after the WHERE clause.

Consider that, by not using mysqli_stmt_bind_param, the same query on phpmyadmin returns what i want, but that would open my code to mysql injections, which i want to avoid.

Also, notice that if i pass just one parameter to the function, the script gets executed (with wrong results), like if the query gets truncated at some point… i properly escaped every single quote, and even tried with double quotes, but the error is always the same.
Any hint on how to prevent the injection and achieve the result would be highly appreciated, because i really can’t figure it out by myself.
Thank you

Tags:

2

Answers


  1. 0

    You have two parameters in the call to mysqli_stmt_bind_param(), but there’s only one placeholder in $query. The first ? is inside quotes, so it’s treated literally, not as a placeholder.

    You can use CONCAT() to concatenate a string literal with a placeholder, so change it to:

    $query = '
    SELECT Recipes.* , Categories.* 
    FROM `Recipes` 
    JOIN `Categories` ON JSON_EXTRACT(Recipes.category, '$.category') = CONCAT('Category ', ?) 
    WHERE Categories.category = ?';
    Login or Signup to reply.
  2. 0

    A placeholder can represent a complete data literal only. To put it simple – anything you would write in quotes (or a number). So it shouldn’t be 'Category ?' but just ? where Category could be concatenated in PHP.

    $query = 'SELECT * FROM `Recipes` JOIN `Categories` ON 
          JSON_EXTRACT(Recipes.category, '$.category') = ?
          WHERE Categories.category = ?';
$stmt = mysqli_prepare($dbManager->getDBInstance(), $query);
$category = "Category $id";
mysqli_stmt_bind_param($stmt,"si", $category, $id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search