2

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.

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.

3
  • Well, first one should note that the caching appears to be per-session but moreover, it says that for stored procedures "the server converts and caches the entire program body" which leaves open the question as to whether or not prepared statements therein are cached separately (on top of already being part of the cached body) and, more to the point of the question, if it therefore does any good to use PREPARE inside the procedure.
    – chell
    Aug 11 '15 at 12:10
  • 3
    IIRC you can only prepare a statement stored in a variable, so it CANNOT be "compiled" with the procedure at all. So it might have a performance benefit only in case the procedure executes the same statement in a loop. It might be cached as other prepares statements per session, but it is not probable you would use the same statement in a procedure and out of it multiple times in the same session.
    – jkavalik
    Aug 11 '15 at 12:18
  • 1
    @jkavalik You should post an answer
    – chell
    Aug 11 '15 at 21:48
0

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.

0

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);
1
  • You could have just said "it is useful for dynamic SQL".
    – mustaccio
    Sep 16 '20 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.