Benefits of using prepared statements in stored procedures?

Question

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)?

Does the engine have any way to keep the prepared statement in memory or is it a wash?

If the answer is depending upon environment, I interested in mysql with innodb.

Answer 1

So yes as you have mentioned about SQL injection the other advantage is what you guessed.

Quoting from documentation:

Using prepared statements with placeholders for parameter values has the following benefits:

• Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

• Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

So yes it does cache the prepared statements and routines.

Answer 2

Does the engine have any way to keep the prepared statement in memory or is it a wash?

Prepared statement is stored in memory till explicit DEALLOCATE statement or connection termination and can be reused in the same connection if it was not destroyed - including from within another simple or compound statement.

Query result obtained by SELECT type prepared statement is stored in query cache on a universal basis independent of the source type of query and can be reused both by another execution of this or another prepared statement and by direct query when its text is identical to the SQL text of the first prepared statement.

Answer 3

The benefit I found is that you can use a variable field name to search from, very handy to write a search routine that can search any field without having to write a LIKE statement for every field that you would want to search. Here is an example below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ADMIN_SEARCH_PLEDGORS`(
    IN P_email VARCHAR(60),
    IN P_password_hash VARCHAR(255),
    IN P_filter_field VARCHAR(80),
    IN P_filter_value VARCHAR(255)
)
BEGIN
    #Takes admin credentials (first tow paramaters and searches the pledgors_table where field name (P_filter_field) is LIKE value (%P_filter_value%))
    DECLARE V_admin_id INT(11);
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;
        SELECT 'ERROR' AS STATUS, CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT) AS MESSAGE;
    END;
    SELECT admin_id INTO V_admin_id FROM admin_table WHERE password_hash = P_password_hash AND email = P_email;
    IF ISNULL(V_admin_id) = 0 THEN    
        SET @statement = CONCAT('SELECT pledgor_id, email, address, post_code, phone, alt_phone, contact_name
        FROM pledgors_table
        WHERE ',P_filter_field, ' LIKE \'%', P_filter_value, '%\';');
        PREPARE stmnt FROM @statement;
        EXECUTE stmnt;
    ELSE
        SELECT 'ERROR' AS STATUS, 'Bad admin credentials' AS MESSAGE;
    END IF;
END

On an unrelated note: Gettig data from the above stored procedure from PHP was very tricky this little snippet below worked with the above procedure:

$query = 'CALL SP_ADMIN_SEARCH_PLEDGORS(\''.
strtolower($email).'\', \''.
$password_hash.'\', \''.
$filter_field.'\', \''.
$filter_value.'\');';

$errNo = 0;
//$myLink is a mysqli connection
if(mysqli_query($myLink, $query)) {
    do {
        if($result = mysqli_store_result($myLink)) {
            while($row = mysqli_fetch_assoc($result)) {
                $data[] = $row;
            }
            mysqli_free_result($result);
        }
    } while(mysqli_next_result($myLink));
}
else {
    $errNo = mysqli_errno($myLink);
}
mysqli_close($myLink);