MySQL (Stored) Procedure - parameters and query

Question

I am trying to create a simple procedure with parameters.

CALL new_procedure('mode', 'ASC');

The first input is the column the second is the sort direction

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN

    DECLARE order_by varchar(30);
    SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
/*
    SELECT * FROM `common_tags` ORDER BY @order_by  LIMIT 5;
*/
    SELECT @order_by as 'c';

END

In the above example I have it only outputting the 2 parameters so I can see what's happening.

Result:

"c"
`mode` ASC

.

When I run the procedure with it's intended code, below.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN

    DECLARE order_by varchar(30);
    SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
    SELECT * FROM `common_tags` ORDER BY @order_by  LIMIT 5;

END

Results

tags_id     data                mode        parent_id       position
1           Wood                2           13              6
2           Trippy              0           0               0
4           Artists             1           0               1
6           "Newest Additions"  1           0               11
12          "Natural Elements"  2           5               8

As you can see the results are not sorted by mode.

Any help is appreciated.

BlitZ · Accepted Answer · 2015-08-03 18:01:38Z

11

Unfortunately, you need to PREPARE entire query in this case:

DELIMITER $$

DROP PROCEDURE IF EXISTS `new_procedure`$$

CREATE PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
    SET @buffer = CONCAT_WS('',
        'SELECT * FROM `common_tags` ORDER BY `', in_order_by_column, '` ', in_order_by_direction, ' LIMIT 5'
    );

    PREPARE stmt FROM @buffer;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

NOTE: Described approach should be used very careful, because it is vulnurable to SQL Injection attacks, if used incorrectly.

edited Aug 3, 2015 at 18:01

answered Oct 3, 2013 at 18:01

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

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

5 Comments

Leukipp Over a year ago

You might want to quote the input values using quote(), see dev.mysql.com/doc/refman/8.0/en/…

BlitZ Over a year ago

@Leukipp OP is trying to costruct query with varying column and direction. Function you're suggesting is designed to escape values, not for column, direction or any query description parts.

Leukipp Over a year ago

My comment referred to your note on the constructed query being vulnerable to SQL injection.

ToolmakerSteve Over a year ago

To clarify @Leukipp's suggestion: This specific constructed query only uses parameters as a column name and as an SQL keyword - using quote() would cause an error. The column name is protected from injection by surrounding the column names with back-ticks, as BlitZ does. Leukipp's comment is indeed useful to anyone who uses this technique for parameters that are used in query as values (so was useful to me in a different concat'd query, to avoid SQL injection).

ToolmakerSteve Over a year ago

More about SQL injection: in_order_by_direction isn't protected; better would be to pass in a bool that is tested, replaced by ASC or DESC when CONCAT the query. To be safe, every part of the query must be protected by some means.

Collectives™ on Stack Overflow

MySQL (Stored) Procedure - parameters and query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related