Ndtyjky

Lets say that I want to write a procedure allowing me to call certain function on certain column, for example:

call foo('min','age') -> SELECT min(age) FROM table;

I want my procedure to be safe from sql injection, therefore, I'm willing to use prepared statements and parametrize the input

SET @var = "SELECT ?(?) FROM table;"

PREPARE x FROM @var;

EXECUTE x USING a, b;

Where a and b are input parameters, function and column, respectively.

However, it doesnt seem to be possible - InnoDB keeps throwing an error whenever I want to execute this statement.

Is it possible to solve this this way, or I need to resort to whitelisting?

EDIT:
Full code:

create procedure test(in func varchar(20), in col varchar(20))

  begin

    set @f = func;

    set @c = col;

    set @sql = "select ?(?) from table;";

    prepare x from @sql;

    execute x using @f, @c;

  end;

calling:

call test('min','age');

Full error:

[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(?) from table' at line 1

You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.

You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.

Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.

Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.

delimiter $$

create procedure test(in func varchar(20), in col varchar(20))

  begin



    set @c = col;



    -- use concat function to generate the query string using func parameter

    set @sql = concat('select ', func, '(?) from table');



    -- prepare the statement

    prepare stmt from @sql;



    -- execute

    execute x using @c;



    -- don't forget to deallocate the prepared statement

    deallocate prepare stmt;

  end$$

delimiter ;

The only way you can parameterize SQL keywords in a keyword is to use a dynamic query. The caveat is that dynamic queries tend to be slower than static queries, primarily because they can't easily be cached.

With the warning out of the way, something like this should work:

SET @query = "SELECT ?('?') FROM table";  

EXECUTE stmt USING 'count', 'id';