如何使用动态SQL语句将值选择为变量以在Mariadb中进行分配？

发布于 2025-02-08 07:54:23 字数 637 浏览 2 评论 0原文

我正在尝试使用动态SQL语句检索值并将其分配给存储过程中的变量。

问题是，在执行动态语句时，它无法访问过程范围中声明的变量。

这是代码的片段（在一个过程中），以澄清：

DECLARE v_amount_of_samples_that_require_revision INTEGER;

SET query = CONCAT('SELECT count(sample_id) INTO v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        'WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ', p_form_id);                    

PREPARE stmt FROM query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我当然遇到的错误是：

sql错误[1327] [42000]：（conn：7575）undeclared变量：v_amount_of_samples_that_require_revision

是否有解决方法？

原文

I'm trying to use a dynamic SQL statement to retrieve a value and assign it to a variable, in a stored procedure.

The problem is, when executing the dynamic statement, it can't access the variable declared in the procedure scope.

Here is a fragment of the code (inside a procedure), for clarification:

DECLARE v_amount_of_samples_that_require_revision INTEGER;

SET query = CONCAT('SELECT count(sample_id) INTO v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        'WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ', p_form_id);                    

PREPARE stmt FROM query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error I get, of course, is:

SQL Error [1327] [42000]: (conn:7575) Undeclared variable: v_amount_of_samples_that_require_revision

Is there a workaround?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆离笙 2025-02-15 07:54:23

代替定期变量，使用用户扣除会话变量

仍然可以用于SQL注入，

当您在Where dor Mause使用已准备好的语句中使用变量时，应针对白名单进行测试

SET @query = CONCAT('SELECT count(sample_id) INTO @v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        ' WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ?');                    
SET @p_form_id =  p_form_id;
PREPARE stmt FROM @query;
EXECUTE stmt USING @p_form_id;
DEALLOCATE PREPARE stmt;
SELECT @v_amount_of_samples_that_require_revision;

instead of regularly variables use user defned session variables

still v_table_name could be used for sql injection and should be tested against a whitelist

When you use variables in the where lause use prepared statements

SET @query = CONCAT('SELECT count(sample_id) INTO @v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        ' WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ?');                    
SET @p_form_id =  p_form_id;
PREPARE stmt FROM @query;
EXECUTE stmt USING @p_form_id;
DEALLOCATE PREPARE stmt;
SELECT @v_amount_of_samples_that_require_revision;

回复收藏 0 原文

~没有更多了~