MySql 准备语句 - 是否可以参数化列名或函数名? [英] MySql prepare statement - is it possible to parametrize column name or function name?

问题描述

假设我想编写一个程序，允许我在某些列上调用某些函数，例如:

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;

我希望我的程序免受 sql 注入的影响，因此，我愿意使用准备好的语句并对输入进行参数化

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;

其中 a 和 b 分别是输入参数、函数和列.

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

然而，这似乎是不可能的 - 每当我想执行此语句时，InnoDB 都会不断抛出错误.

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?

完整代码:

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;

调用:

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

完全错误:

[42000][1064] 你的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本的正确语法在第 1 行的(?) from table"附近使用

[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

推荐答案

您不能参数化列/表/函数名称/别名.因为，PREPARE 语句只允许将 SQL 查询的值"部分用作参数.函数/表/列名/别名用于判断SQL语句的有效性；因此不能在运行时执行期间更改.在执行时更改它可能会改变 SQL 语句是否有效.

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.

此外，MySQL 服务器会将参数视为文字，并在它们周围加上引号，然后在查询执行中使用它们.

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 ;

这篇关于MySql 准备语句 - 是否可以参数化列名或函数名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！