PL/SQL 查询 IN 逗号分隔的字符串

2021-12-24 00:00:00 oracle plsql oracle-apex

我正在 Oracle APEX 中开发应用程序.我有一个以逗号分隔的用户 ID 字符串，看起来像这样，

I am developing an application in Oracle APEX. I have a string with user id's that is comma deliminated which looks like this,

45,4932,20,19

这个字符串存储为

:P5_USER_ID_LIST

我想要一个查询来查找此列表中的所有用户，我的查询如下所示

I want a query that will find all users that are within this list my query looks like this

SELECT * FROM users u WHERE u.user_id IN (:P5_USER_ID_LIST);

我不断收到 Oracle 错误:无效号码.但是，如果我将字符串硬编码到查询中，则它可以工作.像这样:

I keep getting an Oracle error: Invalid number. If I however hard code the string into the query it works. Like this:

SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);

有人知道为什么这可能是一个问题吗?

Anyone know why this might be an issue?

推荐答案

绑定变量绑定 a 值，在本例中为字符串 '45,4932,20,19'.您可以按照 Randy 的建议使用动态 SQL 和串联，但您需要非常小心，用户无法修改此值，否则您会遇到 SQL 注入问题.

A bind variable binds a value, in this case the string '45,4932,20,19'. You could use dynamic SQL and concatenation as suggested by Randy, but you would need to be very careful that the user is not able to modify this value, otherwise you have a SQL Injection issue.

更安全的方法是将 ID 放入 PL/SQL 进程中的 Apex 集合中:

A safer route would be to put the IDs into an Apex collection in a PL/SQL process:

declare
    array apex_application_global.vc_arr2;
begin
    array := apex_util.string_to_table (:P5_USER_ID_LIST, ',');
    apex_collection.create_or_truncate_collection ('P5_ID_COLL');
    apex_collection.add_members ('P5_ID_COLL', array);
end;

然后将您的查询更改为:

Then change your query to:

SELECT * FROM users u WHERE u.user_id IN 
(SELECT c001 FROM apex_collections
 WHERE collection_name = 'P5_ID_COLL')

相关文章