using STORED PROCEDURES: different results in mysq

Question

I am migrating all my mysqli queries to STORED PROCEDURE. It should be as easy as changing one line in the mysqli call, howver, the two following codes give different results:

Regular query, which works correctly:

$query = $this->mysqli->query("SELECT DISTINCT ID FROM user
                                   WHERE 
                                   MATCH (name) AGAINST ('* *$sanitized* *') ");

if ($query) {   
  $nrows = $query -> num_rows;
  if ($nrows > 0) {
      $searchResult = 'We found '. $nrows .' results';
  }
}

CALL to PROCEDURE, which returns a "fetch_array() on boolean" error:

$query = $this->mysqli->query("CALL myfunction('.$sanitized.')");

where the procedures is defined as:

DELIMITER $$
CREATE PROCEDURE myfunction (sanitized VARCHAR(124))
BEGIN
    SELECT DISTINCT ID FROM user
                                   WHERE 
                                   MATCH (name) AGAINST ('* *sanitized* *');
END 
$$
DELIMITER ;

I can't find a solution and it seems that no one has a similar issue in this forum.

Answer 1

consider Prepared Statements used with concat() as they often are.

DROP PROCEDURE if exists myStoredProc101;
DELIMITER $$
CREATE PROCEDURE myStoredProc101
(   pSanitized VARCHAR(124)
)
BEGIN
    set @mySql:=concat("SELECT DISTINCT ID FROM user where match(name) against ('* *",pSanitized,"* *')");
    PREPARE stmt1 FROM @mySql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END 
$$
DELIMITER ;

Your stored proc had no chance of working as it wasn't even using your parameter. What you did was bury something inside of a string literal. Also, varchar(124) is a bit odd :p

About the only success people have with prepared statements is with using a User Variable (with an @) versus failed attempts of using Local Variables (from DECLARE). So, that may save you a few hours of head banging in the future.

From the PHP Manual Page Stored Procedures:

Handling result sets

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query. The mysqli_query function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli_real_query or mysqli_multi_query. Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error.

As for calling the stored proc from mysqli, please take a look at the Answer from Pablo Tobar. It does not look especially pleasant with many variables, but that seems to be where it is at. Spoiler Alert: use mysql variables, not PHP variables.

Granted, Pablo was not returning a resultset, but rather writing to an OUT var in the stored proc. Perhaps you need to do what he did for the IN parameters, and call multi_query(), then a store_result(), then a fetch_all() (in short, the PHP reference a page up).

Alternatively, a call would be made as done by Palladium here.

In either case, case must be taken to avoid the known vulnerability of passing SQL Injection over to stored procedure routines.